Changes

__NOEDITSECTION__The primary purpose Two of most important purposes of any an information system must be to are:* ensure high quality of data and* support operational use of the information .One important use of the data is reporting. If it collectsnot 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:

{| class="wikitable" width="8001000"

| align="center" colspan="57" | '''Table 15. Comparison of {{IMSMANG }} Reporting Options'''

| width="195pt" | '''External MINT'''| width="195pt" | '''Other external reporting tool'''

| align="left" | Includes printing field reportsData Entry Forms, current views Summary items and maps

| align="left" | *Navigating data in IMSMA^NG {{IMSMANG}}*General summaries of pre-provided data| align="left" | *Lists and summary reports of single items

*Any report where the data is more important than the formattingCharts| align="left" | *Manipulating data using external tools

| align="left" | *Creating dashboards targeted to specific audiences*Ad-hoc online data analysis (reports and charts can be created online)*Creating reports online or uploading them from iReport or Jaspersoft Studio*Sharing data and/or dashboards/reports/chart*Scheduling, executing, storing and sharing reports| align="left" | *Linking or exporting {{IMSMANG }} data for use in external tools*Building customised reporting tools for {{IMSMANG }} data

| align="left" | *Fast and easy to use

| align="left" | *Can limit data using searches

| align="left" | *Easiest way to get {{IMSMANG }} data into Excel

| align="left" | *Several data sources can be combined or integrated*Roles and users can be created and permissions managed at a detail level*The tool can be customised for different organisations/roles/users| align="left" | *Most powerful and customisable way of accessing data for reports

| align="left" | *Reports are simplistic

| align="left" | *Limited customisation of resulting data (formats, conditional display, etc.)

| align="left" | *Limited customisation of resulting data (formats, conditional display, etc.)

| align="left" | *An decent internet connectivity needs to be available*Requires initial training for administrators (almost no training for end users though)| align="left" | *Requires expert level understanding of SQL

==Basic iReport Data export External Reporting==__NOEDITSECTION__{{IMSMANG}} provides several reporting toolsDescription Includes options that allow users to produce basic reports directly from {{IMSMANG}}. These include printing field reportsData Entry Forms entered into the system, current views maps from the main navigation window and maps Includes building iReport templates for more sophisticated reporting Summary items. Any of these reports can be sent directly to a printer or to an intermediate format such as well as exporting data rich text format (.RTF) or portable document format (.PDF) for use transmitting in external tools such as Microsoft Excel Includes exporting data to CSV electronic format or 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, etcfurther editing., to perform queries and generate reports

 Navigating data in IMSMANG[[Image:AdminGuide_ExampleMapLayoutTemplate.png|center|500px|''Example of a Map Layout Template'']] General summaries of pre-provided data<div align="center"> Lists and summary reports ''Example of single items Simple cross tabs Monthly progress reports for internal consumption Any report where the data is more important than the formatting  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 dataa Map Layout Template'' Linking data to ArcGIS Desktop, Crystal Reports and other tools</div>

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 databasesChallenges  Reports are simplistic Limited to printing and reports provided with the software. No customisation.  Limited customisation of resulting data (formats, conditional display, etc.) Not ideal for incorporating multiple reports together within a larger report  Only works with one item at a time Process is conducted outside of IMSMANG and so not controlled  Requires expert level understanding of SQL  ==Basic Embedded Reporting==__NOEDITSECTION__{{IMSMANG provides }} supplements the basic reporting options available to all users with several more sophisticated reporting options tools that allow users to produce basic reports directly from IMSMANG. These include printing field reports entered into are embedded within the system. In addition to reporting on individual elements, maps from the main navigation window these embedded tools allow for statistical and item current views for summaries aggregate reporting of itemsdata within the database. Any Instead of reporting the contents of these a single report, statistical reporting allows information managers to produce reports can be sent directly about multiple items and to a printer or to an intermediate format use functions 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 barssum, annotations average and legends or use more sophisticated tools like ArcGIS Desktop count to prepare more complicated map layouts for displaying IMSMANG summarise data.  Figure 25. Example of a Map Layout Template

There are two options for embedded reporting: iReports and data export. Both functions rely on {{note|* Map layouts IMSMANG}} searches to filter data. Using iReports, information managers can be printed and scaled build reporting templates to any size paper. This is visualise data in a simple way of producing IMSMANG maps without needing additional GIS softwarespecific and repeatable format.* Map layout templates can be prepared and distributed to individual IMSMANG clientsUsing data export, allowing information managers can export search results to prepare templates based on user preference or functional need}} an external tool such as Microsoft Excel and perform aggregation and statistical analysis.

