Why your HaloPSA burden rate report ignores half your agents

Connor: This is going to be a slightly different video to what we normally put out. This is going to be more of a learning journey if you will about how we approach this type of subject, which you'll see by the title, which is all about agent cost and burden rates. I'm going to intentionally do it incorrect at the start just to be clear, and then work back with you so you understand what we're doing.

So what are we talking about today? We're going to be looking at HaloPSA. Obviously we're going to be tracking agent costs and working out how much a ticket costs us in terms of agent burden rate. I'm going to show you at the start a few mechanisms that we need to have set up as fundamentals, and then we're going to write a report together, which is something I do live very often, or for the video very often. But I'm going to work you through how you'll potentially navigate this.

Now we've done this for quite a while now. We kind of know the schema a little bit too well, I'm not going to lie, but I'm going to work through this with you as a bit of a workshop. So this is going to be a longer video, but anyone who is getting into Halo or wants to start writing reports, you may find this really valuable. So let's jump right into it and let's get stuck in, shall we?

Connor: So for those who don't know us, my name's Connor Fagan and we are Renada Solutions, and today we're going on what I'm calling this title of a learning journey through agent cost and burden rates. And then we're going to write a report together, show you a few of the pitfalls you may fall into, and it might even spark some of you to go and check some existing reports you already have. And then build a report together, so it's going to be a bit of a longer video. I'm going to show you how I would navigate getting stuck, like you'll probably will, because it's complicated to do this. And then hopefully we'll have something at the end which you can all start to leverage. So let's get stuck into it and discuss what I'm talking about.

So in HaloPSA we obviously have a ticket, so what I'm going to do for this video is make a new ticket, and I'm just going to make it an incident. I'm going to give it some categorisation that's fine. What I'm just going to do is I'm going to triage it, triage it if I could speak, that'd be great, and then I'm going to add a comment and I'm just going to add 30 minutes of remote support. It wants an asset, of course it wants an asset. Why does this build ask me for this? I do not know. So I'm going to have 30 minutes here of remote support that is built at an hour, because I am currently out of hours, which is actually fine for this video. It doesn't matter, but essentially it's out of hours for me. It is nearly 9 PM because I'm a loser. And basically what we're going to be doing today is working out how much this ticket has cost us from an agent cost or agent burden rate perspective.

So how do we go about doing that? How do we go about understanding that? Well, the first thing we have to do is make sure that our staff or our agents actually have costs set up against them. So to do that, you've got to go into configuration, you've got to go into teams and agents, and agents, and then under an agent, let's just pick Jacob. He's our new Solutions Architect, for anyone interested. I'm going to scroll all the way down, and what you'll find down here is a cost per hour. Now you could just put in their salary cost here. However, our recommendation is you put in their burden rate. If you don't know how to work burden rate out, just Google burden rate calculator. You'll find loads of them. Pick which one kind of aligns to what you think and the way you go.

But let's say that I don't know. Let's say I pay Jacob 10 pounds per hour. He'd be lucky. But his burden rate for the company is 20 pounds an hour, right? So I'm just going to type in here 20 an hour, or 20. Doesn't matter. The currency you're in, just 20 is in the integer for this video. What you have to understand about this is that this cost for Jacob is static and is always. And what I mean by that is if I gave Jacob a pay rise tomorrow and I updated this to be 25 an hour burden rate, any existing reporting will reflect this rate. Halo currently has no mechanism to say between these dates, this is what Jacob's burden rate was. As of this new date, this is what Jacob's burden rate is. So you have to be very conscious about that.

So this isn't the best metric in the world if you're doing historical reporting. However, if you're looking for point in time reporting, so what has he done this month? Well, this month his burden rate to our company has been £25 an hour. I'll do 20 just to keep the maths super simple for today. So really important to note that I do believe Halo have it on their development log to make this tabled, so you could add in different salary or burden rates at different times. That way the reporting can reflect the time period of the ticket. But we'll move on that for now.

So I'm also going to do is jump to myself, and I'm just going to put in here my burden rate is, let's say I'm feeling a bit fruity and is 50 an hour, right? So we have a ticket, and that ticket that I just did is called test, and I spent 30 minutes. So some quick maths tells you that if my burden rate is 50, well, this ticket so far has cost us £25 in paying me, okay? That's if I did actually spend 30 minutes. That's another conversation for another day.

