ComponentOne True DataControl 8.0
Tutorial 2 - Crosstab

In this tutorial, you will see a powerful new feature that TDataLite adds to ComponentOne True DBGrid 8.0 (OLE DB version) – crosstab data presentation. 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 TDataLite work together to implement crosstab for you. Unlike crosstab implementations in many other tools, TDataLite 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.

Note: You can use TDataLite 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.

  1. Start a new project.

  2. Place the following controls on the form (Form1) as shown in the figure: a TDataLite control (TDataLite1), an ADODC control (Adodc1) and a True DBGrid 8.0 control (TDBGrid1).

  3. Set properties as follows:
    Example Title
    Copy Code
    Adodc1.ConnectionString
    
    Provider=Microsoft.Jet.OLEDB.3.51;Persist Security
    
    Info=False;Data Source=C:\Program Files\ComponentOne Studio\Common\TDBGDemo.mdb
    
    Adodc1.CommandType         2 - adCmdTable
    
    Adodc1.RecordSource        MonthlyOrders
    
    TDataLite1.DataSource      Adodc1
    
    TDBGrid1.DataSource        TDataLite1
    
  4. Select TDataLite1, and click the right mouse button over it to open the control's context menu. Select the TDataLite Properties option. This will open the property pages dialog, where you can view and edit the properties of TDataLite1. Go to the Crosstab page. Check the XTab check box (TDataLite1.XTab property). This will enable the crosstab feature.

  5. In the XTabRowKey edit box (TDataLite1.XTabRowKey property) type CustomerID. This specifies CustomerID as the row key, the key field defining crosstab rows. Table rows having the same CustomerID values will be displayed in a single row, in different columns according to their value of the Month key. Notes: (a) You must make sure that the recordset is ordered by the key you define in TDataLite.XTabRowKey. For a table, it must be a part of its primary key, for SQL, use ORDER BY to ensure the correct order. (b) If you have multi-field row key, specify the full semicolon-separated key sequence, for example, DepartmentID;CustomerID.

  6. In the XTabColKey edit box (TDataLite1.XTabColKey property) type Month. This specifies Month as the column key, the key field defining crosstab columns.

    Note: If you have multi-field column key, specify the full semicolon-separated key sequence, for example, Year;Month.

  7. Select the Amount field and type the following in the XTabColumns edit box (TDataLite1.Fields(“Amount”).XtabColumns property):

    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
    

    This will create twelve crosstab columns out of a single Amount field (crosstab field). Column definitions are separated with semicolons. Each column definition has the key expression and the column name:

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

    Key expression must be a valid TData expression (TData uses VBScript as its expression language). 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.

    Each column definition creates a crosstab column that is available to TDBGrid. Such column contains the value of the main field (in this tutorial, Amount) corresponding to the value of the column key (in this tutorial, Month) specified in its key expression.

  8. Close the property pages by clicking the OK button or by pressing Enter. We finished the settings in TDataLite1, now we will set up the grid, TDBGrid1.

    Notes:

    (i) If you have multi-field column key, specify a full coma-separated sequence of key values (expressions) for each column definition, for example:

    Example Title
    Copy Code
    1999,1:Jan 99;2000,3:Mar 00.
    

    (ii) 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. To do that, just fill the XtabColumns property for every such field.

    (iii) In the case of multiple crosstab fields, there are two options controlled by TDataLite.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.

  9. Select TDBGrid1, and click the right mouse button over it to open the control's context menu. Select the Retrieve Field option. The grid will show the columns in crosstab layout: CustomerID (a regular, non-crosstab column) and twelve crosstab columns corresponding to the twelve months.

  10. Select TDBGrid1, and click the right mouse button over it to open the control's context menu. Select the Properties option. This will open the property pages dialog, where you can view and edit the properties of TDBGrid1. Go to the Splits page and expand the Splits(00).Columns node in the property tree. You will see two nodes representing two columns: CustomerID and Amount. CustomerID is a regular field, not a crosstab field. The other field, Amount, represents all twelve crosstab columns. Change the HeadAlignment property of Column(01) [Amount] to 2 – Center. Close the property pages by clicking the OK button. You will see that the captions of all twelve crosstab columns are now center-aligned. This way you can specify any settings of the crosstab field to your liking, and it will be applied to all crosstab columns generated by that field – much easier than if you had to set up each crosstab column separately.

    Notes:

    (i) There is a special property, TDataLite.XTabSeparateFields controlling the way crosstab columns are exposed to TDBGrid 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). All settings of the corresponding column will be applied to all crosstab columns it generates, as we saw in Step 10. In most cases, it is convenient because you usually set up all crosstab columns with identical settings. If XTabSeparateFields=True, TDBGrid will see crosstab columns as separate data fields, so you can set up each of them separately.

    (ii) 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.

Run the Program and Observe the Following:

TDBGrid1 displays data as a matrix with CustomerID’s as rows and months as columns. You can modify any cell and the change will be saved in the database.

Congratulations, you have successfully completed Tutorial 2!

 

 


Copyright (c) GrapeCity, inc. All rights reserved.

Product Support Forum  |  Documentation Feedback