HaloITSM Guides

Documentation to assist with the setup and configuration of the HaloITSM platform

Reports and Scheduling

The following video explores Ticket Reporting and how you can configure actions to generate reports from within a ticket.

For Portal Users: Video Link - Ticket Reports

The following video explains the side pane dashboard feature and how to customise it to include your followed tickets.

For Portal Users: Video Link - Side Menu Dashboard With Followed Tickets

In this guide we will cover:

- Out-of-the-Box Options/Online Repository

- Custom Reports

- My Reports Group

- Writing Your Own Reports

- Scheduled Reports

- SQL Query Builder

- View the SQL Query via the Query Builder

- Making Reports Show Viewer Specific Information

- Tip to Join Tables

- Timezone Adjustments

- Money Field Reports (as of v2.184.1+)

Admin Guides:

General Settings (Reporting)

Related Guides:

Out-of-the-Box Options/Online Repository

The reporting suite is a powerful tool, designed to help you understand and improve your service desk processes. It is effectively a friendly, front end of your Halo database. You can extract virtually any information from your database with a SQL query and subsequently export raw data for analysis in an external piece of software e.g. Excel.

Your reporting suite comes with an extensive list of out-of-the-box reports. There is also an online repository with even more reports you can download from to add to your own set of reports. This is accessed by clicking 'Online Repository' as below:

Fig 1. Online Repository area within the reporting suite

Custom Reports

We offer a report writing service as part of the support we provide but any large bespoke reports may incur a fee for the work to be done. More often than not we can provide a report free of charge or one is available in our online repository that we can point you to. The online repository has reports where you can use the filter to search for specific table names or column names.

Good Online Repository Reports for Searching the Database:

Database Tables & Cols
All Columns & Tables
All Tables

My Reports Group

After saving reports you can add them to a "My Reports" group, this is a group that is customisable per agent. To allow for this, navigate to Configuration > Reporting > General Settings and check on this option:

Fig 2. Enable the 'My Reports' view setting in Configuration > Reporting > General Settings

Now when you click into a report, you will see the "Add to My Reports" option, when clicked, the report will be added to this group. The "My Reports" group will be automatically pinned to the top of your report list. For example, there is a lot of useful information on the Database Tables & Cols report, so I added it to My Reports, notice that we can search the tables and columns using the filter:

Fig 3. Database Tables & Cols report

Writing your Own Reports

If you have the required knowledge or wish to learn you can write your own reports in the reporting suite. The Halo reporting suite is read-only access and as such you cannot delete, edit or change the data from here.

We provide two schemas, maps of the database, and a short documentation piece for this purpose:

Faults relevant Database Model - This shows the portion of the database immediately relevant to queries regarding the faults table, the table in which all of your requests/incidents/problem tickets are stored.
Asset and Contract relevant Database Models - This shows the portion of the database relevant to assets and contracts.

The SQL management engine we support and use is Microsoft SQL Server so anything you may need regarding writing your own queries will be available under that syntax and often freely available on the internet.

Reports can be printed, exported to CSV, sent via email, or scheduled to send automatically.

You can also use custom PDF templates when printing reports. See our PDF template guide for more information.

Scheduled Reports

Scheduled reporting is a tool that can send reports via email to relevant parties on a specified schedule. This can be anything from SLA stats on tickets raised/closed in a period to managerial staff, all the way to a daily breakdown of agent hours and interactions in the day.

These can be set by editing the given report heading to the scheduled emails section and clicking the ⊕ symbol and filling in the relevant form as necessary.

Fig 4. Scheduling section in a report

As of v2.176.1, a dropdown named "Reporting Period" will show in the schedule configuration. By default, this will be set to "*Inherit from the report*", but other options appear in the dropdown to change this to other periods. This feature allows multiple report schedules to be sent with different period without having to clone and edit a new report.

The days of the week scheduling is self-evident, send every Monday at 09:00 for example.

Fig 5. Setting a report schedule of when to send the scheduled report

This screen is a little more involved:

"Send on this month" being the calendar month's number with 0=Every Month, 1 = January, 2 = February etc.
Repeat months being repeat every X months.
Repeat years being repeat ever X years.
Days ahead of schedule meaning you can set a report to send say on the 1st of every month but schedule it one day ahead to be the report for the month up to the last day exclusive.

