Building your first custom SQL report in HaloPSA
A practical starting point for MSPs who want to write their own HaloPSA reports instead of waiting on support to build them
The short version
This tutorial walks through building a basic custom SQL report in HaloPSA from scratch, using the area and site tables to pull a list of customers and their sites. It covers reading the SQL schema, writing a select query, and joining tables correctly, useful for anyone tired of asking support for every small report.
What you'll take away
-
The SQL schema is your map
HaloPSA's SQL schema shows how tables relate to each other, and support can fill gaps it does not cover.
-
Start with select star from area
A simple select star from area pulls every customer record straight into a report data source.
-
Area links to site through s area
The site table stores its parent customer as s area, which must match the area table's a area column.
-
Multi part identifier errors mean you forgot the join
Trying to pull columns from two tables without a join throws a cannot find the multi part identifier error.
-
Alias columns for readability
Renaming a area as ID and a area desc as customer name turns raw SQL output into something a client could actually read.
-
Filter after the fact in HaloPSA
You do not need to filter everything in the SQL query itself, standard report filters can narrow results to a single organisation afterwards.
Key insights from the episode
-
Build custom reports from Reports, My Report, then choose Create a custom SQL query under Data Source.
-
Prefix column names with the table name once you join more than one table, or HaloPSA cannot tell where the data comes from.
-
The area table holds customers and the site table holds sites, linked by a area on the area table matching s area on the site table.
-
Use the join option in the data source builder rather than trying to reference a second table directly in the select statement.
-
Alias every selected column, for example a area as ID, so the finished report has readable headers instead of raw database column names.
-
A test successful message on the SQL query confirms the syntax works before you save and view the report.
-
Once customer and site data are joined, ordinary HaloPSA report filters can restrict the view to one organisation without touching the SQL.
Questions people actually ask
How do I create a custom SQL report in HaloPSA?
Go to Reports, then My Report, give the report a name and choose a location such as Ticket Reports. Under Data Source select Create a custom SQL query, write your select statement, click Test to confirm it works, then save and view the report.
Where do I find the HaloPSA SQL schema?
HaloPSA provides a SQL schema that shows the tables in the database and how they relate to each other. It does not cover everything, so for anything missing you can contact Halo support or ask on community forums such as Tech Tribe.
How does the area table relate to the site table in HaloPSA?
The area table stores customer records, and each site in the site table references its parent customer through the s area column. Joining on area equals site.s area lets you pull customer and site data into a single report.
Why does my HaloPSA SQL report say cannot find the multi part identifier?
This error appears when you try to select columns from a second table without joining it first. HaloPSA has no way to know how the tables relate until you add a join clause linking them, such as site on area equals site.s area.
Can I filter a HaloPSA report to show only one customer?
Yes. Rather than restricting this in the SQL query itself, you can apply a standard filter on the finished report, for example filtering the customer name column to show only one organisation.
Do I need to be a SQL expert to write HaloPSA reports?
No. The video's presenter is not a professional SQL report writer either, and describes learning it gradually over about 40 hours of practice. A basic select statement with a join is enough to start building useful reports.
Full transcript
1,341 words
Read full transcript
Collapse
Full transcript
1,341 words
Hello it's me again, two days running. This might become a trend. I thought today I would do a quick rundown of how to make a very basic report within HaloPSA. So a lot of the questions I get asked, there can you make me these reports Connor, and I try and help out as best I can. I don't sell it as a service because I'm definitely not a SQL report writer, but I spend probably about 40 hours over the past month trying to learn this and I'm getting slowly there with it.
Um and actually it's become a really useful skill to have just in general. So before I get started in making a report today, I just want to show you a few things so you can actually access the SQL schema. Now this isn't a complete schema, um but for the majority of things that you need to get data on, you can kind of find it very well. And if you need to find anything else, just email Halo support or you know mention on Tech Tribe or any of the forums you're on.
But essentially um you can basically go through this SQL schema and you can sort of see the correlation between the different databases, all the different tables in the database should I say, and it's already broke, what a fantastic start. So I'm going to basically work today on area and site. This is basically customers and sites and I'll show you how it all links together. But in terms of understanding how the two tables link and they actually added the lines in here, which is a really nice thing, so you can see that if I want to reference how many faults there is from a customer, then I need to match area on area_int and I'll explain what this means in a minute so don't panic.
So to get started, you go into reports, you go to my report and in the top right hand side you click no, I'm going to call this Connor demo for today. And I'm just going to keep it in ticket reports. This is basically the location of where it is on the left hand side. And then I'm going to go ahead to data source and I'm going to create a custom SQL query.
So the first thing to note is I'm going to type in select star from and I want to be pulling all the data from this area table and I'm going to type in area. So what I'm doing here is basically saying I want to select asterisk, which is wildcard for everything, from the table area.
If I click test it should say test successful and if I click save you will then see that in my Halo instance on my database I only have um five customers here.
You can then get a little bit more fancy with this. So this is only showing me my customers, this isn't showing me my sites. So now I want to know right, I want to know where the sites are. So you need to head back to your SQL schema and we can see here that um site is actually linked to this table. I mean it's going to be tiny on the camera, you see that site is actually linked to area from s_area.
So this is where stuff starts to get a little bit head melty I'll be honest with you, but um once you understand how to sort of navigate this, you kind of can start throwing them together. So I'll show you this very quickly, so we have site s_area, going to make a very quick note of that, site s_area, and all I really want is the area and the description of it, because that's the customer ID and then description is obviously the name.
Um so I want to do the following, I want to select a_area as ID so this will show me that a_area and the column header will be ID. And then I want to say um a_area_desc as let's say customer_name. And I'll delete this little bit here for a minute.
I test that and then view the report, you'll then see that I filtered everything else out and all I've got is ID and customer name.
Now I want to know what sites these customers have and as mentioned that information is stored here. So I also want to select now from site the site number, uh no I don't. I want to select s_area, so I want to select s_area just to prove this works basically as um site_ID. And then I want to do the site description.
Now because we're using multiple tables here, I do have to prefix these ones with site so I know what table I'm pulling it from. Add site. Now what you'll notice when I try and do this is it will say error, can't find the multi-part identifier, and this basically means that it has no idea really where site is and you can't join the two together.
So what we've got to do is click join and then we want to join site on um area equals site dot s_area. I think that should work. View report.
And as you can see now we have the ID which is the same across both the tables. As I mentioned up here, we've got area_int um links directly to s_area. So these should be the same. This is how you match the two tables across. And then we're basically saying where the site_ID matches the customer_ID then show me the sites according to that.
And what I've done in my SQL query is I've selected the area as ID, the description I want to show that as a customer_name, and then site to the site_area, site_ID, and site_description as site. And then I've basically joined site, which is the table that I didn't reference above, and said that basically where the area table um column a_area matches site s_area then join those two together.
And that is basically how you start building up these simple reports. And you can go one step further here and you can start just showing only certain values that equal a certain thing. And but a lot of cases you can just filter these out without doing it in the SQL query and just say I only want to show let's say Mendy test org and I want to see what the customer name is and what the site name is. If you're not bothered about the site_ID it's quite simple to move it from here.
Save it, view the report, and then you end up with quite a nice report.
I'm not going to go any further today. I thought I would just slightly show you how to start building these out and have a play. I will hopefully try and start building upon this foundation over the next few weeks you know, slightly editing and expanding on this report video. But that is it, that is my quick rundown on how to make a very basic report in HaloPSA. I hope you found it interesting. Any questions, you know where I am, have a good day, bye.
Author
Related tutorials
Connor and Robbie have been absolutely instrumental in the transformation we have seen in our systems. Their expertise in Halo along with their insight into industry standards have enabled us to leverage significantly more operational success then we could have accomplished by ourselves. Anyone that is looking for a team to help with your Halo deployment look no further.My IT Place
Our Core Services
Offering support to enable sustainable success for your organisation.
