Hi,
I am using C# webmethod to provide data to Google line chart. However I am
stuck with the format of the data.
Currently I can only send data in "List<object>" format. But I want to send
data in DataTable format.
Is this possible?
Please find my code snippet below:
ASP Page
//call to get data from ASP web method
$.ajax({
type: "POST",
contentType: 'application/json',
data: '{}',
url: 'AdminDashboard.aspx/GetChartData',
beforeSend: function () { alert("before send"); },
complete: function () { alert("complete"); },
success: function (data) {
var linedata1 = new
google.visualization.arrayToDataTable(data.d);
linedata1.insertColumn(0, 'date',
linedata1.getColumnLabel(0));
// copy values from column 1 (old column 0) to column 0,
converted to Date
for (var i = 0; i < linedata1.getNumberOfRows() ; i++) {
var val = linedata1.getValue(i, 1);
if (val != '' && val != null) {
var dateArray = val.split('/');
var year = dateArray[2];
var month = dateArray[0] - 1; // convert to
javascript's 0-indexed months
var day = dateArray[1];
linedata1.setValue(i, 0, new Date(year, month,
day));
}
}
// remove column 1 (the old column 0)
linedata1.removeColumn(1);
dashboard.bind(programmaticSlider, programmaticChart);
dashboard.draw(linedata1);
}
ASP webmethod
[WebMethod]
public static List<object> GetChartData()
{
DataTable chartData = new DataTable();
DataTable tktData = new DataTable();
Array arrdata=null;
SPList configList, tktList;
SPQuery dataQuery;
SPListItemCollection configColl;
SPSecurity.RunWithElevatedPrivileges(delegate()
{
using (SPSite oSite = new SPSite("http://dsknomoe11:9696/";))
{
using (SPWeb oWeb = oSite.OpenWeb())
{
//Get Config list value for Ticket Priority and
Ticket Status
configList =
oWeb.Lists.TryGetList("SCMS_Configuration");
dataQuery = new SPQuery();
dataQuery.Query = "<Where><Or><Eq><FieldRef
Name='Title' /><Value Type='Text'>Ticket Priority</Value></Eq><Eq><FieldRef
Name='Title' /><Value Type='Text'>Ticket Status</Value></Eq></Or></Where>";
configColl = configList.GetItems(dataQuery);
//Ticket Priority and status
foreach(SPListItem oItem in configColl)
{
//Get Ticket Priority
if (oItem["Title"].ToString().Equals("Ticket
Priority"))
{
tktPriority =
oItem["value"].ToString().Split(';');
}
//Get Ticket Status
else if
(oItem["Title"].ToString().Equals("Ticket Status"))
{
tktStatus =
oItem["value"].ToString().Split(';');
}
}
//Add columns to DataTable ChartData
foreach (string s in tktStatus)
{
chartData.Columns.Add(s);
}
//Get Config list value for Ticket Priority and
Ticket Status
tktList = oWeb.Lists.TryGetList("SCMS_Tickets");
dataQuery = new SPQuery();
dataQuery.Query = "<Where><Eq><FieldRef
Name='isAct' /><Value
Type='Choice'>Yes</Value></Eq></Where><OrderBy><FieldRef Name='Created'
Ascending='True' /></OrderBy><GroupBy Collapse='True'><FieldRef
Name='Created' /></GroupBy>";
dataQuery.ViewFields = "<FieldRef Name='Created'
/><FieldRef Name='tckPrty' /><FieldRef Name='tckStat' />";
dataQuery.ViewFieldsOnly = true;
tktData =
tktList.GetItems(dataQuery).GetDataTable();
var grpdata= tktData.AsEnumerable().Select(x => new
{ Date = Convert.ToDateTime(x[0]).Date.ToString("MM/dd/yyyy"), Status =
x[2] }).ToArray();
arrdata = grpdata.GroupBy(l => l.Date).Select(g =>
new
{
Date = g.Key,
Open = g.Count(l => (string)l.Status == "Open"),
Closed = g.Count(l => (string)l.Status ==
"Closed"),
InProgress = g.Count(l => (string)l.Status ==
"In-Progress"),
Total = g.Count(l => ((string)l.Status ==
"Open") || ((string)l.Status == "Closed") || ((string)l.Status ==
"In-Progress"))
}).ToArray();
}
}
});
List<object> list = new List<object>();
list.Add(new object[]
{"Date","Open","Closed","In-Progress","Total" });
string strDt, strOpen, strClosed, strInP, strTotal;
for (int i = 0; i < arrdata.Length; i++)
{
var spltData = arrdata.GetValue(i).ToString().Replace('{','
').Replace('}',' ').Split(',');
strDt = spltData[0];
strOpen = spltData[1];
strClosed = spltData[2];
strInP = spltData[3];
strTotal = spltData[4].Trim();
list.Add(new object[] {
Convert.ToDateTime(strDt.Split('=').Last()).Date.ToString("MM/dd/yyyy"),
Convert.ToInt32(strOpen.Split('=').Last()),
Convert.ToInt32(strClosed.Split('=').Last()),
Convert.ToInt32(strInP.Split('=').Last()),
Convert.ToInt32(strTotal.Split('=').Last())
});
}
return list;
}
Please help me with this.
1. My basic requirement is to create a datatable with dynamic columns and
then bind the data to google mutli-line chart.
For clarity, my current data format i.e list data is as
Date Open Closed InProgress1/2/13 2 0 02/2/13 1 2 03/3/13
0 0 14/3/13 0 1 0
--
You received this message because you are subscribed to the Google Groups
"Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/google-visualization-api.
To view this discussion on the web visit
https://groups.google.com/d/msgid/google-visualization-api/6d9ee8b5-8f2e-4183-aef4-12d6312ceab5%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
