Difference between revisions of "Reports"

Revision as of 19:46, 15 October 2021

Introduction

Reports are used to collect specific data for your reporting needs. The reports in Hornbill are a point in time, meaning that the data is accurate at the point when the report is run. These types of reports are best used for reporting on lists of data. There are various different options with reports which will be explored in more detail below. Reports can be created in various formats and can be scheduled for distribution to documents in Document Manager or by email.

Related Articles

Report List

Each Hornbill application will have its own Report List. These can be accessed by browsing the Admin tool starting at Home -> Applications and then selecting the application that you with to report on. A Reports card will displayed if reporting is available for the app.

Tool Bar

Filter

A filter allows you to located reports that you have already created.

Create New Report

This option start the process of creating a new report

Delete Selected

This option will only be available when one or more reports have been selected within the Report List

Creating and Editing a Report

By either selecting the Create New Report or by clicking on the name of an existing report from within your Report List, you are presented with the options to define your report.

Tool Bar

Save

Once the name of the report has been entered within the Report Details, the save option will become available. For both new and existing reports, they must be saved before you can run the report.

Copy Report As

This allows you to create a copy of this report and add it to your list of reports under a different name. This option is only available after the new report has been saved for the first time.

Run

This starts a manual running of the report. The results of the Run are added to the Report History. This option will not be available when there are unsaved changes on a report. It will also not be available while a report generation is already running.

Download

Download a definition file for the report. This allows you to share a report definition with another Hornbill instance. This option is only available after the new report has been saved for the first time.

Upload

Upload a definition file for the report. This allows you to take a report that has been shared with you and upload it to your Hornbill instance. Uploading a definition file will overwrite any settings that you have previously set for that report, including the name.

General

Name

The name of the report. This is displayed in the list of reports as well has providing the default title on the report output.

Description

Use this area to describe the report

Report Output Type

Report Using

SQL Schema Designer

Report Entity

Report Measure

Report Status

Data Collection

Select Tables

Select Tables

Join

Select Columns

Select Filter

Select Ordering

Data Preview

Output Formats

Report Output Formats

Report Layout

Header Options

Report Table Options

Report Chart Options

Additional Data Formats

Schedule

Report Schedule

Publishing

Report History

Report Types

Reports can be created based on Entities, Measures or by using the SQL Schema Designer.

Output Options

Reports are output by default in PDF format. In the Output Formats section you can also choose to output the report in the following additional formats

CSV
XLS
XLSX

When creating reports, the max row limit is set by default on instances to 1000, this can be increased to a maximum of 25000 rows using the following system setting (Home - System - Settings - Advanced):

api.xmlmc.queryExec.maxResultsAllowed

The only exception to this is for the creation of PDF reports output, this is limited to the following max outputs:

reporting.display.maxcolumns = 20

reporting.display.maxrows=5000

If either of these limits are exceeded when trying to generate a PDF output, the PDF generation will fail. PDF reports can only be generated on in A4 format (either portrait or landscape). Because of this restriction, there could be a scenario where not all included columns will be visible in a PDF report, depending on the data contained in these columns. In this scenario, we can only advise reducing the number of columns or opting for a different output type.

Generation of CSV, XLS, XLSX adhere to the maximum row limit you have set on your instance under the following system setting: api.xmlmc.queryExec.maxResultsAllowed

Please also note that any hidden columns will only be excluded from the visual PDF output, with the data being included in the CSV, XLS and XLSX outputs. If you do not wish the data to be included in these output formats, the column should be removed from the report.

Schedule

As well as running reports manually, you can also schedule the reports to run and be distributed on definable intervals: Scheduling Reports.

Table References

Full schema information and table descriptions can be found in the Application entity viewer located in Hornbill Administration: Click here for more info