|[[HowTo: Print Maps Use iReport | Print Maps]]|[[HowTo: Print Maps and Data | Print Maps and DataUse iReport]]

==Embedded Reporting==*clearance: monthly progress reporting*education: number of people trained by type of trainingIMSMANG 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 *land: sum of data within the database. Instead hazardous area by province or type of reporting the contents land*accidents: number of a single report, statistical reporting allows information managers to produce reports about multiple items accidents by type or by province*data quality: number and to use functions such as sum, average and count to summarize data. list of land with incorrect status

There Because iReport templates are two options for embedded initiated with a search, they provide a scalable reporting: iReports and data export. Both functions rely on IMSMANG searches solution that allows one template to filter databe developed for many reports. Using iReportsFor example, information managers can build reporting templates to visualize data in a specific and repeatable format. Using data export, managers report template that shows a list of land with associated ordnance details can export produce different reports depending on the search results to an external tool such as Microsoft Excel and perform aggregation and statistical analysisparameters.

[[Image:AdminGuide_ReportUsingNoSearchParameters.png|center|500px|''Report Example Using No Search Parameters'']]<div align===iReports==="center">''Report Example Using No Search Parameters''</div>

With In the iReport toolfirst example, 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; no search parameter is applied and import the reports into IMSMANG for all data entry personnel to run. Information managers can also design complex reports to summarize data using sum, count, average, subreports and other statistical reporting functions similar to packages such as Crystal Reports. Being fully integrated into in {{IMSMANG, iReport can be translated into any language supported by the system. Using this approach, only information managers are presented with }} is used in 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 MRE: number of people trained by type of training hazard: sum of hazardous area by province or type of hazard accidents: number of accidents by type or But by province data quality: number and list of hazards with incorrect status Because iReport templates are initiated with adding a searchparameter, they provide a scalable reporting solution that allows one template to be developed for many reports. For example, limiting this to land whose status is Worked on, a different report template that shows number of hazards and size of hazards by province can produce different reports depending on the search parametersis generated.

Figure 26[[Image:AdminGuide_ReportUsingSearchParameters. png|center|500px|''Report Example Using No a Search ParametersParameter'']]<div align="center">In the first example, no search parameter is applied and all data in IMSMANG is used in the report. But by adding ''Report Example Using a search parameter, for example, limiting this to hazards whose status is active, a different report is generated. Search Parameter''</div>

Figure 27. Report Example Using a Search ParameterMoreover, if the hazards are land is further limited to hazards that are minefields (excluding UXO and battle areas)a specific type, another report is generated, all using again the same template. In this This way, information managers can build templates and searches to fulfil 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 similar to Crystal Reports.

|[[HowTo: Use i-Report Exporting| Use i-ReportExport data]]

IMSMANG also provides Because data export functionality designed to allow users to export is initiated with a search, it provides a flexible approach for getting data to an external tools such as Microsoft Excel for additional reporting and analysistool. This lets users take advantage of functionality in these tools such as sumsHowever, counts, charts, pivot tables and other once the data manipulation functions to format and present data. Users access this functionality by performing is used in a search in tool outside of {{IMSMANG and then exporting the results }}, it is impossible to .CSV format. Users can choose control how this data is manipulated which columns to export, and may limit the data can be manipulated consistency of reports produced in any external tool that supports .CSVthis way.

Because {{note|{{IMSMANG}} searches return information on only one item at a time, and data export is initiated with a limited to the data returned by the search, it provides a flexible approach for getting data to an external tool. However, once the Data from linked items (such as victim data is used in a tool outside of IMSMANG, it is impossible linked to control how this data accidents) is manipulated which may limit the consistency of reports produced in this waynot available for export. }}

{{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 ==Reporting Tools provided for export}}IMSMA==__NOEDITSECTION__

