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:

CrossTabChart.zip

GrapeCity

GrapeCity Developer Tools
comments powered by Disqus