HaloCRM

HaloCRM Guides

Guides > Reports and Scheduling

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 new sidepane dashboard feature in halo and how to customise it to include your followed tickets. For Portal Users: Video Link - Side Menu Dashboard With Followed Tickets

 
        

Topics Covered In This Lesson

  1. Out-of-the-Box/ Online Repository
  2. Custom Reports
  3. My Reports Group
  4. Writing Your Own Reports
  5. Charts
  6. Scheduling Reports
  7. SQL Query Builder
  8. View the SQL Query via the Query Builder


Out-of-the-Box Options

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:





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

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:

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:


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:

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.



(Released in version 2.103.1) - You can now add Trend Lines to your charts in reports:





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.


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


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. 


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. 

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.


NB: There is limitaiton 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:



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

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 wasn't 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.


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


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:



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 in halo 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.

Popular Guides

Pin It on Pinterest