SubReports

SubReports

Subreports are reports within your report. They are used in three instances:

When want to display data that is not logically contiguous with the main query that the report is based upon. Let us suppose that when you print a special type of quote report you want a summary page at the end that lists the state of the customer's account with you, i.e. how many invoices are outstanding (unpaid) at the time of printing. You would do would do this in a subreport

Sometimes subreports can act as calculation engines for the main report passing the results back to the main report while not actually displaying data themselves.

Sometimes data is retrieved a lot faster if you break a single query up into two queries that run together. Each query acts as a data pipeline a report and a report can have only one data pipeline. So your first query (DataView) supplies the main report, the second a subreport.

Subreports can be free standing within a report or linked to the main report on a common field. The latter is the most likely scenario. Take the example of the account summary on a quote report: the subreport here would need to link to the main report on the customer ID field so that only a summary of unpaid invoices for the customer you are quoting for. But let's take a look at a real report: open the Equipment Book | Rental Warehouse Level | Warehouse Levels report, and go to the Data tab.

In the above screen shot, the master DataView (Types) is a query that joins three tables: Types, Category and MasterCategory. The second DataView, retrieves all data from Eqlist_Detail (the lines on an equipment list) where the data is not a section header and equipment is still out. We can check this by viewing the selection criteria (click the magnifying glass icon) or viewing the SQL for the query directly (click the WordPad icon). The second DataView is joined to the first on the ID field for an equipment type. The link was made by clicking the Eqlist_Detail-eqtype field and dragging across to the Types-Idx field. We can set some properties for the link by double clicking on it or by clicking the Linking icon in the second DataView.

In the report we have opted to retrieve all records from the Types query, regardless of whether there are any matching records is the Eqlist_Detail query. This is because we want a warehouse report on the current stock levels of everything we own.

If we now switch to the Design Tab we can see that at the bottom on the screen there is an extra tab for the subreport.

If you select it you will see a completely separate report layout from the main report; indeed, if you now go to the Calc Tab you will see that this is also now specific to the selected subreport. It you go back to the main report you will also be able to see the subreport object on the right hand side of the Detail Band. In this instance the subreport will not used to display any data, but as a calculating engine, so it has been squeezed to a narrow width, tucked out of the way, and its bands have all been set to invisible. It has been put in the Detail Band so that it is refreshed (re-run) for each record retrieved in the main report. Let's consider what is going in here is a bit more detail.

The two Data Views for the report are in what is called a one-to-many relationship with each other: for each equipment type in the main inventory table, there could be many lines on equipment lists for that type where the Qty Out is greater than the Qty Back. So in the Detail Band of the subreport it possible that for a single type in the main report, many lines would print if we set the visible property of the band to be true. This means that we can perform calculations on this data, adding up how much is out, how much is back, and all the time taking into account when the line on a list is from a subhire or not, since this will clearly have an impact on what is on the shelves. However, we want to get the results of these calculations back into the Main report, and we do this with a special technique using global variables. We'll now see how this works:

In the main report note that there are three variable components in the Details Band: InStock, NetOut and NetBack. If you examine the OnCalc events for each of these you will see that no code is written at all, so how are they having values assigned to them?

Go to the Calc Tab for the main report and switch the Report Window to the Module View. Select Declarations (Variables) and you will see that there are three ppVariables declared: StockTotalVar, NetOutVar, and NetBackVar.

A ppVariable is the code type of a variable component, so here we are not putting actual variable components on the report form itself, but defining placeholder variables which will point to the three actual variable components on the report (InStock, NetOut and NetBack). By so pointing, any value assigned to the three global variables will be display in their corresponding report components.

Select the Events (OnCreate) and you will see the assignment is made when the report module is created:

Now the subreport can access everything that is global to the report module, which means that it assign values to StockTotalVar, NetOutVar, and NetBackVar, and in doing so these values will be displayed automatically in the main report's variable components, InStock, NetOut and NetBack.

Switch to the subreport and select Variable1 in the Detail Band; let's take a look at its OnCalc event code:

Here we check if the line retrieved for a Type is a subhire or not, then add or subtract this to the running total that we store in the implicit result of the variable component, value. We also assign value to NetOutValue, a global variable which itself will now update NetOut, an actual variable component on the main report. The above code will fire for every record retrieved by the subreport for the matching type in the main report. When the main report's Type changes, the subreport is effectively re-run so everything starts afresh.

Now look at the Variable2 in the Summary Band of the subreport; its OnCalc event code reads:

This band (and therefore this code) will only fire once per subreport refresh, so its here that we want to retrieve out two running totals (out and back) so that we can do a simple calculation to work out how much stock should be on the shelf (Total Owned – (Total Out – Total Back)). We then assign this value to the global variable StockTotalVar, which in turn will update the report's variable component, InStock. Note how we are able to report to the table and field 'Types-stocklevel1' in the Types[] DataView. Note also that we can refer the return value of a variable specifically by is data type (asinteger) rather than as a variant type (value).

Finally, go back to the main report, Calc Tab, Events View. Find the three variable components in the Detail Band and note their OnGetText event handling code.

NoData is another global variable that is declared in the Module View of the report. Switch to the subreport Calc Tab, Events View and select the SubReport1 node to inspect the OnPrint event handler. Here NoData is initialised to be FALSE. Now select the Report node and inspect the OnNoData event handler code. Here we assign TRUE to NoData because this event will only fire if the subreport is returning nothing for the current type being displayed in the main report. So we now see that our OnGetText procedure above makes sense: if no data is returned by the subreport then nothing can be Out for that Type so we just get the InStock variable to display the total owned for the current Type, otherwise display the default text for the variable, which is going to be the value passed by Variable3 in the subreport.