So £25. However, how do we report on that? So let's start by doing some reporting, shall we? So there's a few ways that I like to approach reporting. I have this test folder down here, so let's make a new one in here. We'll call it YouTube cost report, okay? And the way that I like to start doing this is I like to start by drafting out what columns I want in here.

So what I really want is the ticket ID. I want to know what customer it's for. I want to know what agent's doing it. I want to know the agent cost. And I might want to see how much time was taken on the ticket. And I might also want to see, I don't know, the let's say the ticket type, right? Let's just start with some basics. I'm going to put all these in brackets like so. If I could click, that would be an amazing start to this video. Like this. So this is what we need to start with, okay?

Now I would normally write this down in notepad or something, not do it in here. So the first thing you're going to do is go, well, how do we know where to get all this data from, Connor? I'm sure it's not that simple. In HaloPSA, you're right. It's not. But with a little bit of knowledge and a little bit of determination, you'll get through it.

So let's start off with the first thing we know. Well, we need to be getting data about this ticket, okay? And this ticket is 140, okay? So I'm just going to do is keep that in a separate tab for now. 140. So what I'm going to do is just select everything from faults, okay? That's going to show me every single column in the Faults table. Because if you don't know, the faults table is where the ticket data is stored, okay?

However, I don't want to return every single ticket in our Halo build with every single column. That report might not even load. It could be thousands of lines, right? So I'm just going to do a where statement, and I'm going to do where the fault ID equals 140. And if we test that, that should say success. Great. And we're going to save it.

Now, if you didn't know that fault ID is the column header, don't panic too much, because what you can do to start with this is you can just select the first entry in the table. So select the top one, the first one from faults, to then understand what data you have in all of these columns, okay? Now as we've determined, it's fault ID, so we can go back and go, great, I'm really interested in that ticket. I want to be getting the data from there. So where fault ID equals 140, okay?

So the first thing I said I wanted is I wanted the ticket ID, okay? Which is great. So we know in this table here that fault ID as ticket ID is what we want to see. We want this column fault ID to display us the ticket ID. Piece of cake. Fantastic. The next thing I want to do is find the customer. Now the problem is, if you type in here customer, you're not going to find very much. There's nothing in here that is going to return the customer name. And this is where the SQL can get very complicated. I will link a few of the SQL diagrams below to help you with this one.

However, what I happen to know, and again the SQL diagrams will help you with this, is that the word area is what we can leverage for the customer ID. So we've got here area int. So I can go, great, area int, okay? The problem with that though is that area int isn't actually the customer name. It's just going to show us the ID of 59, okay? So I'm just going to do is go down here and do join the area table. We'll come back to that in a minute on area int equals something, okay? So we need to join on the customer table, which happens to be area. I'll show you how we know that in a minute, and we need to put something in here for the customer, okay?

Then we need to do the agent. Well, we have this assigned to int here, so that is who it's assigned to. And again, we, that's just a number, just an integer. So we don't want that. So we need to join some table on something where it equals assigned to int. Now this part in the video is actually incorrect. We don't want to do this. I will elaborate later why, but I'm doing this to show you what you might need to check, okay?

Then we have the agent cost. Well, if you was to type in cost in here, you won't find anything. You'll see a few things that don't actually relate to it. So we don't know at the minute where to get the agent cost from. We have no idea. Time taken is time in this table. I happen to know it's not. It does have the clear time, so when you close it, it will tell you how long it took to close it. But there isn't actually anything to do with time. So again, we go back and we go, right, no idea where to find time at the minute. And then ticket type, again, a little bit confusing.

But if we look through some of this here, what you will find in here is request type and the request type. And there's also request type new, which is even more confusing. But request type new is actually the ticket type. Request type is the ticket type. How are you supposed to know that without having that intrinsic knowledge? I don't understand, but we'll come back to that.

And again, what you're seeing in here is an integer. It's not telling me it's an incident or a change request. It's just saying something. So I also want to join something on something where it equals request type new, okay? So we know we need these fields and we're currently in the faults table, okay?

