Difference between revisions of "Create iReport Templates with Subreports"

From IMSMA Wiki
Jump to: navigation, search
(Other subreport relationships)
 
(27 intermediate revisions by 5 users not shown)
Line 1: Line 1:
__FORCETOC__
 
 
{{TOC right}}
 
{{TOC right}}
{{HowTo's
+
==Creating Subreports==__NOEDITSECTION__
|[[Start and Configure i-Report|Start and Configure i-Report]]
+
A subreport is a report 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 subreport 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).
|[[Create a Basic i-Report Template|Create a Basic i-Report Template]]
 
|[[Modify an iReport Template|Modify an iReport Template]]
 
|[[Format an iReport Template|Format an iReport Template]]
 
|[[Create i-Report Templates with Subreports|Create i-Report Templates with Subreports]]
 
|[[Summarize i-Report Template Data|Summarize i-Report Template Data]]
 
}}
 
  
==Sub-Reports==
+
Regardless of what IMSMA data you’re including in your report and subreports, there are 5 basic steps to create a report with subreports:
<p>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).</p>
+
:Step 1 – Create the main report
<p>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:</p>
+
:Step 2 – Add a subreport to the main report
 +
:Step 3 – Add a parameter to the subreport
 +
:Step 4 – Define the report query for the subreport
 +
:Step 5 – Add subreport parameter to the main report
  
:Step 1 – Create the Main report
+
====Step 1 – Create the Main Report====__NOEDITSECTION__
: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
 
  
[[Image:iRepStep01.png|center|500px|''Step 1 – Create the Main Report'']]
+
Before creating the main report, you will need to determine what data should be included in 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 subreport 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.
 
 
====''Step 1 – Create the Main Report''====
 
 
 
<p>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.</p>
 
 
*What are all the data to be included on the 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 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.
+
*What is the target IMSMA item for the subreports?  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?
+
*Determine the relationship between the primary data and the secondary data.  How did they get reconciled to one another? How are they linked?
  
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 [[Create a Basic i-Report Template]] section.
+
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 [[Create a Basic iReport Template]] section.
  
====''Step 2 – Add Subreport to the Main report''====
+
====Step 2 – Add Subreport to the Main report====__NOEDITSECTION__
  
Once the main report has been created you can add a subreport to the main report. The intent of the subreport is to bring data from a different but related datasource into the main report. For example, you could include Activity data on a report about Land.  Below is the instructions to create a sub-report:</p>
+
Once the main report has been created you can add a subreport to the main report. The intent of the subreport is to bring data from a different but related datasource into the main report. For example, you could include Activity data on a report about Land.  Below are the instructions to create a subreport:
  
# On the main report, from the '''Palette''' pane, select '''Subreport''' and drag it to the desired band on the iReport template (normally this is the '''Detail''' band.
+
# On the main report, from the '''Palette''' pane, select '''Subreport''' and drag it to the desired band on the iReport template (normally this is the '''Detail''' band).
 
#:[[Image:IReportSubReportPalette.png|center|300px|''Subreport Palette'']]<div align="center">''Subreport Palette''</div>
 
#:[[Image:IReportSubReportPalette.png|center|300px|''Subreport Palette'']]<div align="center">''Subreport Palette''</div>
 
# Release the button and the window below displays.
 
# Release the button and the window below displays.
#:[[Image:IReportSubReport1.png|center|500px|''Add Sub-report to the Main report'']]
+
#:[[Image:IReportSubReport1.png|center|500px|''Add Subreport to the Main report'']]
# Click '''Next''' to define a new sub-report unless you have an existing report that you would like to use for sub-report.
+
# Click '''Next''' to define a new subreport unless you have an existing report that you would like to use for subreport.
 
# Select the desired iReport Template to apply (Blank is recommended).
 
# Select the desired iReport Template to apply (Blank is recommended).
 
#:[[Image:IReportSubReport2.png|center|500px|''Select the desired iReport Template'']]
 
#:[[Image:IReportSubReport2.png|center|500px|''Select the desired iReport Template'']]
# 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.
+
# 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 “Hazard Reduction” should be the data source for the subreport. Click Next.
 
#:[[Image:IReportSubReport3.png|center|500px|''Select the desired datasource'']]
 
#:[[Image:IReportSubReport3.png|center|500px|''Select the desired datasource'']]
# Select the attributes to be included in your sub-report.  Click '''Next'''.
+
# Select the attributes to be included in your subreport.  Click '''Next'''.
 
#:[[Image:IReportSubReport4.png|center|500px|''Select the desired fields for the subreport'']]
 
#:[[Image:IReportSubReport4.png|center|500px|''Select the desired fields for the subreport'']]
# Enter a name for the sub-report or use the default. Choose '''Next'''.
+
# Select the fields to group by. This step is not required.  Click '''Next'''.
 +
