I was working on something recently that required a lot of data analyses. I used the PivodGrid control as it perfectly represented my data source. And one of the reports I was building had the data grouped by Month and by Day, so I wanted to see it as a line chart as well. Quickly poking around I discovered that the PivotGridControl itself can be used as a DataSource as it implements both the IBindingList and the ITypedList interfaces. There are three members (PropertyDescriptors) that the PivotGridControl::ITypedList exposes to help with chart integration. It is easier to visualize them in terms of the coordinate system. (Line chart for example.)
1: "Series": This would be the the series legend.
2: "Arguments": This would be the X-axis.
3: "Values": This would be the Y-axis.
and this is how the list would look like if bound to a regular grid:
Binding the chart itself is easy (this will use the Auto-Created Series but you can also bind individual series the same way):
chartControl1.DataSource = pivotGridControl;
chartControl1.SeriesDataMember = "Series";
chartControl1.SeriesTemplate.ArgumentDataMember = "Arguments";
chartControl1.SeriesTemplate.ValueDataMembers.AddRange(new string[] { "Values" });
And that's it. Here is a couple of lines to quickly create your pivot table from the database in case you want to try it out.
const string SQL_GroupByMonth =
"select count(*) as Count, month(MyTable.DateColumm) as Month, " +
"day(MyTable.DateColumm) as Day from MyTable " +
"group by month(MyTable.DateColumm), day(MyTable.DateColumm) " +
"order by Month, Day";
var db = new SqlConnection("<CONNECTION STRING>");
db.Open();
var da = new SqlDataAdapter(SQL_GroupByMonth, db);
var ds = new DataSet();
da.Fill(ds);
pivotGridControl.DataSource = ds.Tables[0];
Cheers,
Azret