So what we used to do a lot of the time when we started to dig out this information is we used to look through the online repository of reports, or look in your Halo reports to see actually, is there another report that I can get this data from, right? So let's say we want customer as a starting point. So we can go in the online repository, we can type in the word customer, okay? Or even better, as we know we're working on faults, we can type in faults. And what we can do is we can look through a few of the fault reports and go, aha, this says action done by clients. But I have typed in the word faults. Maybe this report will show me how to grab the customer name.

What you can do is add this report to my library and then what you can do from there is dive into the report in your build. So copy the name of it, click reports, and open it and edit it. And in here, now this is a horrible report. Why would it be? But in here you might start to see some of the joins in here. Now currently we're actually pulling from the actions table and we're joining on faults, which may confuse you. So I would say at this point, you know what? Let's not use this report. It's going to confuse us. Let's find another report which is actually from the faults table.

So if we go back to faults and we can look at maybe actions in parent and child tickets, or we could look at active tickets. So let's say active tickets could this want to go? So I can add this report to my library. I can copy the title. I can go into active tickets, click reports in the top left, and we can see yes, this is from fault. Fantastic. How are they getting some of these details, okay?

So the first thing you're going to notice is that we need something for customer name. Well, that's actually an area desk. So we're going to go ahead and we're going to steal an area desk, okay? And you can see here that the faults table here is left joining area on a area in equals e area, a area. So what we do, we go ahead and steal that. Don't get too caught up on the join types for now. I'll link something below that explains them if you do run into it. There's nothing about who it's assigned to, so we can't do agent just yet. But what we can see is, ah, we have ticket type here. So we have rt desk as ticket type. Brilliant. So we can add in here rt desk as ticket type. Fantastic.

However, how do we know what that joins on? Well, here we have request type new, which is actually being joined on request type. So again, we can steal that bit of SQL, go back in here, and we can again left join on request type. Left join request type on rt ID equals request type new. Brilliant. So all we've really got to do now is find the agent cost and the time taken, okay?

So again, jumping back into that online repository. So I've just found another report here. I've typed in the word agent, and I found one called Engineers Tickets. So we're like great, it has the ticket ID, the agent, and some other bits and pieces. So we can go ahead and add that to our reports as well. Again, grab the title, Engineers Tickets, click reports. Hopefully we're joining on faults. Ah, we're joining on actions and also they're doing this right up here, which isn't a huge problem.

However, what we do know is we've got who. So what we can do is we can type in who up here as, and what we're seeing here is that who in select you name from you name. Now that's going to really confuse you because that's a subquery within that where statement, and there's also no joins on this because who actually appears on actions and not on tickets. So we're going to go back a step and go, ah, we can't use that yet because we don't know who that is. We, it's not following our primary sort of faults right, our primary table. Let's jump back into reports again, super quick.

And I'm just going to pick on a report that I know I have in my repository. And then we have one here called Agent. So this is an open Ticket Master table. And if I look on here, what I can find is that ah, we have from faults in here, which is great, and we have up here you name as agent. So we can do you name as agent, okay? And then if we go down, you'll see that you name is joined on assigned to in. And we established on the ticket earlier that assigned to in—remember this is wrong for later—is who the ticket is assigned to. So we can go ahead and grab that. Fantastic. Left join you name. So we've got an our workout is time taken and agent costs, okay?

So again, you might want to go to the online repository and type in the word time taken. And you can do actions by technician. And go, um, oh yep, time taken here. Add this report to my library. And here you'll see that we are joining actions on faults on this. And what you can actually do is you can take this and rather than left joining on fault, we can left join on action. And what I like to do just to keep it all clean is what I actually do, actually that's fine for me. I like to have the table we are joining on to be the first row or the first join part, and then on the second part I like to do it on the faults table. I say that however, I've not fully done that above, ignore me. Doesn't matter which way around it is, as far as I know.

So we'll just do that. That's how good I am at SQL. I'm not a SQL professional, just to set the scene right now. So what we've got now is we have the ticket ID, the customer, the agent, who it is. We don't have cost yet or time taken, but we also do have ticket type. Well, luckily for us, we have in here time taken. So we can again grab this little bit of information here and drop that into our build here. And the last thing we're trying to find is the agent cost, okay?

