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.
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.
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);
|
|