002 – Creating a crosstabs in Delphi

The task

   The table of products with the next structure of fields is given:

Field name Data type Key field

Объяснение

ID_Product Counter + Unique identifier of field
Name Text Product name
Price Floating point number The price of product
Count Integer number The number of units of product

  Using Delphi tools, we need do a cross table based on two dimentions: “Name” and “Price“. Also we need carry out the summation of columns and rows of the table.

 

Progress

  1. Run Delphi-7.

Save the project.

  1. Creating database and tables using “Database Desktop” utility.

  Create database with alias myDb. Create a database table called “product.db” having the structure in accordance with the conditions of the problem.

  1. Components for creating a cross table.

   To create a cross table in Delphi we need use components from “DecisionCube” tab from the components palette (fig. 1).

01_01_00_001_01_

Fig. 1. Tab “Decision Cube” from components palette

  First of all we place on the form following components from tab “Decision Cube”:

– component of TDecisionCube type (by default it has name DecisionCube1);

– component of TDecisionQuery type (by default it has name DecisionQuery1);

– component of TDecisionSource type (by default it has name DecisionSource1);

– component of TDecisionGrid type (by default it has name DecisionGrid1).

  Of these four components only DecisionGrid is intended for displaying data table.

  Form with placed components has view (fig. 2):

01_01_00_001_02_

Fig. 2. Main form of application with components DecisionCube1, DecisionQuery1, DecisionSource1, DecisionGrid1

 

  1. Setting the components.

  With the help of Object Inspector set the following properties for such components:

– in component DecisionCube1 a property DataSet set to value DecisionQuery1;

– in component DecisionQuery1 a property DatabaseName set to value “myDb”;

– in component DecisionSource a property DecisionCube set to value DecisionCube1;

– in DecisionGrid1 a property DecisionSource is equal DecisionSource1.

 

  1. Setting “Decision Query Editor”.

  The immediate setting of the cross table carried in component DecisionQuery1. For calling window setting we need click on the right button of mouse and select from the context menu “Decision Query Editor…”. Other method of calling – double click in area of component DecisionQuery1 (fig. 3).

01_01_00_001_03_

Fig. 3. Calling the editor for setting in component DecisionQuery1

As a result, a window “DecisionQueryEditor” will be opened (Fig. 4).

01_01_00_001_04_

Fig. 4. Window of editor “Decision Query Editor

  As we see from figure, window consists two tabs:

– tab “Dimensions/Summaries” allows you to set the table fields and aggregate functions (sum, the total number etc.) on which the calculations are made;

– tab “SQL Query” allows to describe queries to the table on SQL-language.

  In this window are accessible following areas:

– area “List of Available Fields” displays information about fields of selected table from the list (area) “Table”;

– area “Dimensions” displays information about the table fields, which are the dimensions on which aggregate functions are calculated (sum, average etc.);

– area “Summaries” displays the aggregate functions which are applied to the table;

– the field “Table” displays the table of database for which calculations are made;

– the field “Database” specifies database for tables of which make calculations.

  In our case we select table “product.db” of database “myDb”.

  The editor window will have next view (fig. 5).

01_01_00_001_05e

Fig. 5. Window of editor “Decision Query Editor” after when we selected database “myDb” and the table “product.db

  Use the arrows to the left (right) to add fields of the table, which will participate in the calculations. Fields will be added to the area “Dimentions”. Using the arrows left / right, placed at the bottom of the editor you can define one or another aggregate function (Fig. 6).

01_01_00_001_06e

Fig. 6. Adding the aggregate functions

  We form the editor window so that it had the following view (Fig. 7) and we confirm our choice by clicking on “OK“.

01_01_00_001_07e

Fig. 7. Editor window after filling all fields

  In the tab “SQL Query” you can see the view of query which was formed on SQL-language.

 

  1. Setting the component DecisionCube1.

  Now, we can configure the component DecisionCube1. For this we are calling the editor “Decision Cube Editor” in the same way as in the case of the editor of the “Decision Query Editor” (Fig. 8).

01_01_00_001_08e

Fig. 8. Calling the editor “Decision Cube Editor” of the component DecisionCube1

  As a result the window “Decision Cube Editor” will be opened (fig. 9).

01_01_00_001_09e

Fig. 9. Window of editor “Decision Cube Editor

  Window consists two tabs: “Dimension Settings” and “Memory Control“.

  In the first tab “Dimension Settings” you can define a number of settings for the cube dimensions. The tab contains the following fields:

– the field “Display Name” defines the name, which will be displayed in the component TDecisionGrid of displaying the data;

– the list “Type”, which specifies the field type. In our case list is inactive, because the information received from component TDecisionQuery;

– the list “Active Type” specifies a moment of loading database into memory (“As Needed” – as needed, “Active” – always, “Inactive” – never;);

– the “Format” string allows to set the format of displayed values;

– the drop-down list “Grouping” allows to group data by time.

In this case, we leave it as is, without changes.

 

  1. Display the cross table.

  If the previous steps have been completed correctly, you can display the cross table.

  For this, in component DecisionQuery1 the property “Active” we set to value “true”.

  As a result, main form of application will have the following view (fig. 10).

01_01_00_001_10e

Fig. 10. The view of main form after settings

  After running the application we get the cross table (fig. 11).

01_01_00_001_11e

Fig. 11. The result of running the application

 

Results

   In this article we studied the creation of elementary cross tables in Delphi.

  For organizing work with the cross tables we need to use the following components from palette “Decision Cube”:

– component TDecisionCube;

– component TDecisionQuery;

– component TDecisionSource;

– component of data view TDecisionGrid.

  Previously database and the table must be created with the help of the utility “Database Desktop“, which is delivered as a tool for working with databases in Delphi.

  In this example, the table was created in two dimensions.

  At their discretion user may create a cross table with a large number of measurements.

  Increasing the number of measurements complicates the perception generated data in the table. Therefore, it is recommended that the number of measurements was range from 2 to 4.