@@ Line 1: / Line 1: @@
-__NOTOC__[[Main Page|Home]] > [[Administration]] > [[Reporting]] > Reports
+{{bluebanner|[[Main Page|Home]] > [[Administration]] > Reports|[[:Category:Service Manager Administration|Index]]}}
-==Introduction==
+{{IntroAndLinks|Reports are used to collect specific data for your reporting needs. The reports in Hornbill are a point in time, meaning that the data is accurate at the point when the report is run. These types of reports are best used for reporting on lists of data.  There are various different options with reports which will be explored in more detail below. Reports can be created in various formats and can be scheduled for distribution to documents in Document Manager or by email.|
-Reports are used to collect specific data for your reporting needs. The reports in Hornbill are point in time, meaning that the data is accurate at the point when the report is run. There are various different options with reports which will be explored in more detail in the following sections.
+:* [[Report Types]]
-<br>
+:* [[Report_Scheduling|Report Scheduling]]
-<br>
+:* [[Document Manager]]
-[[File:reports.png |900px|Hornbill Reports]]
+:* [[Application_Entity_Viewer|Application Entity Viewer]]
+:* [[Advanced Analytics]]
+}}
+== Report List ==
+Each Hornbill application will have its own Report List.  These can be accessed by browsing the Admin tool starting at ''Home -> Applications'' and then selecting the application that you with to report on. A ''Reports'' card will displayed if reporting is available for the app.
+===Tool Bar ===
+{{bullet1|Filter|A filter allows you to located reports that you have already created.}}
+{{bullet1|Create New Report|This option start the process of [[#Creating and Editing a Report|creating a new report]]}}
+{{bullet1|Delete Selected|This option will only be available when one or more reports have been selected within the Report List}}
-<div class="mw-collapsible mw-collapsed" data-collapsetext="Show Less" data-expandtext="Read More" style="width:900px">
+== Creating and Editing a Report ==
-==Reporting Types==
+By either selecting the ''Create New Report'' or by clicking on the name of an existing report from within your Report List, you are presented with the options to define your report.
-<div class="mw-collapsible-content">
+=== Tool Bar ===
-:* SQL Schema Designer
+{{bullet1|Save|Once the name of the report has been entered within the Report Details, the save option will become available. For both new and existing reports, they must be saved before you can run the report.}}
-::Building a report using the Hornbill SQL Schema Designer allows you to gather data using the well known SQL syntax. This option is best for users who have experience with SQL methods. The following options are available:
+{{bullet2|Copy Report As|This allows you to create a copy of this report and add it to your list of reports under a different name. This option is only available after the new report has been saved for the first time.}}
-::* Tables. The database tables that you wish to report on. For all requests, the table h_itsm_requests stores the bulk of the data. In this section it is also possible to join tables together so that you can include data friendly names in your report such as the priority name of a request.
+{{bullet1|Run|This starts a manual running of the report.  The results of the Run are added to the Report History. This option will not be available when there are unsaved changes on a report.  It will also not be available while a report generation is already running.}}
-::* Select Columns. The columns from each table that you wish to include in your report.
+{{bullet1|Download|Download a definition file for the report. This allows you to share a report definition with another Hornbill instance.  This option is only available after the new report has been saved for the first time.}}
-::* Filtering. Any filtering that you wish to apply to your report. This is essentially the 'WHERE' clause in a traditional SQL statement.
+{{bullet1|Upload|Upload a definition file for the report.  This allows you to take a report that has been shared with you and upload it to your Hornbill instance.  Uploading a definition file will overwrite any settings that you have previously set for that report, including the name.}}
-::* Counting, Grouping & Sorting. Any grouping or sorting that you wish to apply to your report. For example if you are building a report about incidents that have a priority of either P1, P2, or P3 you may want to group the results by priority.
-::* Data Preview. Allows you to preview the results of the report that you have written before saving.
-<br>
-:* An Entity
-::Building a report using an entity allows you to gather data based on the Hornbill entity relationships. This option is best for users that are not so comfortable in using traditional SQL methods to retrieve data.
-<br>
-:* A Measure
-::Building a report using a measure allows you to base your report on data that has been collected from a measure. For more information, please see the documentation on [[Measures|measures]].
-</div>
-</div>
-<div class="mw-collapsible mw-collapsed" data-collapsetext="Show Less" data-expandtext="Read More" style="width:900px">
-====Building A New Report - SQL Schema Designer====
+=== General ===
-<div class="mw-collapsible-content">
+{{bullet1|Name|The name of the report. This is displayed in the list of reports as well has providing the default title on the report output.}}
-When building a new report using the Hornbill SQL Schema Designer, the first thing that you need to do is to select the option from the landing page of a new report configuration, but before you can do that, you need to create a new report:
+{{bullet1|Description|Use this area to describe the report}}
-:* Click the button '+ Create New Report'
+{{bullet1|Report Output Type|}}
-<br>
+{{bullet1|Report Using|}}
-[[File:newReport1.png |1000px|Create a new report]]
+{{bullet2|SQL Schema Designer|}}
-<br>
+{{bullet2|Report Entity|}}
-<br>
+{{bullet2|Report Measure|}}
-:* Give the new report a name and then an optional description once it has been created
+{{bullet1|Report Status|}}
-<br>
-[[File:newReportSQLDescription.png |1000px|Name & Description]]
-<br>
-<br>
-:* Specify a report output type. For this particular report, selecting 'Single list of data' will be sufficient.
-<br>
-[[File:newReportSQLOutputType.png |1000px|Report Output Type]]
-<br>
-<br>
-:* Select the 'SQL Schema Designer' option from the 'Report Using' menu
-<br>
-[[File:newReportSQL1.png |1000px|SQL Schema Designer]]
-<br>
-<br>
-:* The category field is used for applying one or more categories to your report. This feature is still being developed and is not yet complete.
-<br>
-[[File:newReportSQLCategory.png |1000px|Report Category]]
-<br>
-<br>
-:* Set the report status to 'Available for use'. The Report Status field is used to indicate whether or not the report is active.
-<br>
-[[File:newReportSQLStatus.png |1000px|Report Status]]
-<br>
-<br>
-:* Now that we have defined the basic options for a report, we can start specifying the data that the report will collect:
-<br>
-[[File:newReportSQL2.png |1000px|New Report Configuration]]
-<br>
-<br>
-===Building A New Report - SQL Schema Designer - Data Collection===
+=== Data Collection ===
+==== Select Tables ====
+{{bullet1|Select Tables||}}
+{{bullet1|Join||}}
-<br>
+==== Select Columns ====
-[[File:newReportSQLDataCollection.png |1200px|Data Collection Options]]
+==== Select Filter ====
-<br>
+==== Select Ordering ====
+==== Data Preview ====
-:* Tables. The first thing to do here is to define at least one table for data collection. Tables can be joined based on user defined criteria which allows you to bring in the data you need from other tables to make your report more complete. To add a table, select it from the list of tables and then click on the 'Add Table' button.
+=== Output Formats ===
-<br>
+{{bullet1|Report Output Formats|}}
-[[File:newReportSQLDataCollectionTables.png |1000px|Data Collection - Tables]]
+{{bullet1|Report Layout|}}
-<br>
+{{bullet1|Header Options|}}
-<br>
+{{bullet1|Report Table Options|}}
-:* Joining Tables. To join a table, first select it from the table list and click on the 'Add Table' button. Next, whilst you have the new table selected, click on the '+' icon in the Join Criteria window.
+{{bullet1|Report Chart Options|}}
-<br>
+{{bullet1|Additional Data Formats|}}
-[[File:newReportSQLDataCollectionTablesJoin.png |1000px|Data Collection - Joining Tables]]
-<br>
-<br>
-This will give you the option to specify your join criteria. You can of course choose the type of join that you wish to make by selecting one of the options from the 'Join' menu. The options, other than the standard join are LEFT JOIN and RIGHT JOIN.
-<br>
-<br>
-[[File:newReportSQLDataCollectionTablesJoin2.png |200px|Data Collection - Joining Tables]]   [[File:newReportSQLDataCollectionTablesJoin3.png |700px|Data Collection - Joining Tables]]
-<br>
-<br>
-For the purpose of this example report, here is the join criteria when joining the h_itsm_requests table with the h_itsm_priority table: (The table h_itsm_requests holds the primary key of h_itsm_priority as a foreign key)
-<br>
-[[File:newReportSQLDataCollectionTablesJoin4.png |1000px|Data Collection - Joining Tables]]
-<br>
-<br>
-When specifying join options, we can join tables based on static values, user prompted values, custom criteria, or as we have done here, against table column values [[File:newReportSQLDataCollectionTablesJoin5.png |150px|Data Collection - Joining Table Options]]
-<br>
-<br>
-:* Select Columns. Now that we have chosen our tables that we will base our example report on, it's time to choose some columns. All columns for tables that have been specified or joined will are available for selection:
-<br>
-[[File:newReportSQLDataCollectionSelectColumns1.png |1000px|Data Collection - Selecting Columns]]
-<br>
-<br>
-Select or remove columns using the buttons between the two windows:
-<br>
-[[File:newReportSQLDataCollectionSelectColumns2.png |1000px|Data Collection - Selecting Columns]]
-<br>
-<br>
-:* Filtering. Now that we have chosen our tables and our columns, it's time to apply a filter. This is essentially the same as specifying a WHERE clause in SQL syntax. As we want this report to be about incidents only, we will specify that here, as well as the requirement that the request must have been reopened at least once. We will also include a filter which will allow the user to select dates from and to so that this report can be run against any time period. First click the '+' button within the 'Filter' window and enter the following criteria:
-<br>
-[[File:newReportSQLDataCollectionFiltering1.png |1000px|Data Collection - Filtering - Incidents only]]
-<br>
-<br>
-Click on the '+' button again and enter the following additional criteria to make sure that we only include incidents that have been reopened at least once:
-<br>
-[[File:newReportSQLDataCollectionFiltering2.png |1000px|Data Collection - Reopened Incidents]]
-<br>
-<br>
-Click on the '+' button again and enter the following additional criteria:
-<br>
-[[File:newReportSQLDataCollectionFiltering3.png |1000px|Data Collection - Date Pickers]]
-<br>
-<br>
-:* Counting, Grouping & Sorting. Let's group the data based on priority:
-<br>
-[[File:newReportSQLDataCollectionCounting1.png |1000px|Data Collection - Group by Priority]]
-<br>
-<br>
-:* Data Preview. Click on the data preview tab and enter 'from' and 'to' dates:
-<br>
-[[File:newReportSQLDataCollectionDataPreview1.png |500px|Data Collection - Data Preview]]
-<br>
-<br>
-The query is run and the resulting data is shown:
-<br>
-[[File:newReportSQLDataCollectionDataPreview2.png |1000px|Data Collection - Data Preview Results]]
-<br>
-<br>
-</div>
-</div>
-====Building A New Report - An Entity====
-====Building A New Report - A Measure====
+=== Schedule ===
+{{bullet1|Report Schedule|}}
+{{bullet1|Publishing|}}
+=== Report History ===
+===Report Types===
+Reports can be created based on Entities, Measures or by using the SQL Schema Designer.
+===Output Options===
+Reports are output by default in '''PDF''' format. In the ''Output Formats'' section you can also choose to output the report in the following additional formats
+* CSV
+* XLS
+* XLSX
+When creating reports, the max row limit is set by default on instances to 1000, this can be increased to a maximum of 25000 rows using the following system setting (Home - System - Settings - Advanced):
+api.xmlmc.queryExec.maxResultsAllowed
+The only exception to this is for the creation of PDF reports output, this is limited to the following max outputs:
+* reporting.display.maxcolumns = 20
+* reporting.display.maxrows=5000
+If either of these limits are exceeded when trying to generate a PDF output, the PDF generation will fail. PDF reports can only be generated on in A4 format (either portrait or landscape). Because of this restriction, there could be a scenario where not all included columns will be visible in a PDF report, depending on the data contained in these columns. In this scenario, we can only advise reducing the number of columns or opting for a different output type.
+Generation of CSV, XLS, XLSX adhere to the maximum row limit you have set on your instance under the following system setting: api.xmlmc.queryExec.maxResultsAllowed
+Please also note that any hidden columns will only be excluded from the visual PDF output, with the data being included in the CSV, XLS and XLSX outputs. If you do not wish the data to be included in these output formats, the column should be removed from the report.
+===Schedule===
+As well as running reports manually, you can also schedule the reports to run and be distributed on definable intervals: [[Report_Scheduling|'''Scheduling Reports''']].
+====Table References====
+* [[Table Info: Main Request Table|Main Request Table (h_itsm_requests)]]
+* [[Table_Info: h_sys_ccodes|ISO Country Codes (h_sys_ccodes)]]
+Full schema information and table descriptions can be found in the Application entity viewer located in Hornbill Administration: [[Application_Entity_Viewer|'''''Click here for more info''''']]
+[[Category:Service Manager]][[Category:Administration]]

Difference between revisions of "Reports"

Revision as of 19:46, 15 October 2021

Introduction

Report List

Tool Bar

Creating and Editing a Report

Tool Bar

General

Data Collection

Select Tables

Select Columns

Select Filter

Select Ordering

Data Preview

Output Formats

Schedule

Report History

Report Types

Output Options

Schedule

Table References

Navigation menu

Search