Difference between revisions of "Create iReport Templates with Subreports"

Revision as of 19:59, 7 May 2013

Advanced Reports

Sub-Report

Sub-report is reports within a report; thus, providing a way to generate a report that includes data on the target IMSMA Item as well as data on other IMSMA Items that are associated to the target IMSMA Item. Generally a sub-report is used in a situation where you have one or more sets of data relating to a single IMSMA item. A common use case would be a Location report with multiple details bands of different IMSMA items (Land, Activity, Accident, Victim, Education, and QM).

Regardless of what IMSMA data you’re including in your report and sub-reports, there are 6 basic steps to create a report with sub-reports:

Step 1 – Create the Main report

Step 2 – Add Sub-report to the Main report

Step 3 – Add Parameter to the Sub-report

Step 4 – Define Report Query for the Sub-report

Step 5 – Add Sub-report Parameter to the Main report

Step 6 – Repeat Step 2-5 for additional Sub-reports

Step 1 – Create the Main Report

Before creating the main report, you will need to determine what data to be included on your report. For example, if you are creating a report about Land with all the Progress Report data, then the target IMSMA item for your main report should be Land and the target data for your sub-report should be Activity assuming that Progress Reports are entered into the system as Activity. Below is a list of questions and things to be considered when designing a report.

• What are all the data to be included on the report?
• What is the target IMSMA Item for the Main report? This is the primary data of your report.
• What is the target IMSMA Item for the sub-reports? This is the secondary data of your report.
• Determine the relationship between the primary data and the secondary data. How did they get reconciled to one another?

The Main report should be created with the IMSMA item determined to be the primary data of your report. The Main report is just a basic report and can be created using the steps provided in the “Creating a Basic Report” section. Below are the instructions:

1. Create your main report with the IMSMA item determined as the primary data of your report. This is just a basic report.
2. Save your main report
3. Compile your main report to make sure that it’s error-free.

Step 2 – Add Sub-report to the Main report

Sub-report is reports within a report. As mentioned earlier, a sub-report should be created with the IMSMA Item determined as the secondary data of the report. Below is the instructions to create a sub-report:

1. Increase the size of Detail section of the Main report to make room for your sub-report.

View -> Bands -> detail -> increase the Band Height -> Apply

2. Click the File:IRepBand.png button
3. Click the detail section of the report. Hold down the mouse button and drag the cursor to create a rectangle to define the area of sub-report. Release the botton and the window below displays.

4. Click Next to define a new sub-report unless you have an existing report that you would like to use for sub-report.
5. Select from the Connection/Data Source list. This should be the IMSMA item determined as the secondary data of your report. For example, if you are creating a report about Land with Progress Reports, then “Activity” should be the data source for the sub-report. Click Next.
6. Select the attributes to be included in your sub-report. Click Next.
7. Select the layout option. Click Next.
8. Enter a name for the sub-report. Click Finish. The sub-report displays.

Step 3 – Add Parameter to Sub-report

To connect a sub-report to the Main report, we need to create a parameter for passing the identification (guid) of the primary IMSMA Item between the sub-report and the main report. Below is the instructions to create the parameter you need for the sub-report:

1. At the Sub-report window, select View -> Parameters.

   The window displays the parameters.

2. Click the New button.

The Add/modify parameter window displays.

3. From the Add/modify parameter window:
• Enter “object_guid” for the Parameter Name where object_guid is the suggested parameter name in the Column D of the Table - Required Parameters for Sub-report below.
• Click the OK button.
4. Close the window that displays the list of all parameters.

Step 4 – Define Report Query for Sub-report

The next step is to create a report query for the sub-report you just created in Step 2 to define how the IMSMA item on the Main report is related to the IMSMA item on the Sub-report. Below is the instructions on how to define the Report Query:

23. At the Sub-report window, select Data -> Report Query.

The Report query window displays.

24. From the Report query window:
3. Select ImsmaQuery from the Query language list.
4. Enter object.guid = “$P{object_guid}” for the ImsmaQuery where object is the object type of the IMSMA item on the Main report - See Column E in the Table - Required Parameters for Sub-report below to determine the ImsmaQuery string for your Sub-report.

