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.

 

Instructions

1. Run Delphi-7

The example of creating a project in Delphi-7 is described here.

Save the project.

 

2. 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.

 

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

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):

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

 

4. 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.

 

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

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

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

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

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 “Dimensions”. Using the arrows left/right, placed at the bottom of the editor you can define one or another aggregate function (Fig. 6).

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“.

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.

 

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

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

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.

 

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

Fig. 10. The view of main form after settings

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

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.

⇑

---