Add the following code in the cs file:
using System.Data;
using System.Linq;
using System.Web.UI;
using Microsoft.SharePoint;
using System.Web.UI.DataVisualization.Charting;
namespace SPChartWp.ChartWp
{
public partial class ChartWpUserControl : UserControl
{
DataView dv;
protected void Page_Load(object sender, EventArgs e)
{
try
{
SPSecurity.RunWithElevatedPrivileges(delegate()
{
using (SPSite oSite = new SPSite(SPContext.Current.Site.Url))
{
using (SPWeb oWeb = oSite.OpenWeb())
{
SPListCollection oListCollection = oWeb.Lists;
SPList oList = oWeb.Lists["Tracker"];
SPListItemCollection oItems = oList.GetItems();
DataTable dtMain = ConvertSPListToDataTable(oItems);
var dtNkdkdew = (from r in dtMain.AsEnumerable()
select r).Distinct();
DataTable resulttbl = new DataTable();
IEnumerable<string> DistinRRMasterID = new List<string>();
DistinRRMasterID = (from results in dtMain.AsEnumerable()
select (string)results["Role"]).Distinct();
//Create DataTable
DataTable returnDt = new DataTable();
DataRow oRow = returnDt.NewRow();
string[] ColumnsArr = { "Role", "Number Postions", "In Progress", "On Hold", "L1 Scheduled", "L2 Scheduled", "HR Scheduled", "Offer Released" };
int noOfColumns = ColumnsArr.Length;
for (int cntr = 0; cntr < noOfColumns; cntr++)
{
DataColumn column = new DataColumn();
column.ColumnName = ColumnsArr[cntr];
column.DataType = typeof(string);
returnDt.Columns.Add(column);
}
foreach (var rrRole in DistinRRMasterID)
{
var rrInProgressCnt = (from r in dtMain.AsEnumerable()
where (r.Field<string>("Role") == rrRole
&& r.Field<string>("Requirement Status") == "In Progress")
select r).Count();
var OnHoldCnt = (from r in dtMain.AsEnumerable()
where (r.Field<string>("Role") == rrRole
&& r.Field<string>("Requirement Status") == "On Hold")
select r).Count();
var L1RoundCnt = (from r in dtMain.AsEnumerable()
where (r.Field<string>("Role") == rrRole
&& r.Field<string>("Requirement Status") == "L1 Round")
select r).Count();
var L2RoundCnt = (from r in dtMain.AsEnumerable()
where (r.Field<string>("Role") == rrRole
&& r.Field<string>("Requirement Status") == "L2 Round")
select r).Count();
var HRRoundCnt = (from r in dtMain.AsEnumerable()
where (r.Field<string>("Role") == rrRole
&& r.Field<string>("Requirement Status") == "HR Round")
select r).Count();
var OfferReleasedCnt = (from r in dtMain.AsEnumerable()
where (r.Field<string>("Role") == rrRole
&& r.Field<string>("Requirement Status") == "Offer Released")
select r).Count();
var MainColl = (from r in dtMain.AsEnumerable()
where (r.Field<string>("Role") == rrRole)
select r);
#region Bind Fields datatable
//Add Data to row
//Create DataTable Rows
oRow = returnDt.NewRow();
var item = MainColl.FirstOrDefault();
oRow["Role"] = rrRole;
oRow["Number Postions"] = item["NmberOfPostion"];
oRow["In Progress"] = rrInProgressCnt;
oRow["On Hold"] = OnHoldCnt;
oRow["L1 Scheduled"] = L1RoundCnt;
oRow["L2 Scheduled"] = L2RoundCnt;
oRow["HR Scheduled"] = HRRoundCnt;
oRow["Offer Released"] = OfferReleasedCnt;
returnDt.Rows.Add(oRow);
#endregion
}
Chart1.ChartAreas["ChartArea1"].AxisY.Title = "Number Postions";
Chart1.ChartAreas["ChartArea1"].AxisX.Title = "Role";
Chart1.DataSource = returnDt;
Chart1.DataBind();
dv = new DataView(returnDt);
dv.Sort = "Role ASC";
//Bind Data to Grid
GridView1.DataSource = dv;
GridView1.PagerTemplate = null;
GridView1.DataBind();
}
}
});
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
private static DataTable ConvertSPListToDataTable(SPListItemCollection spItemCollection)
{
DataTable dt = new DataTable();
try
{
dt = spItemCollection.GetDataTable();
foreach (DataColumn c in dt.Columns)
c.ColumnName = System.Xml.XmlConvert.DecodeName(c.ColumnName);
return (dt);
}
catch
{
return (dt);
}
}
}
}