Difference between pages "Modify an iReport Template" and "Standardising Data Analysis and Information Reporting"

From IMSMA Wiki
(Difference between pages)
Jump to: navigation, search
 
 
Line 1: Line 1:
 
{{TOC right}}
 
{{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.}}
+
Two of most important purposes of an information system are:
 +
* ensure high quality of data and
 +
* support operational use of the information.
 +
One important use of the data is reporting. If it not possible in an easy way to create reports/statistics which reflect the data correctly the credibility of the data is lost. That’s why {{IMSMANG}} was designed with a robust reporting and analysis component that supports various reporting options. These options include:
  
==Overview==__NOEDITSECTION__
+
<center>
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}}.
+
{| class="wikitable" width="1000"
 +
|-
 +
| align="center" colspan="6" | '''Comparison of {{IMSMANG}} Reporting Options'''
 +
|-
 +
| width="20pt" |
 +
| width="195pt" | '''Basic'''
 +
| width="195pt" | '''iReport'''
 +
| width="195pt" | '''Data export'''
 +
| width="195pt" | '''Other external reporting tool'''
 +
|-
 +
| align="left" | '''Description'''
 +
| align="left" | Includes printing Data Entry Forms, Summary items and maps
 +
| align="left" | Includes building iReport templates for more sophisticated reporting as well as exporting data for use in external tools such as Microsoft Excel
 +
| align="left" | Includes exporting data to CSV format for use in Excel as well as cutting and pasting from tables.
 +
| align="left" | The most complicated kind of reporting where users connect to backend databases using SQL, Crystal Reports, ArcGIS Desktop, etc., to perform queries and generate reports
 +
|-
 +
| align="left" | '''Uses'''
 +
| align="left" |
 +
*Navigating data in {{IMSMANG}}
 +
*General summaries of pre-provided data
 +
| align="left" |
 +
*Lists and summary reports of single items
 +
*Simple cross tabs
 +
*Monthly progress reports for internal consumption
 +
*Charts
 +
| align="left" |
 +
*Manipulating data using external tools
 +
*Pivot tables and charts based on one item
 +
| align="left" |
 +
*Linking or exporting {{IMSMANG}} data for use in external tools
 +
*Building customised reporting tools for {{IMSMANG}} data
 +
*Linking data to ArcGIS Desktop, Crystal Reports and other tools
 +
|-
 +
| align="left" | '''Benefits'''
 +
| align="left" |
 +
*Fast and easy to use
 +
*Requires little or no training
 +
| align="left" |
 +
*Can limit data using searches
 +
*Can be preformatted and available on a network
 +
*Backed up and stored
 +
*Users can simply run these reports as opposed to building them
 +
*Supports charts
 +
| align="left" |
 +
*Easiest way to get {{IMSMANG}} data into Excel
 +
*Can manipulate data in common applications
 +
| align="left" |
 +
*Most powerful and customisable way of accessing data for reports
 +
*Can manipulate data in advanced or custom reporting applications
 +
*Can be linked with external databases
 +
|-
 +
| align="left" | '''Challenges'''
 +
| align="left" |
 +
*Reports are simplistic
 +
*Limited to printing and reports provided with the software. No customisation.
 +
| align="left" |
 +
*Not ideal for incorporating multiple reports together within a larger report
 +
| align="left" |
 +
*Limited customisation of resulting data (formats, conditional display, etc.)
 +
*Not ideal for incorporating multiple reports together within a larger report
 +
| align="left" |
 +
*Requires expert level understanding of SQL
 +
|}
 +
</center>
 +
