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
# Right-click on the fields tree in Report Inspector and choose '''paste'''
{{note | It is not recommended to the use the "Add Field" option from the Report Inspector due to the complexity of adding the correct field attributes and properties.}}
===Adding a Parameter to an iReport Template===__NOEDITSECTION__
It is sometimes necessary to add a parameter to an iReport template. Usually parameters are used for transferring data from a main report to a subreport. Below are the steps to add a parameter to an an iReport Template.
# From the Report Inspector, '''right click''' on the '''Parameter''' tree and choose '''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 For example, if you do not required want to edit anything other than use the nameparameter as a prompt, then deselect the '''Use as a prompt''' option.
#: [[Image:IReport AddParameter2.png|center|250px|''Add Parameter'']]
#: [[Image:IReport VariableAdd5.png|center|300px|''Edit Variable Expression'']]<div align="center">''Edit Variable Expression''</div>
# Choose OK.
# Drag the variable to the iReport template as desired.
==Report Query Filtering==__NOEDITSECTION__
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 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.
#Open the Report Query window from iReport Designer.
#:[[Image:IReport ReportQueryButton.png|center|500px|''iReport Report Query Button'']]<div align="center">''Open Report Query''</div>
# Choose '''ImsmaQuery''' from the '''Query language''' drop down.
#:[[Image:IReport ReportQuery.png |center|400px|''iReport Report Query Window'']]<div align="center">''Report Query Window''</div>
# Enter a valid [[#ImsmaQuery Language Syntax|ImsmaQuery Language]] filter in the textbox (e.g. localId="MF-HQ-1").
===ImsmaQuery Language Syntax===__NOEDITSECTION__
The ImsmaQuery Language syntax is a language designed to limit or filter datasets in iReportsinteracting with {{IMSMANG}}. It is similar to the WHERE clause syntax in SQL though not identical. ImsmaQuery language generally uses the following format:
''field'' OPERATOR "''value''"
as in
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>
