Changes

{{Warning | Do 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 edit possible in an easy way to create reports/ overwrite the iReports templates that are included in statistics which reflect the installation package. Use data correctly the '''''Save as''''' function and give credibility of the iReport template a '''new''' namedata is lost.That’s why {{IMSMANG}}was designed with a robust reporting and analysis component that supports various reporting options. These options include:

<center>{| class="wikitable" width=Overview"1000"|-| align="center" colspan=__NOEDITSECTION__"6" | '''Comparison of {{IMSMANG}} Reporting Options'''Once an |-| width="20pt" | | width="195pt" | '''Basic''' | width="195pt" | '''iReport template has been created'''| width="195pt" | '''Data export'''| width="195pt" | '''Other external reporting tool'''|-| align="left" | '''Description''' | align="left" | Includes printing Data Entry Forms, there are many options 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 modifying the report template including adding new fieldsuse 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, creating variablesetc., filtering the query results 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 many other options. This section covers the main customisations performed charts based on iReport templates 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']]

====Option 1: Get fields from Datasource==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 {{IMSMANG}} provides several reporting options that allow users to the report template. You can then delete any unwanted fields produce basic reports directly from the report. {{Note| Make sure that the datasource selected in iReports corresponds to the data source for the report.IMSMANG}} 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># From These include printing Data Entry Forms entered into the Report Query Windowsystem, choose maps from the '''Datasource Provider''' tabmain navigation window and Summary items.#:[[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 Any of these reports can be displayed. # Choose '''Ok''' sent directly to save the fields and return a printer or to the iReport Designeran intermediate format such as rich text format (.RTF) or portable document format (.PDF) for transmitting in electronic format or for further editing.

====Option 2: Copy and Paste====__NOEDITSECTION__Alternatively, you can create {{IMSMANG}} also includes a new report template using the Wizard Map Layout manager that allows users to define map layouts and add the desired fields to the new reportprint high-quality maps with {{IMSMANG}} data. Fields Information managers can then be copied define map layout templates that include scale bars, annotations and pasted from the new report legends or use more sophisticated tools like ArcGIS Desktop to the original report.# Create a new report using the [[Create a Basic iReport Template|iReport Wizard]] and the same prepare more complicated map layouts for displaying {{IMSMANG}} 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 [[Image:AdminGuide_ExampleMapLayoutTemplate.png| It is not recommended to use the center|500px|''Example of a Map Layout Template'']]<div align="Add Fieldcenter" option from the Report Inspector due to the complexity >''Example of adding the correct field attributes and properties.}}a Map Layout Template''</div>

===Adding a Parameter to an iReport Template===__NOEDITSECTION__{{note|It is sometimes necessary * Map layouts can be printed and scaled to add a parameter to an iReport templateany size paper. Usually parameters are used for transferring data from This is a main report to a subreport. Below are the steps to add a parameter to an iReport Templatesimple way of producing {{IMSMANG}} maps without needing additional GIS software. # From the Report Inspector, '''right click''' on the '''Parameter''' tree * Map layout templates can be prepared and choose '''Add Parameter'''.#: [[Image:IReport AddParameter1.png|center|250px|''Add Parameter'']]# In the '''Properties''' pane, enter a parameter name (e.g. ItemGUIDdistributed to individual {{IMSMANG}} and edit any other properties. For exampleclients, if you do not want allowing information managers to use the parameter as a prompt, then deselect the '''Use as a prompt''' optionprepare templates based on user preferences or functional needs.#: [[Image:IReport AddParameter2.png|center|250px|''Add Parameter'']]}}

===Adding a Variable to an iReport Template=Embedded Reporting==__NOEDITSECTION__Often it is necessary {{IMSMANG}} supplements the basic reporting options available to add a variable to an iReport templateall users with several more sophisticated reporting tools that are embedded within the system. Variables are used In addition to store calculations reporting on individual elements, these embedded tools allow for statistical and totals in an iReport templateaggregate reporting of data within the database. They are often used in Instead of reporting the Summary Band portion contents of a single report , statistical reporting allows information managers to store sums of numeric fields (e.g. areaSize) or record counts. There are two main ways produce reports about multiple items and to add variables use functions such as sum, average and count to an iReport templatesummarise data.

====For numeric variables====__NOEDITSECTION__For variables that There are numeric (area sizes, number of devices, etc)two options for embedded reporting:# From the '''Report Inspector''', select the field that you wish iReports and data export. Both functions rely on {{IMSMANG}} searches to sum or countfilter data.# Drag the field Using iReports, information managers can build reporting templates to the Summary Band of the iReport Template canvasvisualise data in a specific and repeatable format.# A dialogue will pop upUsing data export, select '''The result of managers can export search results to an aggregation function''' external tool such as Microsoft Excel 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 VariableAdd2statistical analysis.png|center|300px|''Select aggregation function'']]<div align="center">''Choose the type of calculation''</div>

====To add a variable manually=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.# From the '''Report Inspector''' ''{{HowTo'right click''' on the '''Variable''' tree and choose '''Add Variable'''.s#: [[Image:IReport VariableAdd3.png|center|300px|''Add Variable'']]<div align="center">''Add Variable''</div># In the '''Properties''' pane, #: [[Image:IReport VariableAdd4.pngUse iReport |center|300px|''Variable Properties Pane''Use iReport]]<div align="center">''Variable Properties Pane''</div>## Enter a Variable name (e.g. SumAreaSize}. }## Select With the variable class (i.eiReport tool, information managers have access to an entire suite of report generation functions. iReport provides the type of variable ability to be created. Normally this is design report templates from the same as desired data elements; add page layout and formatting information including images, colours and text; and import the type of the field reports into {{IMSMANG}} for data entry personnel to be used)run. ## Choose the '''Calculation''' typeInformation managers can also design complex reports to summarise data using sum, count, average, normally '''Sum''' or '''Count'''.## Enter a '''Variable Expression'''. Normally this is simply selecting the field subreports and other statistical reporting functions similar to be summed or counted (epackages such as Crystal Reports.g. '''$FBeing fully integrated into {{areaSizeIMSMANG}'''). Click }, iReport can be translated into any language supported by the [[Image:Editor Buttonsystem.png]] on Using this approach, only information managers are presented with the Variable Expression line complexity of designing reports while traditional users simply have to open the Variable Expression editor.#: [[Image:IReport VariableAdd5select a report (and any preceding search) and run it.png|center|300px|''Edit Variable Expression'']]<div align="center">''Edit Variable Expression''</div># Choose OK. # Drag the variable to the Typical uses for iReport template as desiredinclude producing reports commonly required by multiple users within a programme.These reports can include:

==Report Query Filtering==__NOEDITSECTION__*clearance: monthly progress reportingiReport Designer provides an option for limiting or filtering the results *education: number of the query that returns data to the report. While in most cases this Report Query filter is ignored people trained by {{IMSMANG}} once the report has been imported in favor type of the search criteria, there are some cases where it is important to edit the Report Query filters in iReport. The two main reasons aretraining*land:sum of hazardous area by province or type of land* '''temporarily'''accidents: to limit the results returned when previewing the report during the design phase in iReportnumber of accidents by type or by province* '''permanently'''data quality: 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 number and list of land with the Land in the main report.incorrect status

Both approaches use 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 same stepssearch parameters.

===[[Image:AdminGuide_ReportUsingNoSearchParameters.png|center|500px|''Report Example Using Report Query filtersNo Search Parameters'']]<div align===__NOEDITSECTION__"center">To use ''Report Query filters,Example Using No Search Parameters''</div>

#Open In 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 first example, no search parameter is applied and all data in {{IMSMANG}} is used in the '''Query language''' drop down.#:[[Image:IReport ReportQueryreport.png |center|400px|''iReport Report Query Window'']]<div align="center">''Report Query Window''</div># Enter But by adding a valid [[#ImsmaQuery Language Syntax|ImsmaQuery Language]] filter in the textbox (e.g. localId="MF-HQ-1").# Choose the '''Preview Data''' button search parameter, for example, limiting this to verify that the query land whose status is limited as desired.# Choose '''OK''' to save the Report Query settings and return to the Worked on, a different reportis generated.

===ImsmaQuery Language Syntax===__NOEDITSECTION__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[[Image: ''field'' OPERATOR "''value''"as in localId = "MF-HQ-1" #To start using ImsmaQuery Language, first decide which field you want to include in your filterAdminGuide_ReportUsingSearchParameters. 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 ''png|center|500px|''Report Example Using a Search Parameter'LocalId''''', '''''LocalID''''' and '''''localid''''' will all fail to filter the data.]]#Next choose your operator. Valid operators include "<div align=", "<", ">", center"<>", 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}'Report Example Using a Search Parameter'" 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 </div> 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__All iReport data sources have access Moreover, if the land is further limited to a special field called specific type, another report is generated, using again the SQL field to access hard to reach datasame template. This field allows report designers to insert way, information managers can build templates and execute a SQL query searches 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'''fulfill multiple reporting needs.

These fields Reports created with iReport can be added during 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 [[Create inclusion of charts, images and headers and footers, and it acts as a Basic iReport Template|iReport Wizard]] process or manually added latercomplete 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''{{HowTo' pane.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)''.# Choose the '''Properties''' setter to open the '''Properties List'''.#: |[[Image:IReport SQLField1.pngExporting|center|300px|''SQL Field Properties''Export data]]<div align="center">''SQL Field Properties''</div># Choose '''Add''' }}{{IMSMANG}} also provides data export functionality designed 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''' allow users to '''''SQL'''''# Set the '''Property Value''' export data to the desired SQL statementexternal tools such as Microsoft Excel for additional reporting and analysis. #: [[Image:IReport SQLField3.png|center|300px|''Set the SQL Values'']]<div align="center">''Set the SQL Values''</div># Return This lets users take advantage of functionality in these tools such as sums, counts, charts, pivot tables and other data manipulation functions to the report format and use the new SQL Field just like any other '''string''' fieldpresent data. Users access this functionality by performing a search in {{note|Even if IMSMANG}} and then exporting the query returns a numeric value, it is converted into a string; the result of the SQLField is always a stringresults to .CSV format. ThereforeUsers can choose which columns to export, if and the return values have to data can be used as input to some calculation, they need to be explicitly cast to numeric valuesmanipulated in any external tool that supports .CSV.}}

====SQL Field Examples====__NOEDITSECTION__The SQL statement can be any valid PostgreSQL statement that '''returns Because data export is initiated with a result'''. Especially when working with chartssearch, null values will lead it provides a flexible approach for getting data to errorsan external tool. ThereforeHowever, it once the data is a good practice to use a function like COALESCE() used 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 tool outside of {{guidIMSMANG} that passes the value of the {guid} from the report to the SQL statement. This can be used like a regular string , it is impossible to control how this data is manipulated which may limit the SQL statement according to the object consistency of reports produced in the reportthis way.

Examples of SQLField statements include: SELECT hazard_localid FROM hazard WHERE hazard_guid = '{guid{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 COALESCE(sum(qty)==External Reporting Tools==__NOEDITSECTION__{{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}},0) as QTY FROM hazreducdeviceinfo WHERE hazreduc_guid = 'allowing for complete control of {{IMSMANG}} outputs. Or, managers can modify existing reporting solutions to connect to {{guidIMSMANG}' AND ordnance_guid IN (SELECT ordnance_guid FROM ordnance WHERE model LIKE '%AP%')}, which leverages technology already implemented in the programme and limits the need for retraining.

===links.toGuid===__NOEDITSECTION__Another example of a special field available in iReport templates is {{note|When using external reporting tools we recommend to connect to the '''links.toGuid[[IMSMA Staging Area | Staging area]]''' value. links.toGuid is available on any instead of the major objects in {{IMSMANG}} including Land, Activity, Education, Accident, Victim, Assistance, Location, and Quality Managementdatabase. This field can be used in It does require knowledge about the Report Query portion of a report to filter the results based on linkages to other objectsIMSMA data model. 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: linksContact contact your [[Information Management Team | GICHD IM advisor]] if you have questions.toGuid = "$P{HazardGUID}"}

As with ===Connecting other areas in iReport, 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 ''linksODBC driver for PGSQL.toGuid'' field When this is case sensitive complete, information managers can connect any ODBC-compliant tool to the {{IMSMANG}} database, including reporting tools, other database packages and must be referenced exactlySQL management tools.Examples include:

Further information about this can be found on the [[Create iReport Templates with Subreports{|Subreport instruction pages]]. class==Sorting and Grouping==__NOEDITSECTION__"wikitable"! scope="col" | Reporting tools || scope==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. 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. [[Image:iRep07.png"col" |centerDatabase packages |500px|''Land report with no grouping'']]<div alignscope="centercol">| SQL management tools''Land report with no grouping''|-</div>| Crystal Reports || Microsoft Access || PGSQL Query Browser|-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.| ArcGIS Desktop || OpenOffice Base || Navicat|-[[Image:iRep08.png|centeriReport || Microsoft SQL Server |500px|'' Land grouped by location'']]TOAD SQL<div align="center">|-''Land grouped by location''| Microsoft Excel || Oracle || Heidi SQL</div>|}

===Adding a Group When connecting an external reporting tool to an iReport Template===__NOEDITSECTION__{{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 Building database views to group your report results must be included in transform data before importing it into other tools significantly reduces the reportperformance hit associated with connecting external tools.}}

To add a group ===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 existing report:up-to-date data source for other applications.

#From the '''Report Inspector'''Using database views, right-click information managers can perform statistical and other operations on the top node of the Report{{IMSMANG}} set for reporting purposes including sum, count and average or more complicated functions available in SQL.#Select '''Add Report Group''' from the menu#: [[Image:IReport Group1.png|center|200px|''Add These operations can be combined with search criteria to provide a Report Group'']]<div align="center">''Add a Report Group''</div>#Select a name highly precise mechanism for your Report Group and choose a field providing data to group by (ereports.g. maAreaType to group by land classification). The name should be For example, information managers can create a meaningful name view that allows you to easily identify returns the contents name of each clearance and a sum of the group such as the field hours worked, AP mines found and area cleared 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'''all progress reports linked to each clearance.

===Sorting Report Results===__NOEDITSECTION__It may This view could then be helpful to sort the report results using values in one pasted into Excel, linked with additional {{IMSMANG}} data or external data or more of the fields. Sorting the results makes it easier linked with a map to review them and locate any records that you may be searching forprovide additional analysis. 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 As such, database views provide a powerful way 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 resultsformatting and analysing {{IMSMANG}} data.</li></ol>

{{note|When sorting data * Database views created in reports that contain groups{{IMSMANG}} are backed up by the {{IMSMANG}} backup functionality. However, ensure it is important to correctly set the “Group by” fields appear first in 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 sort orderCREATE OR REPLACE VIEW syntax from SQL is an easy way to build scripts for creating or replacing views if they already exist.}}