|
Building a Report - Showing Related Data in Sub-Reports
|
Prerequisites
The topic which covered in this section requires an understanding of the following:
|
If you are familiar with "Subforms" in the application, this is the equivalent concept for reports.
This can be used to create a report on a Component, and also show a LIST of related records. For example:
- Show a List of all Incidents AND
- For each Incident, show it's related Actions AND
- For each Action, show their Progress Records
While there are several ways of achieving this goal, we suggest to use the steps in the order listed below.
Reports with Sub-Reports - Introduction
In the Report Designer, it is required to create a separate SQL script to retrieve the data for EACH of the (Sub-) Reports in a Report.
From the "highest" reporting level to the "lowest" report level, the results of each script need to be passed on to the following script. To stay with the above example:
- To get a list of all Actions for an Incident, the script to retrieve the Actions will need to be filtered by the current Incident.
- Following the same process, the script to retrieve the Action Progress records for each Action will need to be filtered by the current Action.
- These details will be passed between the scripts via Parameters.
Step
|
Details
|
Create ALL required SQL scripts for the current report.
|
- Create the script for the Main Data Set (in our example: Incidents), and then for all the related Data (in our example: Actions, Action Progress)
- While Actions and Action Progress will eventually need to be filtered by their "parent" record via a
WHERE clause, for now do not add this clause so that you can test the script.
|
Create Parameters
|
The Parameters are used to pass on the required filtering information from the "Parent" to correctly filter the "Child" records.
Create a new Parameter and, for each of the scripts which need to pass on information to it's children (in our example: Incidents and Actions)
- Name the parameter, and select Value Type = Number
- Tick the box for the parameter to be hidden
- Select the appropriate Query
- As Value, select the field which identifies, and LINKS each record on database level. This will always be dwk_<Component Name>.
In our example, these would be:
- dwk_action
- dwk_incident
|
Update the SQL scripts for the sub-reports (or children).
|
As a next step, modify the scripts for the sub-reports, so that they will be filtered correctly.
The "logic" for the WHERE clause is that the "dwk_" field which links the current component to it's parent is the same as the parameter which was passed on by the parent.
Each reporting table in the warehouse, whenever it is linked to other tables, has one or several fields which end in "dwk_". For the purpose of linking the records, select the appropriate field, and create a WHERE condition like the following examples.
The parameter which was created in the previous step is included in the {} brackets.
- To filter Action Records by their "parent" Incidents:
Select the script which retrieves the Actions, and add the following condition:
...
WHERE
"rt_action"."dwk_incident_as_source_of_action" = ${IncidentIdentification}
...
- To filter Action Progress Records by their "parent" Actions:
...
WHERE
"rt_action_progress"."dwk_action_as_source_of_action_progress"=${ActionIdentification}
...
|
Create the Sub-Report
|
To create a sub-report, you
- drag the sub-report icon from the control panel onto the report canvas
- decide if you want to insert the sub-report as
- "inline": allows free placement on the canvas, or
- "banded": report will occupy full width of the canvas - from the list displayed by the Report Designer, select the SQL script which will be used to retrieve the data for this sub-report.
- the system will open a new separate tab, in which you can define the report.
- create the layout of the sub-report.
NOTE: the fields containing the data will need to be placed into the DETAILS panel of the report. - You can name the sub-report by selecting
- Structure  sub-report
- and then Attributes Name = <Name of sub-report>
|
Linking the Parameters from the Parent to the Sub-Report.
|
Now you will need to define, which of the Parameters you have set up in the previous step will be passed on to the sub-report.
- While in the sub-report window, select the 'Data" tab
- Right-click on "Parameters", and select "Edit Sub-report parameters".
- In the Sub-report Parameter window, create a new entry for "Import Parameter" and define the
- Outer Name: this is the value which is passed in from the Parent Record into the sub-report
- Inner Name: this is the Parameter which used in the SQL script to accept this value.
- To stay with our example: if this is the Actions sub-report, which is contained in the Incident main-report, then:
- Outer Name is the dwi_incident
- Inner Name is the parameter IncidentIdentification, as used in this part of the script:
...
Where "rt_action"."dwk_incident_as_source_of_action" = ${IncidentIdentification}
...
|
Create further sub-reports
|
It is possible to implement cascading sub-reports - that is add a sub-report to an existing sub-report. In our example:
- Incidents are the main-report
- Actions are a sub-report for Incidents
- Action Progress is a sub-report of Actions.
The process for this is identical, regardless of the "level" of the report.
|
Dealing with sub-report data which don't have corresponding records?
|
If there is a possibility that some of the main-records to not have any related sub-records, you have the option to indicate this by the following configuration:
- Select the sub-report
- In the structure menu, select to "un-hide" the No Data element.
- On the canvas, add the information you want to be shown when there are no sub-report records.
For example, this could be a text field stating "This Incident has no Actions."
|
|
