Halo Implementation Series – Adding Custom SQL to Invoice Template

hi everyone and welcome to another installment in our halo implementation series uh with me today again I have my absolute favorite person at at Halo Mr Morgan aspel um and it's good to see you again Morgan um and on today's video what we're going to do is we're going to talk about um adding custom SQL reports uh to the invoice template so you know you may want send your customer a report uh with the invoice to say you know what's been done against that ticket and and list all of those time entries in that kind of thing so um so that's what we're going to talk about today really good to see you Morgan um and as I always say on on these videos you are Mr Halo and I really appreciate that you've uh you know given up your time again to to join me on these so thanks for thanks for being here and uh hope hope all is well with you hello again Chris hello everyone um yeah always a pleasure to be here cool yeah so this one's uh a little bit different I suppose there there are some um I I don't really know how to describe it we so far um for pretty much all of these sessions we've been following a very standard uh implementation process making sure that we're looking at uh all of the fundamentals this one is not so much a fundamental but nonetheless a very important part of the system and quite a cool feature uh it touches on a couple of uh topics so we'll briefly take a look at the reporting capabilities in Halo uh we'll have a look at the invoice PDF and then we'll see how we can tie the two together by returning reports onto invoices themselves so um the reporting I'll start off by saying the reporting capabilities in Halo are uh practically unlimited and and what I mean by that really is uh Halo sits on top of a SQL database uh and the reporting Suite in Halo PSA is effectively a readon window directly into that database uh so in terms of what can be reported uh if it's in the database we'll be able to report on it I like to say that the reporting capabilities in Halo are limited only by one's knowledge of SQL uh which you know is the um is the sort of the barrier to overcome but I will say that uh you know Halo's support and Consulting teams and people like Chris can certainly help you guys out with understanding the schema and building those reports out for you so let's hop into the reporting week now we have about 400 reports uh Wow way more now we've got about 700 reports uh already in the system VI or already in our online repository and so we can get there from the reporting Suite wherever it is in the reporting Suite where down here at the bottom we have this online repository so um the chances are if you're looking for a report someone else has asked for it or something very similar at which point you'll be able to come in here find that report find the report that you're actually interested in making use of and then there's this button at the top here to uh add this report to my own private Library um now I've actually built a a very quick thepoke report out just prior to this session just to demonstrate this specific use case we have here of adding uh reports to invoices uh and just to briefly sort of highlight the the process of creating reports in Halo um if we edit our report on the left hand side we have some tabs of information here the details is where we can specify the name of the report the group that it's in our data source is where we're actually uh adding the the report information in now uh I've written out some custom SQL here but we actually see this data source option at the top uh where we can make use of a series of builtin uh sort of high level Master reports which we can then filter and sort and Order and group by uh without the the need to uh know any SQL or we've even got this custom query Builder where we can actually start uh building out the fields and columns that we actually want to make use of uh and also our conditions as well so you don't need a knowledge of SQL to be able to create reports in Halo uh but but it certainly helps and and actually and that's true because I mean I I don't have a huge amount of SQL server or SQL experience myself and I I've kind of figured out a lot of a lot of where this stuff how this works and I think you know as long as you have a copy of the the database schema which um I'll actually put a link to that in the in the description um you know and and as long as you have that then writing these SQL queries is fairly straightforward um and what I tend to find as well is I often look at um you know look at the stuff that's there already um if if I want to write my own and then I it it kind of makes a lot of sense when I um when I look at what's there already so yeah it's um it's not too difficult to do yeah yeah for sure and cool so um yeah this is a basic squl report that I've written the important part of this report is highlighted by this dollar invoice ID variable so we can include certain variables in the SQL of our reports which will then be interpreted elsewhere in the system another great example of this is using the dollar client ID variable in these reports and what that can do or what that will do is when a cut if you present that report uh on the portal when a customer logs in to the self-service portal they will see a report based on their own data so you can build one report out or perhaps One dashboard out uh publish that on the portal globally and then based on whoever's logging into that self-service portal will see the information that is relevant to their own customer record of course uh here we we're using the invoice ID variable such that uh we only return the time entries on tickets that uh have been worked on uh in the time period that the invoice itself was created so uh once we've got our our report that we've set up we can then add that to our invoice PDF via configuration billing General where we've got a PDF template section here now in here we have this report section and we can see I've already added in the report uh that I want to be returned on my invoice PDF pages so when you add the that report in it will return you a data variable so for example if I were to add these two reports in they immediately give me back the variable that I would need to include in the HTML of our invoice template which is set up on the pages tab over here where I've have just chucked that in here again um not going to be going into much detail on the the HTML side maybe we'll come back to that at a later date but for now the important point to note is we can add reports into the configuration of our PDF templates by including the associated report data variable yep and then the outcome is not that at all fantastic what's going on there 186 so let's go through that again let's go to configuration billing General let's go to our invoice PDF settings let's go to our invoice with time breakdown I deleted the wrong report what I was meant to keep in here was the consumed time for period it should be and then if we go to uh our invoice for labor what we can see down here and actually what I'll do is I'll generate the whole PDF what we'll see is on the first page we have our standard in invoice details excuse me um where here we would have all of the sort of recurring Services all of the hardware that's been procured all the sales orders all of that kind of stuff um and then on this subsequent page we have the report that has actually been uh added into the PDF configuration so you can imagine um configuring your invoices similar to similar to this where um we are grouping our invoice lines as much as possible which means that uh you know instead of if I have 50 actions across 10 tickets uh I can group that all onto uh one line and that would just be the total time for uh that charge rate across all of the tickets and so that would return on here just the one single line and then below we can have a report that gives a breakdown of all of the notes that were added um another handy example that I've seen set up quite uh commonly is returning down here the time that hasn't actually been buil for the time that has been been consumed by the contract because then that allows you to justify uh the the monthly service charge that the customer is paying yeah absolutely and I think this is this is great because you know very often customers want to know this information right so they want to know you know yeah ju justify why you you know why you're charging us x00 or X th000 dollar pounds whatever it is per month um you know we want to know everything you've done for us how many tickets you've done etc etc so you might want to put that on there and then as you say you could also even even go you know here's a bunch of stuff that we've done that's that's um you know freebies this is where we haven't actually charged you for any of this work we putting that against um against your invoice every month so um and I remember back in the day of of having my MSP business I had a customer that insisted that this was on every single invoice before they paid um most people don't care but you know one particular customer was like Yep this has to be on every single invoice and we want to know exactly what you've done what's free what's included all of that kind of stuff um before we before we pay your bills so um the downside of that is he was a very picky customer as well so literally picked at every single thing you know did you really spend two hours on that did you really spend 15 minutes on that um but at least we got our invoices paid every month so um so yeah very very useful way of being able to do this on um on on the invoice cool yeah yeah I mean in ter terms of the that sort of anecdote about the customer I guess there's always one isn't there and and also in terms of the reports that you can return on here this is a very basic example but you could get quite creative with this and uh return SLA stats for the tickets that have been invoiced for the month you know we've we've hit 90% SLA on all of these tickets that we've build you for um a breakdown of of uh category Trends anything like that really you can um incorporate that into the SQL reports and uh then yeah return that back onto the onto the page so yeah yeah absolutely no I definitely think you know as you say the the possibilities are endless with with the reporting in in Halo um and just with a few you know writing a few SQL scripts and getting the stuff onto the invoice there's so much stuff you can do um around here so it's almost like you know sending that customer a report of everything you've done for them on a um on a monthly basis and just including that as part part of the the invoice rather than sending them a separate report yeah yeah yeah absolutely now that's really cool and what I'll do is um the that SQL script that you you wrote um I'll actually get you to send me that and I will put that into the um the description below as well just if somebody wants to copy that script directly um and and do the same kind of thing um and then equally I'll also put a a in the description below of where to get hold of a document that kind of goes through the entire schema so that if you guys want to start to to build your own reports um and looking at the schema then um then you'll be able to do that fairly easily absolutely yeah absolutely and thank you thanks for that Morgan I know this has come up a few times um and I know there's going to be a lot of people watching this that are going to um you know really really love how we do this so thank you once again really really appreciate it thank you for your time and thank you always as always to Halo for allowing us to do these and allowing us to use your time so really appreciate it and thank you very much and we will see you on the next video thanks again yeah always a pleasure thanks everyone for tuning in and see you on the next one see you on the next one cheers