5. Click OK.

The Report query window closes.

6. Click the File:IRepCompile.png button to compile the sub-report.

Step 5 – Add Subreport Parameter to Main Report

We created a parameter on the sub-report to connect to the Main report in Step 3, now we need to create a parameter on the Main report as well. Below is the instructions to create the parameter you need for the Main report:

1. Make sure you are at the Main report by selecting the name of the Main report from the Window menu or double-clicking the Main report from the Files pane.
2. Double-click the sub-report box within the Main report.

A window displays four tabs (Common, Sub-report, Sub-report (Other), and All).

3. Select the Sub-report (Other) tab. Click Add.
4. From the Add/modify parameter window:
1. Enter “$P{object_guid}” for the Sub-report Parameter Name field where object_guid is the parameter name you created in Step 3 for the Sub-report. See Column B in the Table – Required Parameters for Sub-report below to determine the parameter name.
2. In the Default Value Expression field, enter $F{guid}.
3. Click OK and close the windowthe four tabs.

Step 6 – Repeat Step 2-5 for additional sub-reports.

You can have multiple sub-reports in one Main report. To add more sub-reports to the Main report, just repeat Step 2 to Step 5.

Once you have all the sub-reports created for your Main report, you can compile and run your Main report.

Below is the table listing typical parameters required for both Main report and Sub-report.

The following section of the Advance Reports chapter discusses relationships between Items in IMSMA and also provides instructions on how to create a specific report/sub-reports.

Reports on Items with a Direct Relationship

The reports discussed so far in this training module have been based on a single data source. In this part of the training module, you will learn to create more complex reports that reference multiple data sources. Some items are dependent on the existence of another item. This type of relationship is referred to as a direct relationship. For example, an accident, land, activity, education, QM, or victim has a direct relationship to the location to which it is associated. The accident, land, activity, education, QM, or victim cannot exist unless it is associated with a location. This direct relationship is enforced in IMSMA via the reconciliation process.

Direct relationships also exist when subsets of data are contained within an item. For example, an MRE may have MRE details such as the MRE type, total audience, and percentage of women and men. The MRE details data requires the MRE to exist.

The following sections will describe the process for creating reports on items that have a direct relationship between them.

Country Structure Areas and Associated Locations

Although a location must be associated with a country structure area, the process for creating a report of locations in a country structure area is very similar to a basic report. The location data source contains a field for each country structure area that is defined in your IMSMA database.

To create a report that lists locations within a country structure area:

1. From the File menu, select Report Wizard.

The iReport Wizard window displays.

2. From the Connection/Data Sources list, select Location Data Source.
3. Click the Next button.

The window to select the attributes to include in the report displays.

4. From the Step 2. select fields screen:
1. Select the country structure level(s) on which you would like to list the associated locations.
2. Select the fields that you would like to include in the report.
3. Click the Next button.

A window displays the selected fields.

5. Click the Next button.

The window group the report data displays.

6. From the Group lists, select the country structure level(s) that you selected in step 4a.

Be sure to select the highest country structure level for Group 1, followed by the next highest country structure level for Group 2, and so on.

7. Click the Next button.

The window to select a layout displays.

8. From the window to select a layout:
1. Select the layout option for the report.
2. Click the Next button.

The window displays a message stating that you have successfully created a new report.

3. Click the Finish button.

The template for your report displays.

4. Click the File:IRepCompile.png button to compile the report.

The Save report window displays.

5. Enter a name for the report in the File name field.
6. If there are no compilation errors, you may click the File:IRepRun.png button to run the report.

The results of the report display in the iReport JasperViewer window.

Location and Associated Items

A report of locations and associated items requires you to create an iReport sub-report. A sub-report is a report that is contained within another report. A report on locations and its associated item records will require you to create a report and a sub-report. The main report will contain the data for the location. For each associated item that you would like to include, you will need to create a sub-report.

All records in IMSMA have a guid. A guid is a unique value that identifies the record in the IMSMA database. Because the guid is lengthy and comprised of alphanumeric characters, it is typically not a useful value to display in a report. However, the guid is necessary in determining which item records have a direct relationship.