#:[[Image:Subreport_Group_by.png|center|500px|''Select the fields to group by'']]
 +
# Enter a name for the subreport or use the default. Choose '''Next'''.
 
#:[[Image:IReportSubReport5.png|center|500px|''Enter a name for the subreport'']]  
 
#:[[Image:IReportSubReport5.png|center|500px|''Enter a name for the subreport'']]  
# Select '''Use the same connection use to fill master report'''. Click Finish.   
+
# Select '''Use the same connection used to fill master report'''. Click Finish.   
 
#:[[Image:IReportSubReport6.png|center|500px|''Use same connection'']]  
 
#:[[Image:IReportSubReport6.png|center|500px|''Use same connection'']]  
# The sub-report displays.
+
# The subreport displays.
  
[[Image:iRepStep03.png|center|500px|''Step 3 – Add Parameter to Sub-report'']]
+
====Step 3 – Add Parameter to Subreport====__NOEDITSECTION__
  
====Step 3 – Add Parameter to Subreport====
+
To connect a subreport to the main report, we next need to create a parameter for passing the identification (guid) of the primary IMSMA item between the subreport and the main report. In the '''Report Inspector''' on the subreport, [[Modify an iReport Template#Adding a Parameter to an iReport Template|add a Parameter]] to hold the guid of the IMSMA item on the main report. It is recommended to add a fairly generic parameter name such as ''ItemGUID'' so that the subreport can be more easily re-used.
 
 
To connect a subreport to the Main report, we next need to create a parameter for passing the identification (guid) of the primary IMSMA Item between the subreport and the main report. In the '''Report Inspector''' on the subreport, [[Modify an iReport Template#Adding a Parameter to an iReport Template|add a Parameter]] to hold the guid of the IMSMA Item on the main report. It is recommended to add a fairly generic parameter name such as ''ItemGUID'' so that the subreport can be more easily re-used.
 
  
 
# From the Report Inspector, '''right click''' on the '''Parameter''' tree and choose '''Add Parameter'''.
 
# From the Report Inspector, '''right click''' on the '''Parameter''' tree and choose '''Add Parameter'''.
 
#: [[Image:IReport AddParameter1.png|center|250px|''Add Parameter'']]
 
#: [[Image:IReport AddParameter1.png|center|250px|''Add Parameter'']]
# In the '''Properties''' pane, enter a parameter name (e.g. ItemGUID} and edit any other properties. Normally it is not required to edit anything other than the name.
+
# In the '''Properties''' pane, enter a parameter name (e.g. ItemGUID} and edit any other properties. Normally it is not required to edit anything other than the name, you might however want to deselect the '''Use as a prompt''' option.
 
#: [[Image:IReport AddParameter2.png|center|250px|''Add Parameter'']]
 
#: [[Image:IReport AddParameter2.png|center|250px|''Add Parameter'']]
 
# Once the parameter is added it will be available in the '''Report Inspector''' and can be referenced as ''$P{parametername}''. For example:
 
# Once the parameter is added it will be available in the '''Report Inspector''' and can be referenced as ''$P{parametername}''. For example:
 
  $P{ItemGUID}
 
  $P{ItemGUID}
  
====Step 4 – Define Report Query for Subreport====
+
====Step 4 – Define Report Query for Subreport====__NOEDITSECTION__
  
The next step is to create a report query for the subreport you just created in Step 3 to define how the IMSMA item on the Main report is related to the IMSMA item on the subreport. Typically the subreport Report Query will receive the guid from the main report via the parameter created in Step 4 and then use it to limit items in the subreport to the those linked to the main report.  Below is the instructions on how to define the Report Query:
+
The next step is to create a report query for the subreport you just created in Step 2 to define how the IMSMA item on the main report is related to the IMSMA item on the subreport. Typically the subreport report query will receive the guid from the main report via the parameter created in Step 3 and then use it to limit items in the subreport to those linked to the main report.  Below is the instructions on how to define the report query:
  
# Open the [[Modify an iReport Template#Using Report Query filters|Report Query window]]  
+
# Open the [[Modify an iReport Template#Using Report Query filters|report query window]].
# From the Report query window, select '''ImsmaQuery''' from the Query language list.
+
# From the report query window, select '''ImsmaQuery''' from the query language list.
# Enter a Report Query filter using the parameter defined above to limit the objects according to the main report object. The most common Report Query filter would be to filter to only linked objects. For example '''''links.toGuid="$P{ItemGUID}"'''''. In this step, the Report Query tells the subreport to return only rows that are linked to the guid of the the item in the main report via the link table. Other combinations of Report Query filters can be used on subreports beyond the link table. These are documented in the [[#Possible Subreport Relationships|Possible Subreport Relationships]] section below.
+
# Enter a report query filter using the parameter defined above to limit the objects according to the main report object. The most common report query filter would be to filter to only linked objects. For example '''''links.toGuid="$P{ItemGUID}"'''''. In this step, the Report Query tells the subreport to return only rows that are linked to the guid of the item in the main report via the link table. Other combinations of report query filters can be used on subreports beyond the link table. These are documented in the [[#Example Subreport Relationships|Possible Subreport Relationships]] section below.
# Click '''OK'''. :The Report query window closes.  
+
# Click '''OK'''. The report query window closes.  
 
# Save the subreport.
 
# Save the subreport.
  
=====Possible Subreport Relationships=====
+
====Step 5 – Link the Subreport to the Main Report====__NOEDITSECTION__
{| class="wikitable"
+
Now that both the main and the subreport are ready, the only thing that remains to be done is linking the two together. Below are the instructions to link the two reports:
| align="center" style="background:#f0f0f0;"|'''Main Report'''
+
# Make sure you are at the main report by selecting the name of the main report from the tabs at the top of the iReport Designer canvas pane.
| align="center" style="background:#f0f0f0;"|'''Subreport can be'''
+
# Select the subreport box within the main report.
| align="center" style="background:#f0f0f0;"|'''Value to Pass to Subreport'''
 
| align="center" style="background:#f0f0f0;"|'''Report Query'''
 
|-
 
| Location||Any Item with a Location||location guid||location.guid = "$P{ItemGUID}"
 
|-
 
| Any linked item||Any linked Item||Item guid (e.g. Land guid)||links.toGuid="$P{ItemGUID}"
 
|-
 
| Activity||Impact survey, Interviewee, or Vegetation removal||Activity guid||hazreduc.guid = "$P{ItemGUID}"
 
|-
 
| Interviewee||Question||interviewee guid||interviewee.guid = “$P{ItemGUID}”
 
|-
 
| Task||Work Item||Task guid||task.guid = "$P{ItemGUID}"
 
|-
 
| Any Item with Geospatial information||GeospatialInfo||geoSpatialInfos||guid IN $P{GeospatialInfoList}
 
|-
 
| Geospatial Info||GeoPoint||GeospatialInfo guid||geospatialInfoGuid = "$P{ItemGUID}"
 
|-
 
| Any item with devices||Item Device (e.g. AccidentDevice)||Item guid (e.g. Accident guid)||accident.guid = "$P{ItemGUID}" hazard.guid = "$P{ItemGUID}"
 
|-
 
| MRE||MREDetail||MRE guid||mre.guid = "$P{ItemGUID}"
 
|-
 
| Victim||Victim Needs Assessment||victim guid||victim.guid="$P{ItemGUID}"
 
|-
 
| Victim||Victim Cause||victim guid||victim.guid="$P{ItemGUID}"
 
|-
 
| Victim Assistance||Victim Assistance Assistance||victimAssistance.guid||victimAssistance.guid="$P{ItemGUID}"
 
|-
 
| Task||Objective||SQL Field list of Objectives||guid IN ($P{SQLList})
 
|-
 
| Work Item||Linked Items (Activity, Education, QM, Victim Assistance)||SQL Field list of Items||guid IN ($P{SQLList})
 
|-
 
|
 
|}
 
 
 
====Step 5 – Add Subreport Parameter to Main Report====
 
 
 
We created a parameter on the sub-report to connect to the Main report in Step 4, 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:
 
# Make sure you are at the Main report by selecting the name of the Main report from the tabs at the top of the iReport Designer canvas pane.
 
# Select the subreport box within the Main report.
 
 
#: [[Image:IReportSubReportSelection.png|center|500px|''Select the Subreport on the main report'']]<div align="center">''Select the Subreport on the main report''</div>
 
#: [[Image:IReportSubReportSelection.png|center|500px|''Select the Subreport on the main report'']]<div align="center">''Select the Subreport on the main report''</div>
 
# On the '''Properties''' pane for the Subreport, select the '''Parameter''' selector.
 
# On the '''Properties''' pane for the Subreport, select the '''Parameter''' selector.
 
#: [[Image:IReport_SubReportProperties.png|center|200px|''Subreport properties'']]<div align="center">''Subreport properties''</div>
 
#: [[Image:IReport_SubReportProperties.png|center|200px|''Subreport properties'']]<div align="center">''Subreport properties''</div>
# '''Add''' a parameter and give it a name (e.g. ''ItemGUID'')
+
# '''Add''' a parameter and type '''exactly the name of the subreport parameter created in step 3''' (e.g. ''ItemGUID'')
 
#: [[Image:IReport_SubReportParameter.png|center|200px|''Add a parameter'']]<div align="center">''Add a parameter''</div>
 
#: [[Image:IReport_SubReportParameter.png|center|200px|''Add a parameter'']]<div align="center">''Add a parameter''</div>
 
# For the '''value expression''', select the appropriate field to include in the parameter. '''Normally this is ''$F{guid}''''' but other choices are available.
 
# For the '''value expression''', select the appropriate field to include in the parameter. '''Normally this is ''$F{guid}''''' but other choices are available.
 
#: [[Image:IReport_SubReportParameter2.png|center|200px|''Select the appropriate field'']]<div align="center">''Select the field to include in the parameter''</div>
 
#: [[Image:IReport_SubReportParameter2.png|center|200px|''Select the appropriate field'']]<div align="center">''Select the field to include in the parameter''</div>
# Click '''OK''' and close Subreport properties windows.
+
# Click '''OK''' and close Subreport Parameters window.
  
[[Image:iRepStep06.png|center|500px|''Step 6 – Repeat Step 2-5 for additional sub-reports.'']]
+
You are now ready to run your main report. Running (i.e. previewing) the main report will automatically run the subreport(s) as well, and the data from the main as well as from the subreport(s) will be included in the output.
====Step 6 – Repeat Step 2-5 for additional sub-reports.====__NOEDITSECTION__
 
  
<p>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.</p>
+
==Kinds of Subreports==__NOEDITSECTION__
<p>Once you have all the sub-reports created for your Main report, you can compile and run your Main report.</p>
+
There are three main kinds of relationships in IMSMA on which subreports can be generated from IMSMA data. These are:
 
 
==Kinds of Subreports==
 
There are three main kinds relationships in IMSMA on which subreports can be generated from IMSMA data. These are:
 
 
*Location relationship
 
*Location relationship
 
*Link relationship
 
*Link relationship
Line 142: Line 86:
 
*Other relationship
 
*Other relationship
  
===Location relationship subreports===
+
===Location relationship subreports===__NOEDITSECTION__
Because each of the main items in {{IMSMANG}},
+
Because each of the main items in {{IMSMANG}}, Accident, Activity, Assistance, Education, Land, QM, and Victim, must be reconciled to a Location during the data entry phase, each of these objects has a relationship with Location and can thus be a subreport on a Location main report. Since all accidents, land, activities, educations, QMs, assistances, and victims are associated with a single location each record knows what location it is related to based on the '''''location guid''''' field. When creating a report where the main report is a Location and the subreport is one of the associated objects the Location's guid is passed to the subreport in a parameter and the subreport can use a special ImsmaQuery language syntax to limit the subreport data to those associated with the main report's location:
*Accident  
 
*Activity
 
*Assistance  
 
*Education
 
*Land
 
*QM
 
*Victim
 
must be reconciled to a Location during the data entry phase, each of these objects has a relationship with Location and can thus be a subreport on a Location main report. Since all accidents, land, activities, educations, QMs, assistances, and victims are associated with a single location each record knows what location it is related to based on the '''''location guid''''' field. When creating a report where the main report is a Location and the subreport is one of the associated objects the Location's guid is passed to the subreport in a parameter and the subreport can use a special ImsmaQuery language syntax to limit the subreport data to those associated with the main report's location:
 
 
  location.guid = "$P{parametername}"
 
  location.guid = "$P{parametername}"
  
 
The location guid field in the subreport item table contains the guid of the location to which the item record is associated. The figure below 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.
 
The location guid field in the subreport item table contains the guid of the location to which the item record is associated. The figure below 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.
  
[[Image:iRep14.png|center|500px|''Figure 14. location_guid example'']]
+
[[Image:iRep14.png|center|500px|''location_guid example'']]
 
<div align="center">
 
<div align="center">
''Figure 14. location_guid example''
+
''location_guid example''
 
</div>
 
</div>
  
 
To create a subreport using the Location relationship:
 
To create a subreport using the Location relationship:
# From the main "Location" report, pass the location '''''guid''''' to the subreport in a parameter (e.g. in a parameter called '''ItemGUID''')
+
# From the main "Location" report, pass the location '''''guid''''' to the subreport in a parameter (e.g. in a parameter called '''ItemGUID''').
 
# Create a parameter on the subreport with the same name as in #1.
 
# Create a parameter on the subreport with the same name as in #1.
 
# In the subreport for the related item (e.g. a Victim) use the parameter in the following ImsmaQuery language syntax
 
# In the subreport for the related item (e.g. a Victim) use the parameter in the following ImsmaQuery language syntax
 
  location.guid="$P{parametername}"
 
  location.guid="$P{parametername}"
  
This has the effect of limiting the subreport records to '''only those records that are associated to the location'''' from the main report.
+
This has the effect of limiting the subreport records to '''only those records that are associated to the location''' from the main report.
  
===Link relationship subreports===
+
===Link relationship subreports===__NOEDITSECTION__
In addition to the Location relationship, the main items in {{IMSMANG}} can also be linked with one another during the reconciliation process. This is how, for example, Activities are linked with Land in the {{IMSMANG}} database. Accidents, land, activities, educations, assistances, QMs, and victims 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 Data Entry Form. This special relationship can also be modeled in iReport using a report and subreport  
+
In addition to the Location relationship, the main items in {{IMSMANG}} can also be linked with one another during the reconciliation process. This is how, for example, Activities are linked with Land in the {{IMSMANG}} database. Accidents, land, activities, educations, assistances, QMs, and victims 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 Data Entry Form. This special relationship can also be modeled in iReport using a report and subreport(s).
 
   
 
   
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 Location 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.
+
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 Location relationship. The item in the main report is the primary item for which you would like to find linked items. The subreport would return the records for the particular item that is linked to the primary item. The key difference is the query used in the subreport.
  
 
To create a subreport using the link relationship:
 
To create a subreport using the link relationship:
# From the main report, pass the '''''guid''''' of the main report object to the subreport in a parameter (e.g. pass an accident's '''''guid''''' in a parameter called '''ItemGUID''')
+
# From the main report, pass the '''''guid''''' of the main report object to the subreport in a parameter (e.g. pass an accident's '''''guid''''' in a parameter called '''ItemGUID''').
 
# Create a parameter on the subreport with the same name as in #1.
 
# Create a parameter on the subreport with the same name as in #1.
 
# In the subreport for the related item (e.g. a Victim) use the parameter in the following ImsmaQuery language syntax
 
# In the subreport for the related item (e.g. a Victim) use the parameter in the following ImsmaQuery language syntax
Line 182: Line 118:
 
This has the effect of limiting the subreport records to only those records that have a '''link''' to the guid or object passed from the main report.
 
This has the effect of limiting the subreport records to only those records that have a '''link''' to the guid or object passed from the main report.
  
===Geospatial relationships===
+
===Geospatial relationships===__NOEDITSECTION__
 
In addition to Location and Link relationships, many objects in IMSMA can store geospatial/geographic information (i.e. point/polygon information). This relationship with the geospatial data can be modeled in a report/subreport relationship for any item that has geospatial information associated with it. To include point/polygon information on an object's main report, follow 3 basic steps:
 
In addition to Location and Link relationships, many objects in IMSMA can store geospatial/geographic information (i.e. point/polygon information). This relationship with the geospatial data can be modeled in a report/subreport relationship for any item that has geospatial information associated with it. To include point/polygon information on an object's main report, follow 3 basic steps:
  
 
*Step 1: Create a main report for an object with geospatial info
 
*Step 1: Create a main report for an object with geospatial info
*Step 2: Create a GeospatialInfo subreport on the main report
+
*Step 2: Create a GeospatialInfo subreport on the main report, i.e. a report using the GeospatialInfo datasource
*Step 3: Create a Geopoint subreport on the GeospatialInfo report
+
*Step 3: Create a Geopoint subreport on the GeospatialInfo report, i.e. a report using the Geopoint datasource
  
 
Step 1: Create a main report for an object with geospatial info
 
Step 1: Create a main report for an object with geospatial info
Line 205: Line 141:
 
  geospatialinfoguid IN $P{parametername}
 
  geospatialinfoguid IN $P{parametername}
  
This has the effect of limiting the Geospatial records to only those records whose guid is linked to the geospatialinfo of the main report.
+
This has the effect of limiting the Geospatial records to only those records whose guid is linked to the geospatialinfo of the main report. This is an example of nested reports, i.e. a main report, a subreport, and a sub-subreport.
  
===Other subreport relationships===
+
===Other subreport relationships===__NOEDITSECTION__
Certain items in {{IMSMANG}} have subsets of data that are contained within them. For example, an education may have education details such as the education type, total audience, and percentage of women and men. Other sub
+
Certain items in {{IMSMANG}} have subsets of data that are contained within them. For example, an education may have education details such as the education type, total audience, and percentage of women and men. Examples of other subreport relationships include:
  
 
<center>
 
<center>
 
{| class="wikitable" width="600"
 
{| class="wikitable" width="600"
 
|-
 
|-
| align="center" colspan="2" | '''Table 3. Parent/child relationships'''
+
| align="center" colspan="2" | '''Other subreport relationships'''
 
|-
 
|-
 
| width="150pt" | '''Parent item'''
 
| width="150pt" | '''Parent item'''
| width="450pt" | '''Child item(s) which require the parent item'''
+
| width="450pt" | '''Child item(s)'''
 
|-
 
|-
| Activity  || Hazard Reduction Device
+
| Activity  || Activity Device
  
 
Impact survey
 
Impact survey
  
 
Interviewee
 
Interviewee
 
Organisation
 
  
 
Question
 
Question
 
Vegetation removal
 
  
 
|-
 
|-
Line 235: Line 167:
 
| Victim || Received Education
 
| Victim || Received Education
  
Victim Needs Assessment
+
Needs Assessment
  
Victim Cause
+
Cause
 
+
Needs
 
 
|-
 
|-
| Land || Hazard Device
+
| Land || Land Device
 
|-
 
|-
 
| Accident || Accident Device
 
| Accident || Accident Device
 
|-
 
|-
| Victim Assistance || Victim Assistance Assistance
+
| Assistance || Assistance Given
 
|-
 
|-
 
| Task || Work Item
 
| Task || Work Item
|
+
|}
}
 
 
</center>
 
</center>
  
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 educations and their associated education details, the main report would contain the attributes for education and the sub-report would contain the attributes for education details.  
+
The process for creating a report on a parent item and its children is the same as any other report/subreport relationship.  
  
 
+
===Example Subreport Relationships===__NOEDITSECTION__
{{NavBox HowTo use iReport}}
+
{| class="wikitable"
 +
| align="center" style="background:#f0f0f0;"|'''Main Report'''
 +
| align="center" style="background:#f0f0f0;"|'''Subreport can be'''
 +
| align="center" style="background:#f0f0f0;"|'''Value to Pass to Subreport'''
 +
| align="center" style="background:#f0f0f0;"|'''Report Query'''
 +
|-
 +
| Location||Any Item with a Location||location guid||location.guid = "$P{ItemGUID}"
 +
|-
 +
| Any linked item||Any linked Item||Item guid (e.g. Land guid)||links.toGuid="$P{ItemGUID}"
 +
|-
 +
| Activity||Impact survey or Interviewee||Activity guid||hazreduc.guid = "$P{ItemGUID}"
 +
|-
 +
| Interviewee||Question||interviewee guid||interviewee.guid = “$P{ItemGUID}”
 +
|-
 +
| Task||Work Item||Task guid||task.guid = "$P{ItemGUID}"
 +
|-
 +
| Any Item with Geospatial information||GeospatialInfo||geoSpatialInfos||guid IN $P{GeospatialInfoList}
 +
|-
 +
| Geospatial Info||GeoPoint||GeospatialInfo guid||geospatialInfoGuid = "$P{ItemGUID}"
 +
|-
 +
| Any item with devices||Item Device (e.g. AccidentDevice)||Item guid (e.g. Accident guid)||accident.guid = "$P{ItemGUID}" hazard.guid = "$P{ItemGUID}"
 +
|-
 +
| MRE||MREDetail||MRE guid||mre.guid = "$P{ItemGUID}"
 +
|-
 +
| Victim||Needs Assessment||victim guid||victim.guid="$P{ItemGUID}"
 +
|-
 +
| Victim||Cause||victim guid||victim.guid="$P{ItemGUID}"
 +
|-
 +
| Assistance||Assistance Given||victimAssistance.guid||victimAssistance.guid="$P{ItemGUID}"
 +
|-
 +
| Task||Objective||SQL Field list of Objectives||guid IN ($P{SQLList})
 +
|-
 +
| Work Item||Linked Items (Activity, Education, QM, Assistance)||SQL Field list of Items||guid IN ($P{SQLList})
 +
|}
 +
{{NavBox HowTo Standardizing Data Analysis and Information Reporting}}
 +
[[Category:NAA]]

Latest revision as of 20:32, 20 February 2020

Creating Subreports

A subreport is a report 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 subreport 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 subreports, there are 5 basic steps to create a report with subreports:

Step 1 – Create the main report
Step 2 – Add a subreport to the main report
Step 3 – Add a parameter to the subreport
Step 4 – Define the report query for the subreport
Step 5 – Add subreport parameter to the main report

Step 1 – Create the Main Report

Before creating the main report, you will need to determine what data should be included in 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 subreport 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 subreports? 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? How are they linked?

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 Create a Basic iReport Template section.

Step 2 – Add Subreport to the Main report

Once the main report has been created you can add a subreport to the main report. The intent of the subreport is to bring data from a different but related datasource into the main report. For example, you could include Activity data on a report about Land. Below are the instructions to create a subreport:

  1. On the main report, from the Palette pane, select Subreport and drag it to the desired band on the iReport template (normally this is the Detail band).
    Subreport Palette
    Subreport Palette
  2. Release the button and the window below displays.
    Add Subreport to the Main report
  3. Click Next to define a new subreport unless you have an existing report that you would like to use for subreport.
  4. Select the desired iReport Template to apply (Blank is recommended).
    Select the desired iReport Template
  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 “Hazard Reduction” should be the data source for the subreport. Click Next.
    Select the desired datasource
  6. Select the attributes to be included in your subreport. Click Next.
    Select the desired fields for the subreport
  7. Select the fields to group by. This step is not required. Click Next.
    Select the fields to group by
  8. Enter a name for the subreport or use the default. Choose Next.
    Enter a name for the subreport
  9. Select Use the same connection used to fill master report. Click Finish.
    Use same connection
  10. The subreport displays.

Step 3 – Add Parameter to Subreport

To connect a subreport to the main report, we next need to create a parameter for passing the identification (guid) of the primary IMSMA item between the subreport and the main report. In the Report Inspector on the subreport, add a Parameter to hold the guid of the IMSMA item on the main report. It is recommended to add a fairly generic parameter name such as ItemGUID so that the subreport can be more easily re-used.

  1. From the Report Inspector, right click on the Parameter tree and choose Add Parameter.
    Add Parameter
  2. In the Properties pane, enter a parameter name (e.g. ItemGUID} and edit any other properties. Normally it is not required to edit anything other than the name, you might however want to deselect the Use as a prompt option.
    Add Parameter
  3. Once the parameter is added it will be available in the Report Inspector and can be referenced as $P{parametername}. For example:
$P{ItemGUID}

Step 4 – Define Report Query for Subreport

The next step is to create a report query for the subreport you just created in Step 2 to define how the IMSMA item on the main report is related to the IMSMA item on the subreport. Typically the subreport report query will receive the guid from the main report via the parameter created in Step 3 and then use it to limit items in the subreport to those linked to the main report. Below is the instructions on how to define the report query:

  1. Open the report query window.
  2. From the report query window, select ImsmaQuery from the query language list.
  3. Enter a report query filter using the parameter defined above to limit the objects according to the main report object. The most common report query filter would be to filter to only linked objects. For example links.toGuid="$P{ItemGUID}". In this step, the Report Query tells the subreport to return only rows that are linked to the guid of the item in the main report via the link table. Other combinations of report query filters can be used on subreports beyond the link table. These are documented in the Possible Subreport Relationships section below.
  4. Click OK. The report query window closes.
  5. Save the subreport.

Step 5 – Link the Subreport to the Main Report

Now that both the main and the subreport are ready, the only thing that remains to be done is linking the two together. Below are the instructions to link the two reports:

  1. Make sure you are at the main report by selecting the name of the main report from the tabs at the top of the iReport Designer canvas pane.
  2. Select the subreport box within the main report.
    Select the Subreport on the main report
    Select the Subreport on the main report
  3. On the Properties pane for the Subreport, select the Parameter selector.
    Subreport properties
    Subreport properties
  4. Add a parameter and type exactly the name of the subreport parameter created in step 3 (e.g. ItemGUID)
    Add a parameter
    Add a parameter
  5. For the value expression, select the appropriate field to include in the parameter. Normally this is $F{guid} but other choices are available.
    Select the appropriate field
    Select the field to include in the parameter
  6. Click OK and close Subreport Parameters window.

You are now ready to run your main report. Running (i.e. previewing) the main report will automatically run the subreport(s) as well, and the data from the main as well as from the subreport(s) will be included in the output.

Kinds of Subreports

There are three main kinds of relationships in IMSMA on which subreports can be generated from IMSMA data. These are:

  • Location relationship
  • Link relationship
  • Geospatial relationship
  • Other relationship

Location relationship subreports

Because each of the main items in IMSMANG, Accident, Activity, Assistance, Education, Land, QM, and Victim, must be reconciled to a Location during the data entry phase, each of these objects has a relationship with Location and can thus be a subreport on a Location main report. Since all accidents, land, activities, educations, QMs, assistances, and victims are associated with a single location each record knows what location it is related to based on the location guid field. When creating a report where the main report is a Location and the subreport is one of the associated objects the Location's guid is passed to the subreport in a parameter and the subreport can use a special ImsmaQuery language syntax to limit the subreport data to those associated with the main report's location:

location.guid = "$P{parametername}"

The location guid field in the subreport item table contains the guid of the location to which the item record is associated. The figure below 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.

location_guid example

location_guid example

To create a subreport using the Location relationship:

  1. From the main "Location" report, pass the location guid to the subreport in a parameter (e.g. in a parameter called ItemGUID).
  2. Create a parameter on the subreport with the same name as in #1.
  3. In the subreport for the related item (e.g. a Victim) use the parameter in the following ImsmaQuery language syntax
location.guid="$P{parametername}"

This has the effect of limiting the subreport records to only those records that are associated to the location from the main report.

Link relationship subreports

In addition to the Location relationship, the main items in IMSMANG can also be linked with one another during the reconciliation process. This is how, for example, Activities are linked with Land in the IMSMANG database. Accidents, land, activities, educations, assistances, QMs, and victims 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 Data Entry Form. This special relationship can also be modeled in iReport using a report and subreport(s).

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 Location relationship. The item in the main report is the primary item for which you would like to find linked items. The subreport would return the records for the particular item that is linked to the primary item. The key difference is the query used in the subreport.

To create a subreport using the link relationship:

  1. From the main report, pass the guid of the main report object to the subreport in a parameter (e.g. pass an accident's guid in a parameter called ItemGUID).
  2. Create a parameter on the subreport with the same name as in #1.
  3. In the subreport for the related item (e.g. a Victim) use the parameter in the following ImsmaQuery language syntax
links.toGuid="$P{parametername}"

This has the effect of limiting the subreport records to only those records that have a link to the guid or object passed from the main report.

Geospatial relationships

In addition to Location and Link relationships, many objects in IMSMA can store geospatial/geographic information (i.e. point/polygon information). This relationship with the geospatial data can be modeled in a report/subreport relationship for any item that has geospatial information associated with it. To include point/polygon information on an object's main report, follow 3 basic steps:

  • Step 1: Create a main report for an object with geospatial info
  • Step 2: Create a GeospatialInfo subreport on the main report, i.e. a report using the GeospatialInfo datasource
  • Step 3: Create a Geopoint subreport on the GeospatialInfo report, i.e. a report using the Geopoint datasource

Step 1: Create a main report for an object with geospatial info

  • Create a main report with an object that has geospatial information (e.g. Land).
  • Ensure that the geospatialInfos field is added to the main report.

Step 2: Create a GeospatialInfo subreport on the main report

  • On the main report, add a subreport using the GeospatialInfo datasource
  • Pass the geospatialInfos field to the subreport in a parameter (e.g. GeospatialInfoList). Note: instead of passing the $F{guid} field to the parameter, the parameter value should be $F{geospatialInfos}
  • In the GeospatialInfo subreport use the parameter in the following ImsmaQuery language syntax
guid IN $P{parametername}

Step 3: Create a Geopoint subreport on the GeospatialInfo report

  • On the Geospatialinfo subreport, add a subreport using the Geopoint datasource
  • Pass the guid field to the subreport in a parameter (e.g. GeospatialGUID). Note: Pass the $F{guid} field NOT the $F{geospatialInfos} value.
  • In the Geopoint subreport use the parameter in the following ImsmaQuery language syntax
geospatialinfoguid IN $P{parametername}

This has the effect of limiting the Geospatial records to only those records whose guid is linked to the geospatialinfo of the main report. This is an example of nested reports, i.e. a main report, a subreport, and a sub-subreport.

Other subreport relationships

Certain items in IMSMANG have subsets of data that are contained within them. For example, an education may have education details such as the education type, total audience, and percentage of women and men. Examples of other subreport relationships include:

Other subreport relationships
Parent item Child item(s)
Activity Activity Device

Impact survey

Interviewee

Question

Education Education details
Victim Received Education

Needs Assessment

Cause

Land Land Device
Accident Accident Device
Assistance Assistance Given
Task Work Item

The process for creating a report on a parent item and its children is the same as any other report/subreport relationship.

Example Subreport Relationships

Main Report Subreport can be Value to Pass to Subreport Report Query
Location Any Item with a Location location guid location.guid = "$P{ItemGUID}"
Any linked item Any linked Item Item guid (e.g. Land guid) links.toGuid="$P{ItemGUID}"
Activity Impact survey or Interviewee Activity guid hazreduc.guid = "$P{ItemGUID}"
Interviewee Question interviewee guid interviewee.guid = “$P{ItemGUID}”
Task Work Item Task guid task.guid = "$P{ItemGUID}"
Any Item with Geospatial information GeospatialInfo geoSpatialInfos guid IN $P{GeospatialInfoList}
Geospatial Info GeoPoint GeospatialInfo guid geospatialInfoGuid = "$P{ItemGUID}"
Any item with devices Item Device (e.g. AccidentDevice) Item guid (e.g. Accident guid) accident.guid = "$P{ItemGUID}" hazard.guid = "$P{ItemGUID}"
MRE MREDetail MRE guid mre.guid = "$P{ItemGUID}"
Victim Needs Assessment victim guid victim.guid="$P{ItemGUID}"
Victim Cause victim guid victim.guid="$P{ItemGUID}"
Assistance Assistance Given victimAssistance.guid victimAssistance.guid="$P{ItemGUID}"
Task Objective SQL Field list of Objectives guid IN ($P{SQLList})
Work Item Linked Items (Activity, Education, QM, Assistance) SQL Field list of Items guid IN ($P{SQLList})