Crosstab (matrix) Data Presentation

The crosstab feature is only available in the Lite version, in True DataControl Lite.

Normally, you will use the crosstab feature of True DataControl with ComponentOne True DBGrid. True DBGrid supports crosstab data presentation in conjunction with TDataLite. You can use the crosstab feature with grids other than TDBGrid too, but then all crosstab columns (see below) will appear as separate fields, you will have to specify all settings for every crosstab column separately.

Crosstab is a common feature of database applications. Until now, True DBGrid did not support crosstab directly, it was necessary to use unbound mode events and complex coding to implement it, or create special temporary database tables or queries. Now, True DBGrid and TData/TDataLite work together to implement crosstab for you. Unlike crosstab implementations in many other tools, TData crosstab is modifiable at run time: you can modify values in crosstab columns (cells), if, of course, the underlying recordset is modifiable.

Crosstab is a matrix-like representation where you have multiple columns representing values of a single database column. For example, a MonthlyOrders table has the following fields: CustomerID, Month, Amount, and you need to present the data as a table with twelve columns, one for each month, showing monthly amounts. An example of crosstab data presentation can be found in Tutorial 2 - Crosstab.

Crosstab is only available in DataMode = 0 – DataSource.

In crosstab mode, all visible fields are divided into three categories:

Row key fields: fields defining crosstab rows. Different records with the same row key values (they can differ by their column key values) belong to the same crosstab row. For example, if CustomerID is the row key, each customer is displayed in a different row, but multiple records differing only by their Month (column key) value are displayed in the same customer row. There can be multiple row key fields, for example DepartmentID;CustomerID.
Column key fields: fields defining crosstab columns. Records with different column key values are displayed in different columns. For example, if Month is the column key, each month occupies a separate column. There can be multiple column key fields, for example Year;Month.
Crosstab fields: values of a crosstab field are displayed in crosstab cells, on row/column intersections. For example, if the Amount is a crosstab field and Month is the column key field, one Amount field will generate twelve fields, monthly amounts, at run time. At design time, Amount will remain a single field, so you can conveniently set its properties at design time. These settings will be applied to all crosstab columns it generates (there is a special property, XTabSeparateFields, allowing to override this default behavior). You can have multiple crosstab fields, each of them generating its own crosstab columns. For example, you can have Amount and Discount fields, both of them displaying monthly data, so you will have 24 columns in your crosstab.

You must make sure that the recordset is ordered by row keys. What is essential for TData crosstab implementation, is that all records with a given row key value appear as a contiguous segment of the recordset. In SQL, the common method of ordering the recordset is using ORDER BY clause, for example

Example Title	Copy Code
SELECT CustomerID, Month, Amount FROM MonthlyOrders ORDER BY CustomerID

To enable crosstab mode, set the XTab property to True. This and other crosstab-specific properties can be specified on the Crosstab property page.

To specify row key, set the XTabRowKey property. If it is a single field, just set it to the field’s name, for multiple fields use semicolon-separated list, for example, DepartmentID;CustomerID. The order of fields in this list must correspond to the order sequence of the recordset (ORDER BY).

To specify column key, set the XTabColKey property. If it is a single field, just set it to the field’s name, for multiple fields use semicolon-separated list, for example, Year;Month.

For every crosstab field, specify its XTabColumns property. At design time, select the field in the Crosstab property page and enter the string value in the XTabColumns edit box.XTabColumns property value is a string conforming to the following syntax rules:

An XTabColumns string consists of column definitions separated with semicolons. Each column definition has a key expression and a column name:

Example Title	Copy Code
<key expression>:<column name>

<key expression> is a valid TData expression. Usually, it is just a constant. Don’t forget to enclose it in double quotes if it is a string expression.

<column name> is a string defining the name of the crosstab column. You don’t have to double quote field names.

If you have multiple column key fields (as in the Year;Month example above), the column definition syntax is slightly different. You must supply a valid TData expression (usually, constant) for every column key field, and separate them with colons:

Example Title	Copy Code
<key expression1>,…,<key expressionN>:<column name>

Example of a single column key field, Month:

Example Title	Copy Code
1:Jan;2:Feb;3:Mar;4:Apr;5:May;6:Jun;7:Jul;8:Aug;9:Sep;10:Oct;11:Nov;12:Dec

Example of multiple column key fields, Country,Year:

Example Title	Copy Code
“US”,1999:U.S. 1999;”US”,2000:U.S. 2000;”Canada”,1999:Canada 1999; ”Canada”,2000:Canada 2000

Notice double quotes in constant key expression (you always must enclose constant string expressions in double or single quotes) and the absence of quotes in the column name (it is not an expression, just a string).

XTabColumns is a property of a field. Setting this property makes it a crosstab field. If you have multiple crosstab fields, set XTabColumns for each of them.

The XTabColumns property can also be set at run time in the WillOpenData event.

A special property, XTabSeparateFields controls the way crosstab columns are exposed to True DBGrid at design time.

If XTabSeparateFields = False (default), TDBGrid will see a single field (for every crosstab field) at design time in its property pages (but it will display all crosstab columns properly). In most cases, it is convenient because you usually set up all crosstab columns with identical settings. All settings of the corresponding column will be applied to all crosstab columns it generates. This propagation of settings to all crosstab columns takes place only at design time. At run time, you can resize and set properties of crosstab columns separately, regardless of the XTabSeparateFields value.

If XTabSeparateFields = True, TDBGrid will see crosstab columns as separate data fields, so you can set up each of them separately.

If you have multiple crosstab fields, you can choose one of two possible layouts controlled by the XTabCollate property. If XTabCollate = False (default), the crosstab columns generated by different fields will be interspersed, that is, the corresponding columns will be grouped by the column key value, every group containing all fields with that key value. If XTabCollate = True, the columns will be grouped by the main field, that is, all columns generated by the first field will appear first, then all columns generated by the second fields, and so on.