All accidents, lands, activities, educations, QMs, and victims are associated with a single location. The information about the location to which an item record is associated is stored in the location_guid field of the item table and corresponds to the location_guid of the location to which it is associated (Figure 13).

The location_guid field in the item table contains the guid of the location to which the item record is associated. Figure 14 shows an example of a location and the accident and land that are associated to the location. Notice that the location_guid value (1234-1234) in the accident and land are identical to the location_guid of the location.

Creating a report of a location and associated items will require you to complete the following parts:

• Part 1: Create a report with location fields
• Part 2: Create a sub-report for the associated item
• Part 3: Add parameters to the sub-report
• Part 4: Create a query the in sub-report to associate the guid to the location report
• Part 5: Add parameters to the location report
• Part 6: Compile and execute the location report

Part 1. Create a report with location fields

The first part of creating a report of locations and their associated items is to create a report of the location fields you would like to include.

1. Ensure that the IMSMA server is started.
2. From the File menu, select Report Wizard.
3. From the Connection/Data Sources list, select Location Data Source to create a report of locations.
4. Click the Next button.

The iReport Wizard, Step 2 window displays a list of the location attributes.

5. Select the fields you would like to include for the location.
6. Click the Next button.

The window displays a list of the attributes that you have selected.

7. Click the Next button.

The iReport Wizard, Step 3 window displays to allow you to group the report results.

8. Because the report results for this exercise will not need to be grouped, click the Next button.

The iReport Wizard, Step 4 window displays to allow you to select the report layout.

9. Select a layout option.
10. Click the Next button.

The iReport Wizard, Step 5 window displays.

11. Click the Finish button.
12. Save the report by doing one of the following:
• Click the File:IRepSave.png icon.
• From the File menu, select Save.

The Save report window displays.

13. Enter a name for the report in the File name field.
14. Click the Save button.

Part 2. Create a sub-report for the associated item

1. Expand the detail section of the report by doing the following:
1. From the View menu, select Bands.
2. From the left pane, select detail.
3. In the Band Height field that is in the right pane, increase the height of the detail section. At a minimum you will want to double the height.

3. Click the Apply button.
4. The height of the band increases based on the value that you entered.

2. Click the File:IRepBand.png button to add a sub-report.

The cursor changes to a crosshair.

3. Click the detail section of the report.
4. While holding down the mouse button, drag the cursor to form the rectangle which will contain the sub-report.
5. Release the mouse button.

The window below displays.

6. From the window:
1. Select the Create a new report option.
2. Click the Next button.

The How fill the sub-report screen displays.

7. From the window:
1. Select the data source for the associated item from the Connection/Data Source list. For example, if you are creating a report of locations and its associated accidents, you would select the accident data source.
2. Click the Next button.

The window to select the attributes to include in the report displays.

8. From the window:
1. Select the attributes that you would like to include in the report.
2. Click the Next button.

The window to select a layout displays.

9. From the window to select a layout:
1. Select the layout option.
2. Click the Next button.

The window to specify the sub-report expression displays.

10. From the window to specify the sub-report expression:
1. Enter a name for the sub-report.
2. Click the Finish button.

The sub-report displays.

Part 3. Add parameters to the sub-report

1. From the View menu, select Parameters.

The window displays the parameters.

1. Click the New button.

The Add/modify parameter window displays.

2. From the Add/modify parameter window:
1. In the Parameter Name field, enter a name for the parameter that will be used to identify the guid for the location.

Although there are no requirements for the parameter name, you should enter a name that is easy to remember and meaningful (for example, location or location_guid). Make note of this parameter name since it will used in a later step.

2. Click the OK button.
3. Close the window that displays the list of all parameters.

Part 4. Create a query in the sub-report to associate the guid to the location report

The next part of the process for creating a report on locations and its associated items is to create a query that defines how the location and associated item are related in the IMSMA database.

1. From the Data menu, select Report Query.

The Report query window displays.

2. From the Report query window:
1. Select ImsmaQuery from the Query language list.
2. In the field below the Query language list, enter location.guid = "$P{ParameterName}"

