NoPublic, bureaucrat, administrator
6,632
edits
Changes
no edit summary
{{TOC right}}
{{Warning | Do not edit / overwrite the iReports templates that are included in the installation package. Use the '''''Save as''''' function and give the iReport template a '''new''' name.}}
==Overview==__NOEDITSECTION__
Once an iReport template has been created, there are many options for modifying the report template including adding new fields, creating variables, filtering the query results and many other options. This section covers the main customisations performed on iReport templates for use in {{IMSMANG}}.
====Option 1: Get fields from Datasource====__NOEDITSECTION__
Using the Report Query dialogue, you can select the option to Get Fields from Datasource Provider, which will automatically add all fields from the selected datasource to the report template. You can then remove delete any unwanted fields from the report. {{warning Note| Make sure that the datasource selected in iReports corresponds to the data source for the report.}} To add fields from the Report Query dialogue:
# Choose the Report Query button.
#:<div align="center">[[Image:IReport_ReportQueryButton.png|center|500px|''Report Query Button'']]''iReport Report Query Button''</div>
====Option 2: Copy and Paste====__NOEDITSECTION__
Alternatively, you can create a new report template using the Wizard and add the desired fields to the new report. Fields can then be copied and pasted from the new report to the original report.
# Create a new report using the [[Create a Basic i-Report iReport Template|iReport Wizard]] and the same data source
# Select the desired fields and finish the report
# Using the Report Inspector, navigate to the desired fields
iReport Designer provides an option for limiting or filtering the results of the query that returns data to the report. While in most cases this Report Query filter is ignored by {{IMSMANG}} once the report has been imported in favor of the search criteria, there are some cases where it is important to edit the Report Query filters in iReport. The two main reasons are:
* '''temporarily''': to limit the results returned when previewing the report during the design phase in iReport
* '''permanently''': for use in [[Create i-Report iReport Templates with Subreports|Subreports]] to restrict the data returned to those that have a relationship with the main report. For example to only return Activities that are associated with the Land in the main report.
Both approaches use the same steps.
links.toGuid = "$P{ItemGUID}" and type = "ProgressReport"
areaSize < 1000.0
status ="Active" OR status = "Transitional"
{{note | Numeric fields should always have the ''value'' portion of the filter set to a decimal.
Dates should use the format yyyy-MM-dd.
Strings from lookup tables (i.e. ImsmaEnum) should always refer to the key value rather than the translated value. In one of the above examples, the query filters the status on "Active". However, the status value for the returned rows will be the translated value "Open" instead of "Active".}}
==Special Fields ==__NOEDITSECTION__
===The SQL Field===__NOEDITSECTION__
These fields can be added during the [[Create a Basic i-Report iReport Template|iReport Wizard]] process or manually added later.
To use the SQL field to select data from the IMSMA database:
# In the '''Report Inspector''', choose click on the desired SQLField (e.g. HazardSQLField) to view it's its properties in the '''Properties''' pane.# Edit the name of the SQL field by adding a unique value to the end of the SQL field in parentheses. For example, ''HazardSQLFieldSQLField(testSQL)'' or ''AccidentSQLFieldSQLField(VictimCount)''.
# Choose the '''Properties''' setter to open the '''Properties List'''.
#: [[Image:IReport SQLField1.png|center|300px|''SQL Field Properties'']]<div align="center">''SQL Field Properties''</div>
# Set the '''Property Value''' to the desired SQL statement.
#: [[Image:IReport SQLField3.png|center|300px|''Set the SQL Values'']]<div align="center">''Set the SQL Values''</div>
# Return to the report and use the new SQL Field just like any other '''string ''' field.{{note|Even if the query returns a numeric value, it is converted into a string; the result of the SQLField is always a string. Therefore, if the return values have to be used as input to some calculation, they need to be explicitly cast to numeric values.}}
====SQL Field Examples====__NOEDITSECTION__
The SQL statement can be any valid SQL PostgreSQL statement that '''returns a result'''. This field can be used Especially when working with charts, null values will lead to retrieve data that errors. Therefore, it is a good practice to use a function like COALESCE() in difficult order to access areas of the databaseensure that null values are replaced by a custom value. You can use any valid PostgreSQL statement in the SQL field including
* VIEWS, JOINS, HAVING, sub-selects, and sub-queries
* PostrgreSQL functions such as COALESCE()
As with other areas in iReport, the ''links.toGuid'' field is case sensitive and must be referenced exactly.
Further information about this can be found on the [[Create iReport Templates with Subreports|Subreport instruction pages]].
==Sorting and Grouping==__NOEDITSECTION__
===Sorting Report Results===__NOEDITSECTION__
It may be helpful to sort the report results using the value values in one or more of the fields. Sorting the results makes it easier to review the results them and locate any records that you may be searching for.
To sort report results:
<ol start="1">
::The Sorting window displays. The Sort by list displays all the fields that you have selected for the report.
<li>Repeat steps 4-7 for each field on which you would like to sort the results.</li>
<li>After you have completed adding added all the fields on which you would like to sort the results, click the '''Close''' button.</li>
:The Sorting window closes.
<li>From the Report query window, click the '''OK''' button.</li>
