Hornbill How To: Write Efficient Reports

Home > How To > Reporting > Write Efficient Reports

Introduction

Hornbill has quite a detailed reporting tool that allows users to gather information on requests logged for specific periods and according to Service Level Agreements. The wiki page https://wiki.hornbill.com/index.php/Report_Types has some useful information on the different types of reports and how to create them. This How To guide is meant to be used in conjunction with the Reports guide on https://wiki.hornbill.com/index.php/Reports It is easier to understand this FAQ after you have attempted to create a few reports on your own using the wiki notes.

This How To Guide should be consulted if you are creating a report using the SQL Schema Designer if you are extracting data from more than two tables in the database which is where JOIN Conditions come in.

Join Conditions

JOINs ensure that the data which should be included in query operations matches. JOINs also result in a smaller set of results because each pair of rows that is returned from the join condition is combined to form a single row in the resultset.

For example, in order to report on requests raised by a particular customer, we can join the customers and requests tables using the relationship established by the user id. It is recommended that when creating table JOINS ensure that a field in one table matches a field in another table. In our case, h_itsm_requests. h_fk_user_id equals h_sys_accounts.h_user_id really helps improve report performance.

If there are specific popular cloud services that you would like to see us integrate with please let us know by posting on our Hornbill Forum

Join Between Two Tables

At the moment if you specify two tables, the system will advise that you specify a join condition:

(This is equivilent to the following SQL statement):

In this example, rows from the sys accounts table correspond to rows in the requests table on the basis of the user.

Another example if you wish to create a report on tasks then create a join between h_itsm_requests and h_sys_tasks based on h_itsm_requests.h_social_object_ref equals h_sys_tasks.h_obj_ref_urn.

In this example, rows from the tasks table correspond to rows in the requests table on the basis of the object reference.

Join With More Than Two Tables

If you wish to join more than two tables, ensure you show all relationships among them:

For example, to reports on Incidents by Priority, first set a JOIN condition between h_itsm_requests and h_itsm_priority

Then create a table JOIN between h_sys_accounts and h_itsm_requests

The important thing is to consult the Entity Viewer found in Home > Service Manager > Entity Viewer and see how the tables and keys are related to help you come up with the right join conditions for the right reports. However, this is still in Development and may not be 100% accurate. You can use it to obtain a rough idea on what fields match in two tables.

The absence of a JOIN causes the number of results to be very high because the result will be results from one table multiplied by results in the second table. These results need to be stored in virtual table before actually being sent as a response back to the client. This combined with the pressure on SQL could be impact services if this report is run. Even if we specified maximum number of results returned by an SQL query, this would only relieve the pressure on the data service but the SQL server would still be impacted.

Filters

The first part of this FAQ focused on Join conditions but without filters, the resultset would still be large and impact performance especially if creating a report on calls logged in the last year if the Servicedesk is very busy logging hundreds of calls daily. Better to report on a monthly basis:

The report above has the join conditions recommended in part 1 but also has a filter for the date range 1st February to 28th February. Only February requests will be returned which will contribute to a performance improvement.

Another example of a filter is one which references two different tables as long as they are mentioned in the JOIN condition. For example, in the Open Incidents by Priority example, the filter can be specified as shown in the screenshot to reduce the resultset to open high priority calls logged by Alan Key rather than all high priority calls.

Conclusion

In order to create efficient Hornbill reports, if you are reporting on more than two tables, ensure you specify one or more JOIN conditions between all the tables being queried. Secondly, to reduce the resultset to a more manageable value use Filters. Filters will ensure your reports run faster.