where ParameterName is the name of the parameter that you created for the location guid.

3. Click the OK button.

The Report query window closes.

3. Click the File:IRepCompile.png button to compile the sub-report.

Part 5. Add parameters to the location sub-report

The next part of the process for creating a report on locations and its associated items is to update the location report to include the parameter that will be used to associate the location record to item records.

1. Display the location report by doing one of the following:
• From the Window menu, select the name of the location report.
• Close the sub-report.
• Bring the location report to the foreground by double-clicking the location report name that is displayed in the Files pane.
2. Double-click the sub-report box within the report.

A window displays four tabs (Common, Sub-report, Sub-report (Other), and All).

3. From the window:
1. Select the Sub-report (Other) tab.
2. Click the Add button.

The Add/modify parameter window displays.

4. From the Add/modify parameter window:
1. In the Sub-report Parameter Name field, enter the parameter name that you used for the location.

This is the parameter that you created previously.

2. In the Default Value Expression field, enter $F{guid}.
3. Click the OK button.
5. Close the window with the four tabs.

Part 6. Compile and execute the location report

1. Ensure that the Location data source is selected from the list.
2. Click the File:IRepCompile.png button to compile the report.
3. Click the File:IRepRun.png button to execute the report.

The report results displays.

Items with Data Subsets

Certain items have subsets of data that are contained within them. For example, an MRE may have MRE details such as the MRE type, total audience, and percentage of women and men. The MRE details data requires the MRE to exist. Table 3. Parent/child relationships lists the items and the data subsets that depend on them.

The process for creating a report on a parent item and its children is similar to the process for creating a report on locations and their associated items. The key difference is that the main report will contain the attributes of the parent item. The sub-report will contain the attributes for the child item. For example, if you were trying to create a report of MREs and their associated MRE details, the main report would contain the attributes for MRE and the sub-report would contain the attributes for MRE details.

Part 1. Create a report with attributes for a parent item

The first part of creating a report on a parent and its child items is to create a report which includes the attributes from the parent item you would like to include.

1. Ensure that the IMSMA server is started.
2. From the File menu, select Report Wizard.
3. From the Connection/Data Sources list, select the data source for the parent item.

Table 3. Parent/child relationships lists the items which may be selected as the parent item for a report.

1. Click the Next button.

The iReport Wizard, Step 2 window displays a list of attributes for the selected item.

2. Select the attributes you would like to include for the parent item.
3. Click the Next button.

The window displays a list of the attributes that you have selected.

4. Click the Next button.

The iReport Wizard, Step 3 window displays to allow you to group the report results.

5. Because the report results for this exercise will not need to be grouped, click the Next button.

The iReport Wizard, Step 4 window displays to allow you to select the report layout.

6. Select a layout option.
7. Click the Next button.

The iReport Wizard, Step 5 window displays.

8. Click the Finish button.
9. Save the report.

Part 2. Create a sub-report for the child item

The next part of the process for creating a report on a parent and its child items requires you to create a sub-report within the parent item report.

1. Expand the detail section of the report by doing the following:
1. From the View menu, select Bands.
2. From the left pane, select detail.
3. In the Band Height field that is in the right pane, increase the height of the detail section. At a minimum you will want to double the height.
4. Click the Apply button.

The height of the band increases based on the value that you entered.

You may also expand the height of the detail band by dragging the line that separates the detail section of the report and the page footer section down.

2. Click the File:IRepCompile.png button to add a sub-report.

The cursor changes to a crosshair.

3. Click the detail section of the report.
4. While holding down the mouse button, drag the cursor to form the rectangle which will contain the sub-report.
5. Release the mouse button.

The window to create a new report or use an existing one displays.

6. From the window:
1. Select the Create a new report option.
2. Click the Next button.

The How fill the sub-report screen displays.

7. From the window:
1. Select the data source for the child item from the Connection/Data Source list.
2. Click the Next button.

The window to select the attributes to include in the report displays.

8. From the window:
1. Select the attributes that you would like to include in the report.
2. Click the Next button.

The window to select a layout displays.

9. From the window to select a layout:
1. Select the layout option.
2. Click the Next button.