If "Send on this month" is 0 then the schedule will ignore repeat months.

Adding emails to send the scheduled report to

Within Halo, you can set the customer to apply the schedule to, this will allow you to use the dollar variables inherited for that customer i.e. when using $AREA in the custom report, the customer's name will be visible in the report data. The "To" field of the scheduled report is where we can put semi colon seperated email addresses, this will be the recipients of the scheduled report.

Fig 6. Adding emails to send the report to in the To box

SQL Query Builder

In Halo there is a custom query builder which is perfect for our customers that have limited knowledge of SQL. First head to the new button on the top right hand side of the reporting suite and click it.

Fig 7. Tabs when creating/editing a report

Go to the data source tab of the new report creation screen and select *Use Query Builder*, then an entity of Halo can be chosen i.e. Tickets.

Note: There is limitation to what the query builder can return in terms of entities to choose from and columns within entities that can be chosen. For more advanced reporting, SQL skills will be required.

Entities to choose from:

Fig 8. Available entities in the Query Builder

Then heading to the "Fields" Tab, the specific columns can be added to the report, for example we could choose this:

Fig 9. Example of fields in the Fields tab

In this report I want to know what the satisfaction score was for each ticket and the ID of the agent that closed it ("Closed By" can also be added, this is the column for the name of the agent) . I was not sure of the exact team names, so I used a contains clause in the conditions and I wanted sales teams, so I used Sa and that will find all teams containing the string "Sa" (Note: this is not case sensitive).

I then headed to "Preview Report" to view the data in a table format.

Returning Asset Data

One of the areas in Halo that is hard to report on is Assets. With the Query Builder you can return the data seamlessly.

Fig 10. Assets Entity in the Query Builder

I may want to find a list of assets that are marked with status "Not Ready":

Fig 11. Adding a filter to the report results with conditions.

View the SQL Query via the Query Builder

If you would like to see the SQL that made this possible, you can edit the data source and change it to Custom SQL Query, this will give you the SQL that the query builder has made:

Fig 12. Switching back to SQL data source

Fig 13. The SQL from our example.

As you can see, the asset reports are fairly difficult to create, so it would be recommended to use the query builder for this kind of report.

Making Reports Show Viewer Specific Information

Within the reporting suite there are pseudo-variables that you are able to match the date on the report to, being $agentid,$userid, $siteid, $clientid, and $invoiceid.

$agentid allows you to make internal reports only match to the viewing agent. $userid, $siteid and $clientid are used so that users logging into the portal can only view values on reports in the end user portal which are related to themselves or their site site or client.

$Invoiceid is different from the ones above as instead of relating to the recipient of the report, it instead will only invoice relevant information in the case that you create a report that is inserted into an invoice via the PDF templates.

For an example, say that you have a report counting how many tickets have been cleared by each agent, and you are using the column ClearWhoInt to find the agents for you, then you would be able to add the following to the code:

where $agentid = clearwhoint

This will make the viewing agent be able to see their count of closed tickets but non of the other agents in their organisation.

Tip to Join Tables

If you are unsure about how to join certain tables, the online repository is a way to look at how to do so. Since it has such a large number of pre-made reports, you can search "join ___" in the search bar to bring up reports that contain that in the SQL.

Fig 14. Finding a join in the repository.

Upon saving the report to your instance, you can view the SQL in the "Data Source" tab to see how it was used.

Fig 15. Join area in a report.

Timezone Adjustments

In the "Details" tab of the report, you can set the timezone the report should follow. This will default to your current timezone, but can be manually changed.

As of v2.176.1, you can also enable "Do not make timezone adjustment for Reporting Period (Use for Date only fields)". This removes the timezone adjustment from the "Reporting Period" filters at the top of the report, which can be useful when the reporting period is a date field.

Fig 16. Timezone adjustments.

Money Field Reports (as of v2.184.1+)

If creating a report that is based on profitability etc, you may wish to format the results as money. In the field list, add all the fields for you report and edit the field you wish to format as money. Here you can check the "Is Money Field".