In memory datasets

Advanced report design

In memory datasets

There is a special database driver, Mybase, that will allow the use of in memory datasets.

The features of this driver are:

• Read the dataset from a file a Mybase binary or XML formatted file (created by TClientDataset component from Borland tools)
• Read the dataset from a plain text file, with the help of a field definition file, this files are usually files with one line for each row and fixed size fields. There is a wizard no create the field definition file.
• Sort the dataset by any field, with the Index Fields property you specify the fields separated by ; symbol (CUSTNO;ORDERNO for example).
• Get the records from any other already defined dataset. You use the UNIONS list to specify which datasets will be used to fill the in memory dataset, if you select more than one dataset, the number of fields and field types must be the same.
• Group the union, by selecting union grouping, the in memory dataset will by grouped by the fields specified in the Index Fields property.
• Establish master detail relationships, this is a filter based on fields of another dataset. Use the Master Fields property combined with Index Fields property to perform field relation equivalences.

Sample

Imagine we are reporting sales by customer but very detailed. We will use a subreport with a group, customer number, the trick is that at the group header we want to print total by customer, so expression evaluator sums will not work.

You can solve the problem by using linked queries, but this linked query will be executed each time a CUSTNO changes in the main dataset, if there are 200 customers, 201 queries will be executed (once for each custno and the main query).

To enhance performance we will execute 2 queries one for the detail and another for all totals by customer.

1.Create a database connection.
2.Create a MyBase connection.

DETAIL dataset with database connection with the sql:
SELECT CUSTNO,ARTNO,QTY,PRICE,AMOUNT FROM SALES ORDER BY CUSTNO

TOTALS dataset with database connection with the sql
SELECT CUSTNO,SUM(AMOUNT) AS AMOUNT FROM SALES GROUP BY CUSTNO

No datasource needed, both queries will be executed only once to enhance performance and
net bandwith.

Now to show the correct grand total (TOTALS.AMOUNT) for each customer at the group header:

INMEMTOTAL dataset with MyBase connection, UNION the TOTALS datset.
IndexFields CUSTNO
Datasource DETAIL, MasterFields CUSTNO.

Master fields relates to field names in DETAIL, that will be used with IndexFields to perform a filter in
INMEMTOTAL dataset. In this case the filter will result in only one row, but it's not limited in any way.

For example a INMEMORY DATASET can contain lot of rows and the filter can reduce the result set:
IndexFields: CUSTNO;ORDERNO
MasterFields: CUSTNO