[[Image:BI in context graph.png|center|700px']]
  
==Fields, Variables, and Parameters==__NOEDITSECTION__
 
===Adding a Field to an iReport Template===__NOEDITSECTION__
 
<p>If you need to include a field that was not selected while using the Report Wizard, there are several options for including additional fields.
 
  
====Option 1: Get fields from Datasource====__NOEDITSECTION__
+
==Basic Reporting==__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 delete any unwanted fields from the report. {{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:
+
{{IMSMANG}} provides several reporting options that allow users to produce basic reports directly from {{IMSMANG}}. These include printing Data Entry Forms entered into the system, maps from the main navigation window and Summary items. Any of these reports can be sent directly to a printer or to an intermediate format such as rich text format (.RTF) or portable document format (.PDF) for transmitting in electronic format or for further editing.
# Choose the Report Query button.
 
#:<div align="center">[[Image:IReport_ReportQueryButton.png‎|center|500px|''Report Query Button'']]''iReport Report Query Button''</div>
 
# From the Report Query Window, choose the '''Datasource Provider''' tab.
 
#:[[Image:IReport_ReportQuery.png‎|center|400px|''Report Query Window'']]
 
#:<div align="center">''Report Query Window''</div>
 
# Select the '''Get Fields From Datasource''' button.
 
#:[[Image:IReport ReportQuery Datasource1.png|center|500px|''Datasource Fields'']]<div align="center">''Datasource Fields''</div>
 
# All fields will now be displayed. 
 
# Choose '''Ok''' to save the fields and return to the iReport Designer.
 
  
====Option 2: Copy and Paste====__NOEDITSECTION__
+
{{IMSMANG}} also includes a Map Layout manager that allows users to define map layouts and print high-quality maps with {{IMSMANG}} data. Information managers can define map layout templates that include scale bars, annotations and legends or use more sophisticated tools like ArcGIS Desktop to prepare more complicated map layouts for displaying {{IMSMANG}} data.
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 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 field and select copy
 
#:[[Image:IReport Field Copy.png|center|250px|''Field Copy'']]<div align="center">''Field Copy''</div>
 
# Navigate to the original report with the missing fields
 
# Right-click on the fields tree in Report Inspector and choose '''paste'''
 
  
{{note | It is not recommended to use the "Add Field" option from the Report Inspector due to the complexity of adding the correct field attributes and properties.}}
+
[[Image:AdminGuide_ExampleMapLayoutTemplate.png|center|500px|''Example of a Map Layout Template'']]
 +
<div align="center">
 +
''Example of a Map Layout Template''
 +
</div>
  
===Adding a Parameter to an iReport Template===__NOEDITSECTION__
+
{{note|
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 iReport Template.  
+
* Map layouts can be printed and scaled to any size paper. This is a simple way of producing {{IMSMANG}} maps without needing additional GIS software.
# From the Report Inspector, '''right click''' on the '''Parameter''' tree and choose '''Add Parameter'''.
+
* Map layout templates can be prepared and distributed to individual {{IMSMANG}} clients, allowing information managers to prepare templates based on user preferences or functional needs.
#: [[Image:IReport AddParameter1.png|center|250px|''Add Parameter'']]
+
}}
# In the '''Properties''' pane, enter a parameter name (e.g. ItemGUID} and edit any other properties. For example, if you do not want to use the parameter as a prompt, then deselect the '''Use as a prompt''' option.
 
#: [[Image:IReport AddParameter2.png|center|250px|''Add Parameter'']]
 
  
===Adding a Variable to an iReport Template===__NOEDITSECTION__
+
==Embedded Reporting==__NOEDITSECTION__
Often it is necessary to add a variable to an iReport template. Variables are used to store calculations and totals in an iReport template. They are often used in the Summary Band portion of a report to store sums of numeric fields (e.g. areaSize) or record counts. There are two main ways to add variables to an iReport template.
+
{{IMSMANG}} supplements the basic reporting options available to all users with several more sophisticated reporting tools that are embedded within the system. In addition to reporting on individual elements, these embedded tools allow for statistical and aggregate reporting of data within the database. Instead of reporting the contents of a single report, statistical reporting allows information managers to produce reports about multiple items and to use functions such as sum, average and count to summarise data.  
  
====For numeric variables====__NOEDITSECTION__
+
There are two options for embedded reporting: iReports and data export. Both functions rely on {{IMSMANG}} searches to filter data. Using iReports, information managers can build reporting templates to visualise data in a specific and repeatable format. Using data export, managers can export search results to an external tool such as Microsoft Excel and perform aggregation and statistical analysis.
For variables that are numeric (area sizes, number of devices, etc):
 
# From the '''Report Inspector''', select the field that you wish to sum or count.
 
# Drag the field to the Summary Band of the iReport Template canvas.
 
# A dialogue will pop up, select '''The result of an aggregation function''' and choose the calculation type you want to perform.
 
#: [[Image:IReport VariableAdd1.png|center|300px|''Select aggregation function'']]<div align="center">''Choose the type of calculation''</div>
 
# Choose '''OK'''.
 
# The variable will now appear in the '''Report Inspector''' and can be used in the iReport.
 
#: [[Image:IReport VariableAdd2.png|center|300px|''Select aggregation function'']]<div align="center">''Choose the type of calculation''</div>
 
  
====To add a variable manually====__NOEDITSECTION__
+
===iReports===__NOEDITSECTION__
This may be required when adding more complicated variables such as variables designed to perform calculations on one or more fields or when using fields that are not numeric.
+
{{HowTo's
# From the '''Report Inspector''' '''right click''' on the '''Variable''' tree and choose '''Add Variable'''.
+
|[[Use iReport | Use iReport]]
#: [[Image:IReport VariableAdd3.png|center|300px|''Add Variable'']]<div align="center">''Add Variable''</div>
+
}}
# In the '''Properties''' pane,
+
With the iReport tool, information managers have access to an entire suite of report generation functions. iReport provides the ability to design report templates from the desired data elements; add page layout and formatting information including images, colours and text; and import the reports into {{IMSMANG}} for data entry personnel to run. Information managers can also design complex reports to summarise data using sum, count, average, subreports and other statistical reporting functions similar to packages such as Crystal Reports. Being fully integrated into {{IMSMANG}}, iReport can be translated into any language supported by the system. Using this approach, only information managers are presented with the complexity of designing reports while traditional users simply have to select a report (and any preceding search) and run it.
#: [[Image:IReport VariableAdd4.png|center|300px|''Variable Properties Pane'']]<div align="center">''Variable Properties Pane''</div>
+
## Enter a Variable name (e.g. SumAreaSize}.
+
Typical uses for iReport include producing reports commonly required by multiple users within a programme. These reports can include:
## Select the variable class (i.e. the type of variable to be created. Normally this is the same as the type of the field to be used).  
 
## Choose the '''Calculation''' type, normally '''Sum''' or '''Count'''.
 
## Enter a '''Variable Expression'''. Normally this is simply selecting the field to be summed or counted (e.g. '''$F{areaSize}'''). Click the [[Image:Editor Button.png]] on the Variable Expression line to open the Variable Expression editor.
 
#: [[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__
+
*clearance: monthly progress reporting
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:
+
*education: number of people trained by type of training
* '''temporarily''': to limit the results returned when previewing the report during the design phase in iReport
+
*land: sum of hazardous area by province or type of land
* '''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.
+
*accidents: number of accidents by type or by province
 +
*data quality: number and list of land with incorrect status
  
Both approaches use the same steps.
+
Because iReport templates are initiated with a search, they provide a scalable reporting solution that allows one template to be developed for many reports. For example, a report template that shows a list of land with associated ordnance details can produce different reports depending on the search parameters.
  
===Using Report Query filters===__NOEDITSECTION__
+
[[Image:AdminGuide_ReportUsingNoSearchParameters.png|center|500px|''Report Example Using No Search Parameters'']]
To use Report Query filters,
+
<div align="center">
 +
''Report Example Using No Search Parameters''
 +
</div>
  
#Open the Report Query window from iReport Designer.
+
In the first example, no search parameter is applied and all data in {{IMSMANG}} is used in the report. But by adding a search parameter, for example, limiting this to land whose status is Worked on, a different report is generated.  
#:[[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").
 
# Choose the '''Preview Data''' button to verify that the query is limited as desired.
 
# Choose '''OK''' to save the Report Query settings and return to the report.
 
  
===ImsmaQuery Language Syntax===__NOEDITSECTION__
+
[[Image:AdminGuide_ReportUsingSearchParameters.png|center|500px|''Report Example Using a Search Parameter'']]
The ImsmaQuery Language syntax is a language designed to limit or filter datasets in iReports interacting with {{IMSMANG}}. It is similar to the WHERE clause syntax in SQL though not identical. ImsmaQuery language generally uses the following format:
+
<div align="center">
''field'' OPERATOR "''value''"
+
''Report Example Using a Search Parameter''
as in
+
</div>  
localId = "MF-HQ-1"
 
 
 
#To start using ImsmaQuery Language, first decide which field you want to include in your filter. You may choose any field that is available on the data source to begin your filtering. As in most places in iReport fields are case sensitive. Therefore '''''localId''''' will work but '''''LocalId''''', '''''LocalID''''' and '''''localid''''' will all fail to filter the data.
 
#Next choose your operator. Valid operators include "=", "<", ">", "<>", and "LIKE" among others.
 
#Finally, select the value you want to use. Values in ImsmaQuery are always double quoted (e.g. "MF-HQ-1" or "MF-HQ-%"). In addition to constants in the value section, the LIKE operator can take % characters to represent wild cards. For example:
 
localId LIKE "MF-HQ-1%"
 
 
 
Further, parameters can be used in the ImsmaQuery to represent variables. These take the form:
 
localId = "''$P{parametername}''"
 
 
 
Finally, ImsmaQuery language can support the "IN" operator. The IN operation takes a comma separated, double quoted list, bounded by parentheses as it's value. As in:
 
localId IN ("value1", "value2", "value3")
 
 
 
====ImsmaQuery Examples====__NOEDITSECTION__
 
localId LIKE "MF-HQ-%"
 
endDate > 2010-09-01 AND endDate <2010-09-30
 
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__
 
In addition to providing access to all of the data fields on a particular object, the iReport data sources provide access to several special fields to access hard to reach data from the IMSMA database.
 
  
===The SQL Field===__NOEDITSECTION__
+
Moreover, if the land is further limited to a specific type, another report is generated, using again the same template. This way, information managers can build templates and searches to fulfill multiple reporting needs.
All iReport data sources have access to a special field called the SQL field to access hard to reach data. This field allows report designers to insert and execute a SQL query to access data from within an iReport template. It's usage requires a knowledge of SQL and the IMSMA database structure and is only recommended for IMSMA administrators with a strong information management background. In all data sources, this special field is called '''SQLField'''.
 
  
These fields can be added during the [[Create a Basic iReport Template|iReport Wizard]] process or manually added later.  
+
Reports created with iReport can be printed directly within {{IMSMANG}} or saved to an intermediate format such as .RTF, .PDF or .XLS for electronic transmission or additional editing or inclusion in other reports. iReport supports the inclusion of charts, images and headers and footers, and it acts as a complete reporting package.
  
To use the SQL field to select data from the IMSMA database:
+
===Data Export===__NOEDITSECTION__
# In the '''Report Inspector''', click on the SQLField to view its properties in the '''Properties''' pane.
+
{{HowTo's
# Edit the name of the SQL field by adding a unique value to the end of the SQL field in parentheses. For example, ''SQLField(testSQL)'' or ''SQLField(VictimCount)''.
+
|[[Exporting| Export data]]
# 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>
+
{{IMSMANG}} also provides data export functionality designed to allow users to export data to external tools such as Microsoft Excel for additional reporting and analysis. This lets users take advantage of functionality in these tools such as sums, counts, charts, pivot tables and other data manipulation functions to format and present data. Users access this functionality by performing a search in {{IMSMANG}} and then exporting the results to .CSV format. Users can choose which columns to export, and the data can be manipulated in any external tool that supports .CSV.
# Choose '''Add''' to create a new property.
 
#: [[Image:IReport SQLField2.png|center|300px|''Create the SQL Property'']]<div align="center">''Create the SQL Property''</div>
 
# Set the '''Property Name''' to '''''SQL'''''
 
# 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__
+
Because data export is initiated with a search, it provides a flexible approach for getting data to an external tool. However, once the data is used in a tool outside of {{IMSMANG}}, it is impossible to control how this data is manipulated which may limit the consistency of reports produced in this way.  
The SQL statement can be any valid PostgreSQL statement that '''returns a result'''. Especially when working with charts, null values will lead to errors. Therefore, it is a good practice to use a function like COALESCE() in order to ensure 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()
 
* a special parameter called {guid} that passes the value of the {guid} from the report to the SQL statement. This can be used like a regular string to limit the SQL statement according to the object in the report.  
 
  
Examples of SQLField statements include:
+
{{note|{{IMSMANG}} searches return information on only one item at a time, and data export is limited to the data returned by the search. Data from linked items (such as victim data linked to accidents) is not available for export.}}
SELECT hazard_localid FROM hazard WHERE hazard_guid = '{guid}'
 
  
SELECT COALESCE(sum(qty),0) as QTY
+
==External Reporting Tools==__NOEDITSECTION__
FROM hazreducdeviceinfo
+
{{IMSMANG}} also allows information managers to connect advanced reporting tools such as Crystal Reports or ArcGIS Desktop directly to its relational database for highly advanced report generation. With this capability, information managers can use custom reporting solutions to connect with {{IMSMANG}}, allowing for complete control of {{IMSMANG}} outputs. Or, managers can modify existing reporting solutions to connect to {{IMSMANG}}, which leverages technology already implemented in the programme and limits the need for retraining.
WHERE hazreduc_guid = '{guid}' AND
 
ordnance_guid IN (SELECT ordnance_guid FROM ordnance WHERE model LIKE '%AP%')
 
  
===links.toGuid===__NOEDITSECTION__
+
{{note|When using external reporting tools we recommend to connect to the '''[[IMSMA Staging Area | Staging area]]''' instead of the {{IMSMANG}} database. It does require knowledge about the IMSMA data model. Contact contact your [[Information Management Team | GICHD IM advisor]] if you have questions.}}
Another example of a special field available in iReport templates is the '''links.toGuid''' value. links.toGuid is available on any of the major objects in {{IMSMANG}} including Land, Activity, Education, Accident, Victim, Assistance, Location, and Quality Management. This field can be used in the Report Query portion of a report to filter the results based on linkages to other objects. For example, on a Land report with an Activity subreport, the subreport could contain the following Report Query filter to return only Activities with linkages to the Land on the main report:
 
links.toGuid = "$P{HazardGUID}"
 
  
As with other areas in iReport, the ''links.toGuid'' field is case sensitive and must be referenced exactly.
+
===Connecting other External Reporting Tools===__NOEDITSECTION__
 +
The process for connecting external reporting tools is essentially the same for all tools. External tools connect to the {{IMSMANG}} relational database via an ODBC connection. Information managers establish this connection by installing the ODBC driver for PGSQL. When this is complete, information managers can connect any ODBC-compliant tool to the {{IMSMANG}} database, including reporting tools, other database packages and SQL management tools. Examples include:
  
Further information about this can be found on the [[Create iReport Templates with Subreports|Subreport instruction pages]].
+
{| class="wikitable"
 
+
! scope="col" | Reporting tools || scope="col" | Database packages || scope="col" | SQL management tools
==Sorting and Grouping==__NOEDITSECTION__
+
|-
 
+
| Crystal Reports || Microsoft Access || PGSQL Query Browser
===Grouping===__NOEDITSECTION__
+
|-
Grouping allows you to organise the report results using the values of a particular field. Grouping results makes it easier to identify the records which share a common value. Rather than having to visually scan the report results to determine which records have a common value, grouping automatically organises the results for you.
+
| ArcGIS Desktop || OpenOffice Base || Navicat
 
+
|-
To illustrate, suppose you wanted to create a report to list all land within each location ID. An ungrouped report would simply list all land along with their location ID. Although the location ID is included in the report, it would be difficult, tedious, and time-consuming to scan the entire report to identify those land that have a common location ID.
+
| iReport || Microsoft SQL Server || TOAD SQL
 
+
|-
[[Image:iRep07.png|center|500px|''Land report with no grouping'']]
+
| Microsoft Excel || Oracle || Heidi SQL
<div align="center">
+
|}
''Land report with no grouping''
 
</div>
 
 
 
Grouping report results automatically organises the records using the values on the specified field. Using the example above, the land in the report shown in the figure below are organised within the location to which they are associated. Records that do not contain a value for the selected grouping are put into their own group.
 
 
 
[[Image:iRep08.png|center|500px|'' Land grouped by location'']]
 
<div align="center">
 
''Land grouped by location''
 
</div>
 
  
===Adding a Group to an iReport Template===__NOEDITSECTION__
+
When connecting an external reporting tool to {{IMSMANG}}, it is recommended that information managers build database views within the database to do any necessary data transformations. While it is possible to directly import or connect {{IMSMANG}} database tables in external tools and perform transformations within the external tools, the recommended method is to do transformations within the {{IMSMANG}} database using database views and then import the resulting views into the external tools.
  
{{note|The field on which you would like to group your report results must be included in the report.}}
+
{{note|Building database views to transform data before importing it into other tools significantly reduces the performance hit associated with connecting external tools.}}
  
To add a group to an existing report:
+
===Building Database Views===__NOEDITSECTION__
 +
Building database views is the primary way of transforming data to support external reporting. Information managers can use SQL editing tools to build and save database queries as views that can be used to format data for easier reporting. Using database views, information managers can join data together into flat tables that allow for easier incorporation into reports or other analysis tools. Database views act like database tables and can be imported or queried from external tools. Moreover, views automatically refresh as data is added to {{IMSMANG}} and provide an up-to-date data source for other applications.
  
#From the '''Report Inspector''', right-click on the top node of the Report.
+
Using database views, information managers can perform statistical and other operations on the {{IMSMANG}} set for reporting purposes including sum, count and average or more complicated functions available in SQL. These operations can be combined with search criteria to provide a highly precise mechanism for providing data to reports. For example, information managers can create a view that returns the name of each clearance and a sum of the hours worked, AP mines found and area cleared on all progress reports linked to each clearance.  
#Select '''Add Report Group''' from the menu
 
#: [[Image:IReport Group1.png|center|200px|''Add a Report Group'']]<div align="center">''Add a Report Group''</div>
 
#Select a name for your Report Group and choose a field to group by (e.g. maAreaType to group by land classification). The name should be a meaningful name that allows you to easily identify the contents of the group such as the field on which the results are grouped
 
#: [[Image:IReport Group2.png|center|500px|''Create a Report Group'']]<div align="center">''Create a Report Group''</div>
 
# Choose '''Next''' and '''Finish'''.
 
  
This will create a new group in the iReport template as well as two additional bands (a header and a footer for the group).
+
<center>
 +
{| class="wikitable" width="600"
 +
|-
 +
| align="left" | '''Clearance ID'''
 +
| align="left" | '''Area cleared'''
 +
| align="left" | '''AP mines found'''
 +
| align="left" | '''Hours worked'''
 +
| align="left" | '''Number of progress reports'''
 +
|-
 +
| align="left" | CL-1022
 +
| align="left" | 23,400
 +
| align="left" | 45
 +
| align="left" | 120
 +
| align="left" | 4
 +
|-
 +
| align="left" | CL-1239
 +
| align="left" | 22,330
 +
| align="left" | 42
 +
| align="left" | 160
 +
| align="left" | 6
 +
|-
 +
| align="left" | CL-2345
 +
| align="left" | 1,920
 +
| align="left" | 4
 +
| align="left" | 20
 +
| align="left" | 1
 +
|}
 +
</center>
  
===Sorting Report Results===__NOEDITSECTION__
+
This view could then be pasted into Excel, linked with additional {{IMSMANG}} data or external data or linked with a map to provide additional analysis. As such, database views provide a powerful way of formatting and analysing {{IMSMANG}} data.
It may be helpful to sort the report results using values in one or more of the fields. Sorting the results makes it easier to review them and locate any records that you may be searching for.  
 
To sort report results:
 
<ol start="1">
 
<li>Create the report.</li>
 
<li>Select the '''Report Query''' button.</li>
 
:The Report query window displays.
 
<li>Click the '''Sort options''' button that is located at the bottom of the Report query window.</li>
 
:The Sorting window displays.
 
<li>Click the '''Add Field''' button.</li>
 
<li>From the '''Sort by''' list, select the field by which you would like to sort the report results.</li>
 
</ol>
 
  
{{note|When sorting data in reports that contain groups, ensure the “Group by” fields appear first in the sort order.}}
+
{{note|
 +
* Database views created in {{IMSMANG}} are backed up by the {{IMSMANG}} backup functionality. However, it is important to correctly set the database permissions on views or it will cause problems when restoring the databases.
 +
* Information managers should maintain separate files containing the view creation scripts so that they can be quickly and easily restored or modified. Using the CREATE OR REPLACE VIEW syntax from SQL is an easy way to build scripts for creating or replacing views if they already exist.
 +
}}
  
<ol start="6">
+
{{NavBox IMSMA NG Administration}}
<li>From the '''Sort type''' list, select either the ''Ascending'' or ''Descending'' sort order.</li>
 
<li>Click the '''OK''' button.</li>
 
::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 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>
 
:The Report query window closes.
 
</ol>
 
  
{{NavBox HowTo Standardizing Data Analysis and Information Reporting}}
 
 
[[Category:NAA]]
 
[[Category:NAA]]

Latest revision as of 12:44, 4 May 2021

Two of most important purposes of an information system are:

  • ensure high quality of data and
  • support operational use of the information.

One important use of the data is reporting. If it not possible in an easy way to create reports/statistics which reflect the data correctly the credibility of the data is lost. That’s why IMSMANG was designed with a robust reporting and analysis component that supports various reporting options. These options include:

Comparison of IMSMANG Reporting Options
Basic iReport Data export Other external reporting tool
Description Includes printing Data Entry Forms, Summary items and maps Includes building iReport templates for more sophisticated reporting as well as exporting data for use in external tools such as Microsoft Excel Includes exporting data to CSV format for use in Excel as well as cutting and pasting from tables. The most complicated kind of reporting where users connect to backend databases using SQL, Crystal Reports, ArcGIS Desktop, etc., to perform queries and generate reports
Uses
  • Navigating data in IMSMANG
  • General summaries of pre-provided data
  • Lists and summary reports of single items
  • Simple cross tabs
  • Monthly progress reports for internal consumption
  • Charts
  • Manipulating data using external tools
  • Pivot tables and charts based on one item
  • Linking or exporting IMSMANG data for use in external tools
  • Building customised reporting tools for IMSMANG data
  • Linking data to ArcGIS Desktop, Crystal Reports and other tools
Benefits
  • Fast and easy to use
  • Requires little or no training
  • Can limit data using searches
  • Can be preformatted and available on a network
  • Backed up and stored
  • Users can simply run these reports as opposed to building them
  • Supports charts
  • Easiest way to get IMSMANG data into Excel
  • Can manipulate data in common applications
  • Most powerful and customisable way of accessing data for reports
  • Can manipulate data in advanced or custom reporting applications
  • Can be linked with external databases
Challenges
  • Reports are simplistic
  • Limited to printing and reports provided with the software. No customisation.
  • Not ideal for incorporating multiple reports together within a larger report
  • Limited customisation of resulting data (formats, conditional display, etc.)
  • Not ideal for incorporating multiple reports together within a larger report
  • Requires expert level understanding of SQL
700px'


Basic Reporting

IMSMANG provides several reporting options that allow users to produce basic reports directly from IMSMANG. These include printing Data Entry Forms entered into the system, maps from the main navigation window and Summary items. Any of these reports can be sent directly to a printer or to an intermediate format such as rich text format (.RTF) or portable document format (.PDF) for transmitting in electronic format or for further editing.

IMSMANG also includes a Map Layout manager that allows users to define map layouts and print high-quality maps with IMSMANG data. Information managers can define map layout templates that include scale bars, annotations and legends or use more sophisticated tools like ArcGIS Desktop to prepare more complicated map layouts for displaying IMSMANG data.

Example of a Map Layout Template

Example of a Map Layout Template

Note.jpg
  • Map layouts can be printed and scaled to any size paper. This is a simple way of producing IMSMANG maps without needing additional GIS software.
  • Map layout templates can be prepared and distributed to individual IMSMANG clients, allowing information managers to prepare templates based on user preferences or functional needs.

Embedded Reporting

IMSMANG supplements the basic reporting options available to all users with several more sophisticated reporting tools that are embedded within the system. In addition to reporting on individual elements, these embedded tools allow for statistical and aggregate reporting of data within the database. Instead of reporting the contents of a single report, statistical reporting allows information managers to produce reports about multiple items and to use functions such as sum, average and count to summarise data.

There are two options for embedded reporting: iReports and data export. Both functions rely on IMSMANG searches to filter data. Using iReports, information managers can build reporting templates to visualise data in a specific and repeatable format. Using data export, managers can export search results to an external tool such as Microsoft Excel and perform aggregation and statistical analysis.

iReports

With the iReport tool, information managers have access to an entire suite of report generation functions. iReport provides the ability to design report templates from the desired data elements; add page layout and formatting information including images, colours and text; and import the reports into IMSMANG for data entry personnel to run. Information managers can also design complex reports to summarise data using sum, count, average, subreports and other statistical reporting functions similar to packages such as Crystal Reports. Being fully integrated into IMSMANG, iReport can be translated into any language supported by the system. Using this approach, only information managers are presented with the complexity of designing reports while traditional users simply have to select a report (and any preceding search) and run it.

Typical uses for iReport include producing reports commonly required by multiple users within a programme. These reports can include:

  • clearance: monthly progress reporting
  • education: number of people trained by type of training
  • land: sum of hazardous area by province or type of land
  • accidents: number of accidents by type or by province
  • data quality: number and list of land with incorrect status

Because iReport templates are initiated with a search, they provide a scalable reporting solution that allows one template to be developed for many reports. For example, a report template that shows a list of land with associated ordnance details can produce different reports depending on the search parameters.

Report Example Using No Search Parameters

Report Example Using No Search Parameters

In the first example, no search parameter is applied and all data in IMSMANG is used in the report. But by adding a search parameter, for example, limiting this to land whose status is Worked on, a different report is generated.

Report Example Using a Search Parameter

Report Example Using a Search Parameter

Moreover, if the land is further limited to a specific type, another report is generated, using again the same template. This way, information managers can build templates and searches to fulfill multiple reporting needs.

Reports created with iReport can be printed directly within IMSMANG or saved to an intermediate format such as .RTF, .PDF or .XLS for electronic transmission or additional editing or inclusion in other reports. iReport supports the inclusion of charts, images and headers and footers, and it acts as a complete reporting package.

Data Export

IMSMANG also provides data export functionality designed to allow users to export data to external tools such as Microsoft Excel for additional reporting and analysis. This lets users take advantage of functionality in these tools such as sums, counts, charts, pivot tables and other data manipulation functions to format and present data. Users access this functionality by performing a search in IMSMANG and then exporting the results to .CSV format. Users can choose which columns to export, and the data can be manipulated in any external tool that supports .CSV.

Because data export is initiated with a search, it provides a flexible approach for getting data to an external tool. However, once the data is used in a tool outside of IMSMANG, it is impossible to control how this data is manipulated which may limit the consistency of reports produced in this way.

Note.jpg IMSMANG searches return information on only one item at a time, and data export is limited to the data returned by the search. Data from linked items (such as victim data linked to accidents) is not available for export.

External Reporting Tools

IMSMANG also allows information managers to connect advanced reporting tools such as Crystal Reports or ArcGIS Desktop directly to its relational database for highly advanced report generation. With this capability, information managers can use custom reporting solutions to connect with IMSMANG, allowing for complete control of IMSMANG outputs. Or, managers can modify existing reporting solutions to connect to IMSMANG, which leverages technology already implemented in the programme and limits the need for retraining.

Note.jpg When using external reporting tools we recommend to connect to the Staging area instead of the IMSMANG database. It does require knowledge about the IMSMA data model. Contact contact your GICHD IM advisor if you have questions.

Connecting other External Reporting Tools

The process for connecting external reporting tools is essentially the same for all tools. External tools connect to the IMSMANG relational database via an ODBC connection. Information managers establish this connection by installing the ODBC driver for PGSQL. When this is complete, information managers can connect any ODBC-compliant tool to the IMSMANG database, including reporting tools, other database packages and SQL management tools. Examples include:

Reporting tools Database packages SQL management tools
Crystal Reports Microsoft Access PGSQL Query Browser
ArcGIS Desktop OpenOffice Base Navicat
iReport Microsoft SQL Server TOAD SQL
Microsoft Excel Oracle Heidi SQL

When connecting an external reporting tool to IMSMANG, it is recommended that information managers build database views within the database to do any necessary data transformations. While it is possible to directly import or connect IMSMANG database tables in external tools and perform transformations within the external tools, the recommended method is to do transformations within the IMSMANG database using database views and then import the resulting views into the external tools.

Note.jpg Building database views to transform data before importing it into other tools significantly reduces the performance hit associated with connecting external tools.

Building Database Views

Building database views is the primary way of transforming data to support external reporting. Information managers can use SQL editing tools to build and save database queries as views that can be used to format data for easier reporting. Using database views, information managers can join data together into flat tables that allow for easier incorporation into reports or other analysis tools. Database views act like database tables and can be imported or queried from external tools. Moreover, views automatically refresh as data is added to IMSMANG and provide an up-to-date data source for other applications.

Using database views, information managers can perform statistical and other operations on the IMSMANG set for reporting purposes including sum, count and average or more complicated functions available in SQL. These operations can be combined with search criteria to provide a highly precise mechanism for providing data to reports. For example, information managers can create a view that returns the name of each clearance and a sum of the hours worked, AP mines found and area cleared on all progress reports linked to each clearance.

Clearance ID Area cleared AP mines found Hours worked Number of progress reports
CL-1022 23,400 45 120 4
CL-1239 22,330 42 160 6
CL-2345 1,920 4 20 1

This view could then be pasted into Excel, linked with additional IMSMANG data or external data or linked with a map to provide additional analysis. As such, database views provide a powerful way of formatting and analysing IMSMANG data.

Note.jpg
  • Database views created in IMSMANG are backed up by the IMSMANG backup functionality. However, it is important to correctly set the database permissions on views or it will cause problems when restoring the databases.
  • Information managers should maintain separate files containing the view creation scripts so that they can be quickly and easily restored or modified. Using the CREATE OR REPLACE VIEW syntax from SQL is an easy way to build scripts for creating or replacing views if they already exist.