So so far we have these tables. We have area. We have you name. We have request type. And actions. Well, what I'm just going to do out of curiosity, and I'm completely screwing up my build here in terms of messiness, just going to select everything from the table you name. Because what we know is that you name, or the table you name, joins on the assign to in ID, and there might be data in this you name table such as the agent cost.

So if we select from you name and then go into that table, what we should be able to find in here, hopefully if we're lucky, is the cost of Connor Fagan. So if I type in the word cost, and again the schema will help you with this to be clear, you'll see there's a column called you cost price. And for me, because I'm the second one down, it says 20. However, for Jacob at the bottom, it says the cost price is zero.

Well, what I may have done earlier is not save that to his account. So if I go back to Jacob and put his cost per hour at 20 and save it, and then check that report again, what we should hopefully see is that Jacob Newman now has a cost of 20 at the bottom, which he does. Fantastic. And that completes our report.

However, something that we need to factor in is that you cost is just going to give us the 20 value, which is wrong. So what we need to do is we need to times the you cost by the time taken. So you cost times time taken. However, that's not how SQL works. What we need to do is do a sum, you cost time taken as a cost. And I think this should probably work.

So with any SQL, to start with, a select statement. Not strictly true. You can start with other things, but let's just. Most will start with a select statement. You can start with some other mad things, but I'm not that clever. And what we need to do is make sure that these are all comma at the end, apart from the last one. And we need to make sure that all of our joins look correct. Now this doesn't really matter too much. Again, I will link below what the different join types mean. But when I press test, what you're going to see is a few things appear, okay?

So you can see that the failed with the ambiguous name fault ID. Now the reason it's done that is because the fault ID appears in both the faults and the action tables. So whenever you reference fault ID, unfortunately you've got to start with the table name. Or you can do fault ID as like F, for instance, and then you could do F dot, which then fixes that. I think I just do this. It's way easier. Faults dot fault ID. Cool. And then it says an invalid column name you cost. So it needs to be you cost price times taken.

For the astute among you, you would have already noticed that I can do this. I can add in my select statement at the top, and I can make sure that I append this with the ticket ID faults dot fault ID and press test. Incorrect syntax near you name name. Again, there's no comma at the end. Add in the comma, and it should work. Then you get another error message which says the column fault ID is not, is invalid because it's not containing an aggregate function. And what that means is there could be multiple of the same thing. We need to be grouping them together if we can match them. So we can do is we can add in the bottom group by fault ID. It will then throw some more errors at you. So it's going to tell me area desk. Oh sorry, needs to be faults dot fault ID.

It's going to go, hang on a minute, area desk is also not included in the group by statement, okay? Fine. We need to group by customer as well. Oh, and you name, because that's the agent name. Fine. And time taken. And what you basically do, copy and paste until this stops erroring. Well, that's how I do it anyway.

The ticket type needs to be grouped, and then we get a test successful. What we end up with is all of these tickets with the customer ID, the agent, and the cost of the agent, how much time they took, and the ticket time. So let's just go back to our ticket. I'm just going to add a filter for now and just do ticket ID is equal to 140. Now don't stop watching now because if you do, this report is wrong, and I will explain why in two seconds time.

So on the surface, you know that I've set my rate as 20 because I forgot...

Connor: to save it if I go back into my agent and say my hourly burden rate is actually 50 and save it and jump back into that report what you'll notice is that it now says I am £225 is my agent burden rate for this ticket. Cool and you can add filters in here so stuff like you know time taken is greater than zero to make it a little bit tidier, but that's looking to be okay. Right I can go back to that ticket I can find 1,400 I can add another comment to the ticket so another 30 minutes of remote support and I can save it.

And then we go back to the ticket and then we see that my agent cost is £50 however you'll notice the time taken is currently wrong so on the ticket you'll see that I've added two time entries here and these should add up to be an hour. Well the reason that's happening is because what we've done or haven't done is told it to sum the time taken we basically just pulled it once from the ticket or from one of the actions. So need to do is remove this round for now and just replace it with a sum and view it. Oh let me just do that, sum it, and that should now show us an hour.

