I wanted something that would make it easy to generate graphs from large datasets in a variety of ways while keeping things clean when the data became questionable. My focus was aimed at financial analysis over a specified period of time, generally dealing with revenue numbers. For this reason, my example will demonstrate how to use the API to create a graph showing spend vs. revenue over time.
I was able to create a GoogleChart class that basically aids in generating the Google URL syntax and combine that with a GraphHelper class that scrubs up the data for use with the charts. The GoogleChart class will simply return a string with the url to the google chart you want generated.
The source code for the API and Helper class is provided below, and I will simply show how to code against them.
1: GoogleChart chartRevenue = new GoogleChart();
2: chartRevenue.GraphWidth = width;
3: chartRevenue.GraphHeight = height;
4: chartRevenue.ChartTitle = "Revenue vs Spend";
5: chartRevenue.ChartType = ChartTypes.LineChart;
6: //The Legend is stored in a List and expects a set of data points per legend.
7: chartRevenue.ChartLegend.Add("Revenue Dollars");
8: chartRevenue.ChartLegend.Add("Spend Dollars");
9: //The Colors are stored in a List and expect a set of data points per color
10: chartRevenue.DataSetColors.Add("#00ff00");
11: chartRevenue.DataSetColors.Add("#ff6614");
12: chartRevenue.ShowGridLines = true;
13:
14: //fake query
15: string query1 = "select revenue from data where date between now and later";
16: string query2 = "select spend from data where date between now and later";
17:
18: DataSet dsRev = DataAccess.ExecuteQueryGetDataSet(query1);
19: DataSet dsSpend = DataAccess.ExecuteQueryGetDataSet(query2);
20:
21: int MaxRev = 0;
22: int MaxSpend = 0;
23:
24: //The MaxValue is needed to scale the results to the graph.
25: //The second parameter is the column to use for the dollar value.
26: try { MaxRev = GraphHelper.GetMaxValueFromDataSet(dsRev, 2); }
27: catch { MaxRev = 0; }
28:
29: try { MaxSpend = GraphHelper.GetMaxValueFromDataSet(dsSpend, 0); }
30: catch { MaxSpend = 0; }
31:
32: if (dsRev != null && dsRev.Tables[0].Rows.Count > 0)
33: {
34: //Set the MaxValue for the chart to get the proper scale.
35: chartRevenue.MaxValue = MaxRev > MaxSpend ? MaxRev : MaxSpend;
36: //The GraphHelper will grab out a range of X Axis Labels
37: //using your max value as the top most value.
38: //The additional x axis values are generated by dividing out the
39: //max value to get intermediate values.
40: foreach (string s in GraphHelper.GetAxisLabelsFromDataSet(dsRev, 1))
41: {
42: chartRevenue.XAxisLabels.Add(s);//Add the labels to the chart
43: }
44: //Add the revenue data points to the collection using the
45: //Graph Helper, the second parameter is the column holding the data values.
46: chartRevenue.DataPoints.Add(GraphHelper.GetDataValuesFromDataSet(dsRev, 2));
47: }
48: else
49: {
50: //No Data, return no data image
51: return "~/Images/Layout/Tracker_LCD_No_Data.jpg";
52: }
53:
54: //Add the Spend data points to the chart
55: if (dsSpend != null && dsSpend.Tables[0].Rows.Count > 0)
56: chartRevenue.DataPoints.Add(GraphHelper.GetDataValuesFromDataSet(dsSpend, 0));
57:
58: //Tell the graph to build with the supplied data and
59: //return a URL to the resulting chart image.
60: return chartRevenue.GetGraphImage();
The Source code is below. I'm sure this solution is not for everyone, but people with large data sets might find it useful.