The window to specify the sub-report expression displays.

10. From the window to specify the sub-report expression:
1. Enter a name for the sub-report.
2. Click the Finish button.

The sub-report displays.

Part 3. Add parameters to the sub-report

The next part of the process for creating a report on a parent and its child items is to define parameters that will be used for passing the parent item guid between the sub-report and main report.

1. From the View menu, select Parameters.

The window displays the parameters.

2. Click the New button.

The Add/modify parameter window displays.

3. From the Add/modify parameter window:
1. In the Parameter Name field, enter a name for the parameter that will be used to identify the guid for the parent item.

Although there are no requirements for the parameter name, you should enter a name that is easy to remember and meaningful. Make note of this parameter name since it will used in a later step.

2. Click the OK button.
4. Close the window that displays the list of all parameters.

Part 4. Create a query in the sub-report to associate the guid to the parent item report

The next part of the process for creating a report on locations and its associated items is to create a query that defines how the location and associated item are related in the IMSMA database.

1. From the Data menu, select Report Query.

The Report query window displays.

2. From the Report query window:
1. Select ImsmaQuery from the Query language list.
2. In the field below the Query language list, enter ParentItem.guid = "$P{ParameterName}"

where ParentItem is the default value expression for the parent item and ParameterName is the name of the parameter that you created for the parent item guid.

3. Click the OK button.

The Report query window closes.

4. Click the File:IRepCompile.png button to compile the sub-report.

Part 5. Add parameters to the parent report

The next part of the process for creating a report on a parent and its child items is to update the parent report to include the guid attribute and parameters that will be used to associate the parent item to the child item.

1. Display the parent item report by doing one of the following:
• From the Window menu, select the name of the report.
• Close the sub-report.
• Bring the parent item report to the foreground by double-clicking the report name that is displayed in the Files pane.
2. Double-click the sub-report box within the report.

A window displays four tabs (Common, Sub-report, Sub-report (Other), and All).

3. From the window:
1. Select the Sub-report (Other) tab.
2. Click the Add button.

The Add/modify parameter window displays.

4. From the Add/modify parameter window:
1. In the Sub-report Parameter Name field, enter the parameter name that you used for the parent item guid.

This is the parameter that you created in step previously.

2. In the Default Value Expression field, enter $F{guid}.
3. Click the OK button.
5. Close the window with the four tabs.

Part 6. Compile and execute the parent item report

1. Ensure that the data source for the parent item is selected from the list.
2. Click the File:IRepCompile.png button to compile the report.
3. Click the File:IRepRun.png button to execute the report.

The report results displays.

Reports on Linked Items

Part 4 of this training module discussed direct relationships—relationships where the parent item must exist in order for the child item to exist. In this part of the training, you will learn about linked relationships. Linked relationships are optional relationships which may exist between items. Unlike direct relationships that are enforced during the IMSMA reconciliation process, links are optional. Accidents, lands, activities, educations, and QMs may be related to one another via links. For example, a victim may be associated to an accident in a particular land. If these relationships are known, IMSMA users may define these links while reconciling the field report.

The process for creating a report on an item and its linked items is similar to the process for creating a report on items with a direct relationship. The item in the main report is the primary item for which you would like to find linked items. The sub-report would return the records for the particular item that is linked to the primary item. The key difference is the query used in the sub-report.

If you would like to report on different items that linked to the item in the main report, you may create a new sub-report for each linked item. For example, to create a report of lands and the accidents and activities that are linked to those lands, you would create the main report on lands. This report would then contain a sub-report to list linked accidents, and another sub-report to list linked activities.

Part 1. Create a report with attributes for a primary item

The first part of creating a report on linked items is to create a report which includes the attributes from the primary item for which you would like to list linked items.

1. Ensure that the IMSMA server is started.
2. From the File menu, select Report Wizard.
3. From the Connection/Data Sources list, select the data source for the primary item.

Table 3. Parent/child relationships lists the items which may be selected as the primary item for a report.

4. Click the Next button.

The iReport Wizard, Step 2 window displays a list of attributes for the selected item.

5. Select the attributes you would like to include for the primary item.
6. Click the Next button.