Fantastic so we're summing the agent costs we're summing the time taken we can show anything where the time taken is you know greater than zero and away we go. Fantastic okay so we're now tracking the burden rate of our agent and how much it cost us to do this ticket. The problem is though the following if I just turn myself into Jacob very quickly and I impersonate Jacob and I go into that ticket so 1,400 and if I add a comment to this ticket and I do another 30 minutes as Jacob so hi I'm Jacob and I save it and then go back to my report again and look at it.

What you'll now see is that Connor Fagan has logged one and a half hours on the ticket and the agent cost is £75. Now we come across this problem a lot in reports that people either make themselves or they get from the online repository because they were built for a particular use case. So this is the point I'm trying to make in this video is that you have to really audit your reports when you're making them because what can be seemingly valid data and correct is actually wrong.

Now let's go back a few steps and this is kind of the point for is to work through this is why is it doing that okay because we can clearly see that both Connor and Jacob are working on this incident ticket but for some reason Jacob's nowhere to be seen in terms of the burden rate so as far as I'm convinced Connor is doing all the work. Well the reason that's happened is because at the very beginning we joined the ticket on the agent. So here we're joining the agent to the ticket. However the ticket or the faults table doesn't store any data about the actions so the actions being down here is where all those time entries are kept.

I can show you this because if I just copy that a minute and select everything from actions where fault ID plus 1,400 what you're seeing here is that the time taken is logged against each agent so Jacob and Connor and Connor Fagan and all the time is being tracked here and it's here where we need to be pulling the time to work out a how much time is done per agent but also b what the burden rate is for the agents on that ticket. We come across this all the time.

So what we need to do is we don't want to join on the assigned to on the faults table. What we need to do is join on who the problem is who on an action is the name and the name up here is you name. So what we need to do is draw new name on who. So just to reiterate before we were joining we were pulling the agent from the ticket and doing all the calculations on that that is incorrect because we need to be pulling the agent from the actions and it's the actions that dictate who was doing it when they was doing it what they did how long they did not the ticket.

So now when I go back to that report I have some invalid syntax somewhere. Invalid syntax means I am missing a select at the top like this, and it'll say invalid column name who. Now the reason it's saying that I believe is because you name is joined before actions so it works kind of in a list as far as I understand so we need to put it underneath the action join. I think yeah there we go. So again if you ever get in that message and you think you're joined on the right table just make sure the sequence of joins are correct so I put it underneath actions so actions and then you name and then who and then what you should end up with is a report like this.

Again as before we can do ticket type is equal to 1,400 and now what you can see is that Connor has spent an hour on the ticket and Jacob has spent 30 minutes on the ticket. Therefore if I was to group by this ticket ID and group for a total you will see that the burden rate of this ticket was £60.

And that is a 36 minute video or however long this gets edited down to. It's quite a heavy one but I've wanted to really try and take you through the steps of this report so you understand how you might approach finding some of the data. To be clear what I'm going to be linking to in the description below is HaloPSA.com/sqlschema and this will highlight some of the things that I've talked about today.

So you'll see that when I'm looking at faults and I want to join on the customer name you will see when I hover over area in it says it's very small on the screen I appreciate that but it says client ID and if you hover over the line you see the area in joins on a area on the area table. So that's how you can leverage this schema. I appreciate a lot of you won't be SQL professionals by no stretch not saying I am, but this will hopefully start to give you a bit of understanding of how you can start writing these reports.

I appreciate today was a long one I really wanted to try and get this out for you to try and help you start building some of these data sets yourselves. I will put a link below to this report SQL in case you're interested it is a very very very simple report I appreciate that however it could be a great foundation for some of you on starting out on your journey. We've got a bunch of reports we leverage all the time for stuff like this using some of this data set in there.

So again have a play with it have some fun get stuck in. The read if you get super stuck just hit us up below I'll try and respond whoever YouTube doesn't really love me posting any SQL in the comments so you might want to find us in the Discord channels found below. But as always I've been Connor Fagan I hope you find some value in this video it is quite late for me to play some video games now but I will speak to you all soon take care and goodbye.