{{HowTo's|[[HowTo: Export data | Export data]]}}==External Reporting Tools=MINT===__NOEDITSECTION__The Mine Action Intelligence Tool (MINT) is available as of {{IMSMANG also allows information managers to connect advanced }} version 6.0. It is a web-based tool for reporting tools , data analysis and indicator tracking. As such as Crystal Reports or ArcGIS Desktop directly , it allows users to its relational database for highly advanced create and view digital dashboards, perform ad-hoc data analysis online in a very intuitive way, create reports and charts online, and upload reports created in a report generationdesigner tool. MINT is available on a web server managed by GICHD. With this capabilityEach organisation/programme/country can however manage its own space, create roles and users, manage data sources and data domains, information managers can use custom etc. MINT is the most advanced and complete reporting solutions to connect with IMSMANG, allowing solution provided for complete control of IMSMANG outputsIMSMA. Or, managers can modify existing reporting solutions It is recommended to connect MINT to IMSMANGthe [[IMSMA Staging Area]]. A more detailed description, instructions, which leverages technology already implemented in how-to pages etc. are available on the programme and limits the need for retraining[[MINT|MINT pages of this WIKI]].

{{note[[Image:MINT_dashboard.png|Using external reporting tools is center|700px|''Example of a complex activity requiring an advanced understanding dashboard created in MINT'']]<div align="center">''Example of SQL, reporting tools and the IMSMANG data model}}a dashboard created in MINT''

</div> ===Connecting External Reporting Tools===__NOEDITSECTION__The process for connecting external {{IMSMANG}} also allows information managers to connect advanced reporting tools is essentially the same for all tools. External tools connect such as Crystal Reports or ArcGIS Desktop directly to the IMSMANG its relational database via an ODBC connection. Information managers establish this connection by installing the ODBC driver for MySQLhighly advanced report generation. When With this is completecapability, information managers can use custom reporting solutions to connect any ODBC-compliant tool to the with {{IMSMANG}}, allowing for complete control of {{IMSMANG database}} outputs. Or, including managers can modify existing reporting toolssolutions to connect to {{IMSMANG}}, other database packages which leverages technology already implemented in the programme and SQL management toolslimits the need for retraining. Examples include:

Reporting {{note|When using external reporting tools Database packages and SQL management tools* Crystal Reports* ArcGIS Desktop* iReport* Microsoft Excel * Microsoft Access* OpenOffice Base* Microsoft SQL Server* Oracle* MySQL Query Browser* Navicat* TOAD SQL* Heidi SQLwe 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.}}

When ===Connecting other External Reporting Tools===__NOEDITSECTION__The process for connecting an external reporting tool to IMSMANG, it tools is recommended that information managers build database views within essentially the database to do any necessary data transformationssame for all tools. While it is possible External tools connect to directly import or connect the {{IMSMANG }} relational database tables in external tools and perform transformations within via an ODBC connection. Information managers establish this connection by installing the external toolsODBC driver for PGSQL. When this is complete, the recommended method is information managers can connect any ODBC-compliant tool to do transformations within the {{IMSMANG }} database using , including reporting tools, other database views packages and then import the resulting views into the external SQL management tools.Examples include:

{{note|Building database views to transform data before importing it into other class="wikitable"! scope="col" | Reporting tools significantly reduces the performance hit associated with connecting external || scope="col" | Database packages || scope="col" | 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|}

===Building Database Views===Building database views is the primary way of transforming data to support When connecting an external reporting. Information tool to {{IMSMANG}}, it is recommended that information managers can use SQL editing tools to build and save database queries as views that can be used within the database to format do any necessary data for easier reportingtransformations. Using While it is possible to directly import or connect {{IMSMANG}} database views, information managers can join data together into flat tables that allow for easier incorporation into reports or other analysis in external tools. Database views act like database tables and can be imported or queried from perform transformations within the external tools. Moreover, views automatically refresh as data the recommended method is added to do transformations within the {{IMSMANG }} database using database views and provide an up-to-date data source for other applicationsthen import the resulting views into the external tools.

Using {{note|Building database views, information managers can perform statistical and to transform data before importing it into other operations on tools significantly reduces the IMSMANG set for reporting purposes including sum, count and average or more complicated functions available in SQL. These operations can be combined performance hit associated 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 clearanceconnecting external tools. }}

<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>

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.

* 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 UPDATE REPLACE VIEW syntax from SQL is an easy way to build scripts for creating or updating replacing views if they already exist.}}