The window displays a list of the attributes that you have selected.

7. Click the Next button.

The iReport Wizard, Step 3 window displays to allow you to group the report results.

8. Because the report results for this exercise will not need to be grouped, click the Next button.

The iReport Wizard, Step 4 window displays to allow you to select the report layout.

9. Select a layout option.
10. Click the Next button.

The iReport Wizard, Step 5 window displays.

11. Click the Finish button.
12. Save the report by doing one of the following:
• Click the File:IRepSave.png icon.
• From the File menu, select Save.

The Save report window displays.

13. Enter a name for the report in the File name field.
14. Click the Save button.

Part 2. Create a sub-report for the linked item

The next part of the process for creating a report on linked items requires you to create a sub-report for the item to which the primary item is linked.

1. Expand the detail section of the report.
2. Click the File:IRepBand.png button to add a sub-report.

The cursor changes to a crosshair.

3. Click the detail section of the report.
4. While holding down the mouse button, drag the cursor to form the rectangle which will contain the sub-report.
5. Release the mouse button.

The window to create a new report or use an existing one displays.

6. From the window:
1. Select the Create a new report option.
2. Click the Next button.

The How fill the sub-report screen displays.

7. From the window:
1. Select the data source for the child item from the Connection/Data Source list.
2. Click the Next button.

The window to select the attributes to include in the report displays.

8. From the window:
1. Select the attributes that you would like to include in the report.
2. Click the Next button.

The window to select a layout displays.

9. From the window to select a layout:
1. Select the layout option.
2. Click the Next button.

The window to specify the sub-report expression displays.

10. From the window to specify the sub-report expression:
1. Enter a name for the sub-report.
2. Click the Finish button.

The sub-report displays.

Part 3. Add the parameters to the sub-report

The next part of the process for creating a report on linked items is to define parameters that will be used for passing the guid of the primary item between the sub-report and main report.

1. Click the New button.

The Add/modify parameter window displays.

2. From the Add/modify parameter window:
1. In the Parameter Name field, enter a name for the parameter that will be used to identify the guid for the primary item.

Although there are no requirements for the parameter name, you should enter a name that is easy to remember and meaningful. Make note of this parameter name since it will used in a later step.

2. Click the OK button.
3. Close the window that displays the list of all parameters.

Part 4. Create a query in the sub-report to associate the guid to the primary report item

The next part of the process for creating a report on linked items is to create a query that defines how the primary item and the linked item are related in the IMSMA database.

1. From the Data menu, select Report Query.

The Report query window displays.

2. From the Report query window:
1. Select ImsmaQuery from the Query language list.

The following query is different from the query used when creating a report on items with data subsets.

2. In the field below the Query language list, enter links.toGuid = "$P{ParameterName}"

where ParameterName is the name of the parameter that you created for the parent item guid.

3. Click the OK button.

The Report query window closes.

3. Ensure that the Query Executor data source is selected from the list.
4. Click the button to compile the sub-report.

Part 5. Add attributes and parameters to the main report

The next part of the process for creating a report on linked items is to update the main report to include the guid attribute and parameters that will be used to associate the primary item to the linked item.

1. Display the main report by doing one of the following:
• From the Window menu, select the name of the report.
• Close the sub-report.
• Bring the primary item report to the foreground by double-clicking the report name that is displayed in the Files pane.
2. Double-click the sub-report box within the report.

A window displays four tabs (Common, Sub-report, Sub-report (Other), and All).

3. From the window:
1. Select the Sub-report (Other) tab.
2. Click the Add button.

The Add/modify parameter window displays.

4. From the Add/modify parameter window:
1. In the Sub-report Parameter Name field, enter the parameter name that you used for the primary report item guid.

This is the parameter that you created previously.

2. In the Default Value Expression field, enter $F{guid}.
3. Click the OK button.
5. Close the window with the four tabs.

Part 6. Compile and execute the main report

1. Ensure that the data source for the item in the main report is selected from the list.
2. Click the File:IRepCompile.png button to compile the report.
3. Click the File:IRepRun.png button to execute the report.

The report results displays.

Template:NavBox HowTo use iReport