HaloPSA

HaloPSA Guides

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

Guides > Report Builder

Report Builder

In this guide we will cover:

- Opening the Query Builder

- Adding Fields

- Adding Conditions

- Generating the Report

- Adding Filters

- Saving the Report

- Using the Report

- Customising Fields (Sorting, Grouping, Count, Compare to previous period)

- Using the Query Builder to create SQL



Admin Guides:


Related Guides:


Opening the Query Builder

The Query Builder function in the reporting suite will allow you to create reports without any knowledge of SQL.


From the Reporting module, under the Data Source tab, you will find a "Query Builder" option in the "Data Source" dropdown.


Fig 1. *Use Query Builder* data source.


Upon selecting this option, you will be able to select the specific entity you are looking to report on.


Fig 2. Entity dropdown.


Adding Fields

Below the "Data Source" section, you will see a "Fields & Conditions" section. This is where you add the columns to your report and specify respective conditions. The fields selected here become the columns in the report.


Fig 3. Fields & Conditions section.


When adding fields, you can select multiple at once to add. Here, I have added some commonly used fields for reporting on ticket data.


Fig 4. Adding fields to the report.


These appear in a list format like in Fig 5 upon pressing the "Save" button. You can rearrange the order of these fields by hovering over a field and the dotted icon will appear to the left of the box (as seen in the "Ticket ID" field), and you can then drag-and-drop these to the order of your liking.


Fig 5. Fields set on the report.


Adding Conditions

You can then add conditions to restrict which tickets (or whichever entity you have chosen) appear in the report.


Click "Add" and a "Choose a Field" dropdown will appear. Choose the field you would like, and upon selecting, the filter type box will appear to select whether you want the condition to include or not include that field. Finally, set the criteria. I have set my report to include incidents, so only tickets of ticket type "Incident" will show.


Note: "Includes"/"Does not include" work by entire exact match, whilst "Contains"/"Does not contain" works by if the condition entered is part of the whole.


Fig 6. Setting conditions on the report.


Generating the Report

You have now made the basis of your report, and can generate it to see how it looks. Click the "Load Report"/"Refresh Report" button, and your report will appear.


Note: If you are making a report that is likely to have a lot of results, it is recommended to filter the report first before doing this.


Fig 7. Generating the report.


Adding Filters

You can then add filters and time restrictions onto the report. I have set the status to not include "Closed" so only open tickets will show, and a date filter can be set in the "Reporting Period". 


Fig 8. Filtering the report.


Saving the Report

In the "Details" section, set the title and description of the report, and the group you want to store it in.


You can then click the "Save" button in the top left, and you have your report!


Fig 9. Setting report details.


Using the Report

Once your report is saved, it will work the exact same way any report will do. It can be filtered as mentioned, but can also have charts added to it, or used on a dashboard. Guidance for this is linked in the "Relevant Guides" at the top of this page.


Customising Fields

The fields added to the report can be grouped or sorted by to customise the report even more. This can be done by clicking the pencil icon by the field name, shown in Fig 6.

Fig 10. Customise fields configuration options.


Operation

Count, sum or average can be selected in this dropdown.


Grouping

Selecting "Group by this column" will group the report into sections based on the data in that column.


Total row calculation

Sums or averages can be shown at the bottom of the column.


Sorting

Columns can be set to sort either ascending or descending order.


Hide Column

Hides the column from the report table.


Is Identifier

Allows comparison of a previous period of the report.


Setting the "Report Comparison" to "Previous Period" in the "Details" tab.


Fig 11. Report Comparison field.


Choosing an identifier to not show a comparison between - usually a field like Agent or Customer which consistently stays the same.


Fig 12. Selecting an identifier field.


Upon loading the report and choosing a Reporting Period in the filters at the top, the columns will show both that period, and the previous one in the brackets  next to it.

Fig 13. Report showing comparison.


Using the Query Builder to Create SQL

If a report is made from the query builder fields, you can also then go back to the "Data Source" tab, and change the data source to "Write a custom SQL Query".


Fig 14. Switching the data source to *Write a custom SQL Query*.


This will then autofill the SQL with what was created by the query builder. This can be useful if you have a knowledge of SQL to create a basis of a report that you can then tweak, or if you are new to SQL it can help you visualise what the query can look like.


Fig 15. SQL query made by the Query Builder.


Popular Guides