The report that is designed in this section shows a summary of outstanding orders. This is rather like the Orders form, and contains an outer block (which shows the Client table) and an inner block (into which details of each order appear). This is the OrdersSummary report in the Orders demonstration database.
A report is created using the New Report entries under the desired database entry. However, unlike the Orders form, this report gets its data via the query from the previous section; select Query Block as the Top-level block type in the first report dialog, which is shown below. This dialog also allows you to set print margins (as of version 2.2.4, the margins appear under a single property). The values are initially set to defaults, which can be changed via the View/Options menu on the main database dialog.
Having clicked OK on the report dialog, a dialog for the query appears. The settings for this are listed below, and the dialog is shown in the following screenshot. Note that there are a set of properties Where condition, Row order, Row grouping and Having; these are appended to the Rekall query and can be used to customise the query for the purposes of the report (although, beware that it would be possible to create invalid queries).
Property | Significance | Setting |
Query name | Rekall query to be used to supply data | OrdersSummary |
Top-level table | The table in the query which is used to supply data for the outermost report block. | Clients |
Row Order | Additional SQL query ordering expression. | Client.Company |
Note also the Row order setting. Even if you were not really bothered about the order, you would still need something which orders the clients, such as comany name or ClientID. This is because there is no gaurantee that the server database would otherwise return data with rows at least grouped together by company (so the report might show a page for some orders for company A, then some for B, and then another for A). In this example an order has been added here, but the order could equally well be set in the OrdersSummary query itself. The advantage to setting it here is that OrdersSummary could then be used in another report which shows client orders for each product (as opposed to product orders for each client), with the Row order property set to Product.Description.
Clicking OK in this dialog will lead to the third dialog, the block dialog. This is similar to a form, although some properties are not present, for instance there is no row count and no control spacings. The former is not needed since a report will always generate as much output as is needed for the data, and the latter since spacings are controlled by the layout in the design. Clicking OK once more leads to a blank report, shown in the following screenshot.
Some explanation is in order here. Because of the choice of a Rekall query as the top-level block type, and the choice of the Client table as the top table within that query, Rekall has created a report with a sub-report, and has created the blank report with a nested block (the sub-report) already in place. Rekall has also added headers and footers at both the report (Client table) level and at the sub-report (Orders and Product table) level. These are tagged to the right, with the number at the end indicating the blocking level within the OrdersSummary Rekall query. Had you set the top-level block type to access a table or a free-text SQL query, then there would have not been any sub-report (and the tags would change appropriately).
There are two things to notice about blocks in reports. Firstly, they all have the same width, and are all aligned to the left. This is because of the way that reports are generated; output is produced row-by-row, advancing down the page and throwing pages where needed. Secondly, they do not have the dx nor dyproperties; movement between rows is always down the page, and the distance is controlled by the height of the block less the height of the header and the footer (since the block is generated once for each row).
Essentially, a report is executed by processing each row that is retrieved from the server database and generating output as needed. In this report, since the data comes from a Rekall query, and the Client table has been set as the top table, execution can be thought of as iterating over each client (in the top level block), and for each client iterating over each order (in the nested block). Page throws are controlled by the Page throw block properties, according to the table below:
None | Page throws only occur when a page is full |
Group | A page throw occurs after the last record |
Record | A page throw occurs after each record |
In the Orders demonstration database, the Page throw property of the inner block is set to Group. Since last record is interpreted as meaning the last order record for the current client, there will be a page throw between clients (plus, of course, page throws if a page becomes full). Each time a page is thrown, footers are output for the block that is processing records, and all enclosing blocks, and headers are output similarly. Rekall keeps track of the amount of space needed for the footers and headers ( The situation where the space required for the headers and footers is such that there is no space left on the output page is detected, and treated as an error! ) .
The image below shows the report at an early stage of development. A few basic controls have been added, and the sizes of the blocks, and headers and footers have been changed a little. In this state, executing the report would place the title Orders Report at the top of each page, followed by the company and contact names for the client, and a set of column headings.
The remainder of the controls can now be added, as shown in the screenshot a little further on. Various controls are worth noting. The Quantity and Value controls to the right of the Total label are Summary controls. These are like normal fields, but accumulate information, and have a property Summary function which controls their behaviour; currently, total, minimum and maximum are supported. Summary controls are always reset when the block in which that are embedded is finished (note that headers and footers are two other examples of containers, so controls that are embedded in them are associated with the block that the header or footer is embedded in). They also have a property which controls whether they are reset each time a page is thrown (so you can do per-page summaries or running summaries).
The bottom-left control is a field, however its Display expression (ie., the expression which is retrieved as part of the server database select query) is actually set to 'Page %{pageno} of %{pagecount}'. The value retrieved will be exactly this string for all rows retrieved, but just prior to it being output, the %{.....} parts are substituted with the page number and the total page count.
The top-right control is similar, but with its Display expression set to =time.strftime("%d-%b-%y", time.localtime (time.time())). This is actually a very small piece of python script, specifically it is a python expression and, rather than forming part of the select query, the expression is evaluated each time a value is needed, and will return a formatted date-and-time string ( To get this to work, the Import modules property of the report must be set to include time. This is explained in the chapter on scripting. )
From here, you can switch to data view to display and print the report.