Using Crosstab Queries in Data Bound Charts
Applies To:
Chart for WinForms
Author:
John Juback
Published On:
10/11/2005
ComponentOne Chart for WinForms supports data binding via the DataSource property of the C1Chart control and the DataField property of the X and Y elements (of type ChartDataArray) owned by each data series. While this is a convenient technique for simple data-bound plots, it also works with queries that compute summary totals based on values for each row and column. This article demonstrates how to use crosstab queries as a data source for C1Chart to provide automatic data analysis without coding.
First, add an OleDbDataAdapter component to the form, then specify a data connection in the Data Adapter Configuration Wizard. The sample project for this article uses the Microsoft Jet 4.0 OLE DB Provider to connect to a Microsoft Access database located in:
C:\\Program Files\\ComponentOne Studio.NET\\common\\C1WinDemo.mdb or \\ComponentOne Samples\\Common\\C1Nwind.mdb
Next, configure the data adapter to use SQL statements. Click the Advanced Options button and clear all check boxes in the dialog. Since we are going to use a crosstab query, we do not want to generate INSERT, UPDATE, and DELETE statements.
The Query Builder dialog does not recognize crosstab syntax, so we will have to enter the SQL statement manually:
TRANSFORM SUM([Product Sales by Date].ProductSales) AS SumOfProductSales
SELECT [Product Sales by Date].CategoryName
FROM [Product Sales by Date]
GROUP BY [Product Sales by Date].CategoryName
PIVOT Format([OrderDate], 'yyyy');
Finish the wizard and ignore the error message "Unable to parse query text." Right-click the OleDbDataAdapter component and select Generate Dataset to add a new dataset called DsCategorySales to the project.
Understanding the Data Transformation
Note that [Product Sales by Date] is not a physical database table, but is defined as a SELECT statement that joins four tables: Categories, Products, Orders, and Order Details.
SELECT DISTINCTROW
Categories.CategoryName,
Products.ProductName,
Orders.OrderDate,
Sum(CCur([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100) AS ProductSales
FROM (Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID)
INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID)
ON Products.ProductID = [Order Details].ProductID
GROUP BY Categories.CategoryName, Products.ProductName, Orders.OrderDate;
Running this query within Microsoft Access yields a result set similar to the following:
CategoryName ProductName OrderDate ProductSales
------------ ----------- --------- ------------
Beverages Chai 20-Sep-02 $518.40
Beverages Chai 30-Sep-02 $259.20
Beverages Chai 31-Oct-02 $288.00
...
Seafood Spegesild 06-May-04 $300.00
Seafood Spegesild 13-May-04 $36.00
Seafood Spegesild 05-Jun-04 $341.28
The TRANSFORM statement listed earlier groups the results according to the CategoryName field. The PIVOT clause creates a data column for each distinct year extracted from the OrderDate field:
PIVOT Format([OrderDate], 'yyyy');
Note that you can vary the format string argument to create data columns that represent other time increments such as months or quarters. The following figure shows the result set generated by the TRANSFORM statement, with three columns of yearly totals for each product category.
First, choose the type of chart you want to display. The easiest way to do this is by right-clicking the chart and selecting Chart Properties. The sample project uses a horizontal bar chart, unstacked, with 3D effects.
Keep the Chart Properties dialog open and select the Binding node under Data. Set the data source to dsCategorySales1.Table as shown in the following figure.
Next, create a data series to represent each year column exposed by the crosstab query. By default, the chart contains four unbound data series (0 through 3). Select the first one (series 0), and set the following properties:
Label
2002
Color
PaleGreen
X
CategoryName
Y
2002
Note that each data series is represented by a distinct color. Also, since a horizontal bar chart is really an inverted column chart, the sense of the X and Y axes are reversed. Repeat the above steps for the remaining data series.
For series 1:
Label
2003
Color
DeepSkyBlue
X
CategoryName
Y
2003
For series 2:
Label
2004
Color
DarkOrange
X
CategoryName
Y
2004
Finally, since there are only three sets of data points, remove series 3. The following figure depicts the ChartData tab after all three data series have been configured.
Note that the chart preview at the lower left also reflects additional customizations not spelled out here, such as background gradients, rounded corners, header, and legend. Everything was set up at design time using the Chart Properties dialog or the Visual Studio Properties window.
Before running the application, create a handler for the form's Load event and add the following line of code:
this.oleDbDataAdapter1.Fill(this.dsCategorySales1);
This is necessary to fill the chart's DataSource with the results of the crosstab query. At run time, the data bound chart will look something like this:
To download the source code for the sample project described in this article, visit the following link:
MESCIUS inc.