Report Types

From Hornbill
Revision as of 10:37, 16 March 2018 by Stevenb (talk | contribs) (→‎Reporting Types)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search
Home > Administration > Reports > Report Types Index


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

Related Articles

Reporting Types

  • SQL Schema Designer
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:
  • 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.
  • Select Columns. The columns from each table that you wish to include in your report.
  • Filtering. Any filtering that you wish to apply to your report. This is essentially the 'WHERE' clause in a traditional SQL statement.
  • 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.

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

  • 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. You will only be able to use the Measures option as a base for your reports if you have an Advanced Analytics subscription on your instance.

Building A New Report - SQL Schema Designer

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:

  • Click the button '+ Create New Report'

Create a new report

  • Give the new report a name and then an optional description once it has been created

Name & Description

  • Specify a report output type. For this particular report, selecting 'Single list of data' will be sufficient.

Report Output Type

  • Select the 'SQL Schema Designer' option from the 'Report Using' menu

SQL Schema Designer

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

Report Category

  • Set the report status to 'Available for use'. The Report Status field is used to indicate whether or not the report is active.

Report Status

  • Now that we have defined the basic options for a report, we can start specifying the data that the report will collect:

New Report Configuration

Building A New Report - SQL Schema Designer - Data Collection

Data Collection Options

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

Data Collection - Tables

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

Data Collection - Joining Tables

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.

Data Collection - Joining Tables Data Collection - Joining Tables

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)
Data Collection - Joining Tables

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 Data Collection - Joining Table Options

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

Data Collection - Selecting Columns

Select or remove columns using the buttons between the two windows:
Data Collection - Selecting Columns

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

Data Collection - Filtering - Incidents only

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:
Data Collection - Reopened Incidents

Click on the '+' button again and enter the following additional criteria:
Data Collection - Date Pickers

  • Counting, Grouping & Sorting. Let's group the data based on priority:

Data Collection - Group by Priority

  • Data Preview. Click on the data preview tab and enter 'from' and 'to' dates:

Data Collection - Data Preview

The query is run and the resulting data is shown:
Data Collection - Data Preview Results

Building A New Report - An Entity

Building A New Report - A Measure