Crosstab Data Provider

As you know, Chart FX supports data organized in columnar format. In many cases, however, data is actually stored in tabular format, which previously required manual translation into columnar format prior to passing the data to Chart FX. This often-complicated translation of data format is costly in terms of development time. The Crosstab Data Provider included with Chart FX is an extension that automatically converts data from tabular to columnar format.

Suppose you want to chart the quarterly sales for various products. This can be accomplished by creating a standard SELECT query that uses the GROUP BY clause for the Product Name and Invoice date fields, and sums the values in the otal Sale field:


SELECT ProductName,datepart('q',InvoiceDate), Sum(TotalSale)
FROM InvoiceDetails
GROUP BY ProductName, datepart('q',InvoiceDate)
ORDER BY datepart('q',InvoiceDate),ProductName

This query produces the following output:

The previous table when passed to Chart FX will generate the following chart:

In most cases, this is not the chart you prefer to generate. The ProductName field information is repeated for each row and in order to compare the quarter sales for various products in different quarters, you must match numbers that are physically distant from one another on the page. Additionally, it could be easily overlooked that there are some quarters with no sales for a specific product.

In order to solve this problem, you should reorganize the data into columns to achieve the following table:

The following chart is generated from the previous table data:

This chart displays the data in this sample scenario much more effectively. The Crosstab Data Provider automatically converts the data from tabular to columnar format, using some definitions that are explained in the following section

The Crosstab Data Provider Functionality

There are two important aspects to effectively use the Crosstab Data Provider:

  • How the Crosstab Data Provider integrates with Chart FX.
  • How to configure it to convert tabular data to columnar data.

Taking advantage of the extensibility of Chart FX's design, the Crosstab Data Provider is a separate component that can be integrated into Chart FX as a data provider. The Crosstab Data Provider's input must be in the form of a supported Chart FX data provider, such as an array, collection, text file, XML or database among others. For more information on data providers, please refer to the Data Providers section of the Programmer's Guide or Resource Center.

Configuring the Crosstab Data Provider to Convert Tabular Data to Columnar Data

Due to the multitude of possible scenarios, the Crosstab Data Provider needs to be given rules for the conversion of tabular to columnar data. These rules define how each column in the tabular format is used. A column may be defined as any of the following options using the DataType property: Column Heading, Row Heading, Value, IniValue or Not Used.

Column Heading: When data in a column of the tabular data is used as column headings, each unique item that appears in that column will form a column in the resulting columnar format. There can only be one Column Heading defined; if more than one column heading is defined the first one defined will be used.

Row Headings: When a column or set of columns of the tabular data is used as a Row Heading, each unique item that appears in the columns specified will form a row in the resulting columnar format. The number of row headings that can be defined is unlimited.

If more than one column is specified as a row heading, the Crosstab Data Provider will concatenate the values into a single string in left-to-right order. The Separator property can be used to define a string that will separate each part of the row heading. By default, there is no separator.

IniValue: When a column of the tabular data is defined as IniValue, the Crosstab Data Provider will use it's content as the initial value for data points to achieve floating bars and area charts.

Value: When a column of the tabular data is defined as Value, the Crosstab Data Provider will use its content as the value of the element in the matrix, at the intersection of a specific row and column. Only one value column can be defined; if more than one value is defined, the first one defined will be used.

Not Used: When a column of the tabular format is defined as Not Used, the Crosstab Data Provider will ignore that column. By default, all the columns are not used.

For further explanation consider the following example:

If this data is used:

This chart is returned:

However, if the Column and Row headings are changed:

This chart is returned:

Other Crosstab Data Provider Properties

DataSource: The DataSource property is used to instruct the Crosstab provider to retrieve the information from an external data provider. The configured external data provider must be configured to a valid data provider such as a TextProvider, XmlDataProvider, ListProvider, etc.


CfxCT.setDataSource(myListProvider);

DateFormat: The DateFormat property is used to instruct Chart FX how to format the dates when a column specified as a Row Heading is a date. If no DateFormat is specified, the format will be set from the Culture property. By default, the Culture property is set to en-US.


CfxCT.setDateFormat("mm-dd-yyyy");

NullColumnHeading: The NullColumnHeading property is used to instruct Chart FX to use the configured string value when a null column heading is encountered.


CfxCT.setNullColumnHeading("N/A");

Separator: The Separator property sets the separator between two or more fields that form the point label when more than one Row Heading is configured as a DataType.


CfxCT.setSeparator("/");

XValue: The XValue property instructs Chart FX to use a single row heading as XValues instead of X Axis labels. Setting this property to True will instruct Chart FX to plot the row heading values as Xvalues instead of X Axis labels. This does not apply when you have multiple row headings configured.


CfxCT.XValue(true);

Crosstab Data Provider to Populate a Chart

This sample shows you how to connect a chart to a Crosstab Data Provider that specifies only one Row Heading. The Crosstab Data Provider connects record set to read the data from a database. Similarly, it can connect to any other data provider supported by Chart FX.

Data that generates this chart (in tabular format):

Referencing the Crosstab Data Provider

When you wish to utilize the Crosstab Data Provider in your Chart FX applications, you will need to create a new CrosstabProvider object in your project.


String url="jdbc:jtds:sqlserver://dbserver:1433/NetSamples";
java.sql.ResultSet rs = null;java.sql.Connection conn = null;
Class.forName("net.sourceforge.jtds.jdbc.Driver");
conn = java.sql.DriverManager.getConnection(url,"webguest","");
String query = "SELECT * from SampleFinancial1";
java.sql.Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
java.sql.ResultSet.CONCUR_READ_ONLY);
rs = stmt.executeQuery(query);
JDBCDataProvider provider = new JDBCDataProvider(rs);
conn.close();

CrosstabDataProvider CfxCT = new CrosstabDataProvider();
CfxCT.setDataType(0,CrosstabDataType.COLUMN_HEADING);
CfxCT.setDataType(1,CrosstabDataType.ROW_HEADING);
CfxCT.setDataType(2,CrosstabDataType.VALUE);
CfxCT.setDataSource(provider);

chart1.DataSourceSettings.setDataSource(CfxCT);