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:

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 querys, but this linked query will be executed each time a CUSTNO changes in the main dataset, if there are 200 customers, 201 querys will be executed (once for each custno and the main query).

To enhace performance we will execute 2 querys 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 connectoin with the sql
SELECT CUSTNO,SUM(AMOUNT) AS AMOUNT FROM SALES GROUP BY CUSTNO

No datasource needed, both querys will be executed only once to enhace performanc 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