Halo Implementation – Reporting and Dashboards

he everyone and welcome to another installment in the Halo implementation series with me today I have uh my my friend Mr Halo uh Morgan aspel um and today we're going to go through talking about uh creating reports charts dashboards all that kind of stuff um and I know you never get sick of me saying uh Mr Halo so how are you doing sir Mr Halo yeah yeah doing very well good to be back Chris good good good yeah good to have you back um and yeah as I mentioned a few minutes ago we're going to be talking about you know creating reports dashboards um you know charts going through you know the whole kind of reporting side of of Halo because I think that's an important thing that I hear a lot in in implementation about okay well I got all this data in the system how do I get it out and how do I get meaningful information out of the system now so um yeah so let's talk about reports yeah yeah for sure I so pretty much so far we've been talking about building Halo configuring Halo uh and getting the data in Halo and getting more over getting clean data into the system um now we'll start taking a look at how we can extract bits of information uh out of the platform um via reporting and dashboards so I'll start off by mentioning that Halo sits on top of a SQL database so it's a SQL database that we're using behind the scenes uh and our reporting capabilities or our sort of reporting functionality is effectively a window directly into that database so I always say that the reporting capabilities in Halo are limited only by one's knowledge of SQL um which you know is its own hurdle uh but the the information is there to be obtained so um we'll start off by hopping into the the reporting module now in here uh on the left hand side we have these different report groups so you know these are the well it's exactly as it says on the tin these are groups for our reports so we can create folders with the different types of reports that we find most useful uh what we can also do is right click on this list to pin uh an a report group to the top of the list so you can put your your favorites at the top the most important reports at the top of that list pinned at the top and then all of the other uh groups below now we in a group we can see we have these reports in here uh and let's take a look at the projects reports so as I mentioned uh the reporting capabilities in Halo uh really do sit uh on top of a query that's running on that data base so what does that query look like how do we access that query how do we amend it and so on we do that by clicking edit against the report here and then we've got a few options that are presented on the left hand side of course we can give our report a name add a description to provide some further context uh put our report into a particular group and then the data source here this is this is where we're actually compiling that report um and sort of collating all the information that we do want to report on now we can see here uh we've got a a query already sort of baked in to to this report and above that we have this data source option now there's a couple of different uh configuration options in that data source so of course we can use a custom SQL query uh which point you know if you are Savvy with SQL and you have an understanding of the schema which we will um will'll put in the in the comments below uh then you know all all systems go you can create whatever reports you need however there are some other options in here to make the the report creation a little bit more user friendly so firstly we have uh these options at the top these options at the top are referred to as data sources and data sources themselves are actually prebaked SQL queries uh for you to use as soon as you get Halo PSA so perhaps I just want to H get all of my Open tickets and you'll see if I'm picking a data source uh I don't need to enter my my SQL query into the window below because we're going to be inheriting the SQL query that sits behind this data sour Source yeah now um the option below that is this Fields option so of course the the data sources are intended to be very broad queries uh capturing as many columns as are needed with as few sort of wear Clauses or conditions in the report as possible and the reason for that is because you can actually specify the The Columns that you want in your report and the conditions that want to apply to your report without needing to to to write any sequel so firstly in terms of specifying The Columns of information that are presented uh on the report that is subsequently generated to do that we go to the Field section click add and we add the fields that we want to return in this report and these are the fields that we obtain from our data source and we'll talk about where the data source are configured in just a moment so perhaps I want ticket ID subject agent and status now uh just like with all the other field list type screens that we see throughout Halo PSA uh the reordering of those columns is a case of dragging and dropping um we can also by clicking the pencil against one of our Fields apply groupings in here and sorting so again uh there's no direct need for writing the SQL uh to create the report you can actually set your grouping set your sorting set your Fields uh directly from the UI as such now to actually have a look at the report we need to go to the preview report section where I can click load report and there's my report uh now in terms of adding our sort of conditionality adding our wear Clauses uh we can do that via the filters section so our filters are our wear Clauses so perhaps uh I only want to look at the tickets in this list that are you know awaiting approval let's say um so for that I would add to my filters and I would say where status includes awaiting approval refresh that and now the report is as expected only returning those tickets that are with that status waiting approved we can apply multiple different conditions in here we can also see that if we are including a date field somewhere in the report we can filter by that as well so I don't necessarily need to show that column uh in here but as long as it is in the report behind the scenes we can then filter on that as well where we could say I only want to have a look at the tickets that were log last month are awaiting approval of which there are none we can get rid of this filter we can see there was one ticket that was logged last month last month yeah and I know you said that this was your your SQL and it's um you know your it's it's it's only by your um knowledge and Imagination of what you can do with SQL but you know with these data sources and I say this to a lot of customers on calls it's actually very easy and very quick to create a report in Halo without a amount of SQL experience because of the data sources you know they they're there they pre-baked in as you saying these are the data sources that most people would be um would be looking at and then it's just a matter of deciding which Fields you want to show and then also doing your filters so you know don't get overwhelmed too much by the fact that it's SQL and you know don't have SQL experience it's it's actually relatively straightforward to um you know to to generate these reports um and that's what's so nice about these these kind of pre-baked data sources that you guys have have on the system already yes yeah absolutely and you can also uh I mean this is something that I've just fought for the first time now but you could actually reverse engineer the data sources as well because we see down on the left hand side here we have this data sources section so um it's not a it's not a black box so to speak it's not that you don't know what the SQL is that Associated to a data source to see the seq that is associated to a data source we click on our data sources section uh and then in here if I want to look at all Open tickets again that's going to give me the full report and then if I click edit up top here there's a SQL query section yeah so we can actually uh see the query that is being used uh for our respective data source so you know if someone does need to get an understanding of uh what the the name of ticket statuses we can now see that that is T status desk um and we can see that we need to join uh T status on faults uh via this common column here yeah yeah absolutely and I think and and I think that's the thing is is you know the fact that those data sources are actually there and editable um also I I think then it does help if you don't have a huge amount of SQL experience by being able to have those data sources to hand and being able to look at them in in Halo you can then just um you know it for for a lot of people it's easier to kind of see what's already there rather than think about how do I actually do this and join these tables together and all that kind of stuff so um you know so it's and and I know a lot of people get overwhelmed by oh oh my goodness it's SQL and how am I going to you know I don't I don't know SQL I can't do reporting it's it's not that hard and and you know like I said a lot of these data sources are um are there and you know there there's a bunch of them available either through a consultant or Halo or you know whatever I'm sure we can we can build some data sources for um you know for common things that people use yeah absolutely yeah and it's um you know it's a point of if the community uh cumulatively need a data source then we at Halo will absolutely create one um it's actually something that I'm trying to uh improve one of the last projects that I've got for this year is to make a really good set of reports for for new trials so um but yeah you know any data sources that people uh are looking for that think the community could benefit from then reach out to Chris reach out to me uh and you know we at Halo will be able to or or Chris or another consultant will be able to to help set that up for you yeah absolutely and I think so yeah so it's you know I I was very overwhelmed by when I first saw it and I was like oh I don't know anything about SQL and this is going to be hard but actually once I started using it it's and it's a lot easier than other PSA tools I've seen um you know that have kind of a crystal report like thing that's really really hard to use so yeah yeah and you know I would say uh I absolutely agree with you Chris I mean it was the same for me it was very daunting um when when I first started making reports I didn't know much squel whatsoever but really you don't need much more than an understanding of joining tables um you know we're not doing anything too complicated in these reports we're just getting columns out of the database um and of course it's a relational database we're just joining different tables together so yeah another cool feature that uh is not you know uh immediately understood or you know immediately presented but it's very handy is of course you can edit the you can add the grouping the Sorting from the Field section you can actually do that directly from the report as well once it's been rendered um so all I need to do is right click on one of these columns and I have a couple of options that have been made available to me namely I can very easily group or ungroup by a particular column um at which point I can you know expand or collapse said column um I can then you know show totals for our columns um based on the based on the grouping so yeah another another handy uh little Easter egg I suppose the ability to right click on these these column headers um to amend how the how the report looks and feels yeah absolutely cool yeah that's useful thing I I use that all the time actually I use the grouping um to to further group the stuff I actually used to do it through the um you know through the SQL but then I figured that out and and I I was able to kind of group it through here so yeah yeah awesome um so of course you know this is all good and well returning a a table of data um but we all have a good chart right so uh of course we can create different types of graphs graphs and charts for these reports we could do that from the chart setup over here at which point we specify our chart so let's have a look let's see maybe we want to do a count of projects by username let's just do something simple like that so we go to our chart um here I can specify the chart types there's a few different options you know pie or donut chart to multiple series or funnel or um yeah anything like that so we might want to do a bar chart in this case at which point I've got some options that have been presented to me so I think uh along the x-axis we're going to want you username and the Y AIS we're going to want project ID um and let's just preview that real quick cool so um we can see that actually we're getting the ticket ID for the user uh so at that point what I actually want to do is I want to count count my values up here um so of course you can count or you can sum the y-axis values uh you can add titles just like so um add a title as well and even add your own uh hex uh codes to change the coloring of the the different elements in your chart so yeah creating a chart is as simple as that super simple yeah cool um now personally and we'll we'll get onto this in a little bit more detail shortly but personally I uh would I I would want my data presented on a dashboard and I would want to view it at at my heart's desire um but you might also want to send report metrics uh to an individual or a set of individuals on a scheduled basis perhaps uh the management team need to get last week's agent utilization reports uh or the accounting team uh need to get a a quarterly report uh looking at the profitability for for the quarter that's just passed something like that so we can set that up in our scheduled emails section where in here we specify uh the individuals that will be receiving the email um you know subject and body the schedule uh for which we want to send this report so I might want to send it you know every Monday as of this Monday we can see in here again um this screen is very reminiscent of the recurring invoice schedule where we've got the calendar at the top with the blue boxes denoting when this event will trigger namely when this report will be emailed and then of course up here we've got some different formats for the um for the report do we want to send it in PDF do we want to send it CSV um do we want to send it even if it contains no data so you know for example um you might have uh a P1 ticket list that's sent out every morning but you only want that to be sent out if there's an open P1 ticket so we can specify whether or not we're sending the report uh if it has any data um of course you know and this is quite an important Point actually that I didn't think about so um months ago now uh when right when we started this implementation series one of the first things that we discussed were agent permissions and we spoke about how we can uh set the level of access that an agent has to the platform very important Point here uh if an agent does not have access to invoices let's say uh but does have access to the reporting Suite then in theory they could get that invoice data out of Halo and so reporting access access to the reports module as a whole is a separate permission but by proxy having access to the reporting Suite means you have access to the database which means you have access to potentially anything if you you know someone uh SQL Savvy enough gets their hands on it um so you can combat that in in a couple of ways so you can restrict access to reports um if you're restricting access to reports then you specify the agents that have access to that report uh and whether or not that is in a readon state or editable um that is quite a labor intensive uh task so you can also at report group level apply Access Control in here where you can specify the the agent the role the team and the level of access they have to the reports in this group uh so something to watch out for there um and then finally you can uh if you so choose publish your report data to an API Endo so this uh a long time ago years ago now uh before we had the the level of uh reporting capabilities in Halo we used to use a tool called clipfolio and um which is a sort of separate dashboard uh tool um and to feed clipfolio data from Halo we had to publish these reports and basically what that does is just return the report data in Json format just like this um obviously at that point you can do whatever you want with this endpoint you can plug it into you know uh any any tool that you're using to to colate your report data um just bear in mind that I don't think this sits behind any level of authentication of course this is not going to be easy to crack but if someone does get their hands on it they have access to the data so yeah another point to watch out for yeah and that tool still exists by the way I was actually looking at it the other day the yeah yeah yeah um I'm I mean when we when we put dashboards into Halo I I was very happy because um yeah I when I was quite new I just started to figure out Halo and then it's like what's clipfolio it's like a whole Behemoth in and of itself to figure out how that works um yeah it's kind of a PBI type type thing that data sources into yeah it's it's not bad actually but yeah I mean it's good it's powerful for sure it's very powerful um however you don't need clipfolio anymore you can you still use it uh should you wish um but Halo now has and has done for a while um has native dashboard capabilities so of course the idea is you build these reports out um add them to your respective groups and then you're probably going to want to return multiple report metrics on a single page either for you to track internally or to present to your customers via the self-service portal uh and to do that we would create a dashboard so um let's take a quick look at the the dashboard configuration so for that we go to configuration reporting and then in here we have a dashboards option now um the the dashboards have a few different types so it can be an external link it can be an iframe so it could be like a a published powerbi report or a published clipfolio dashboard um that you know at that point you would plug the URL in here or the dashboard type can be inapp now if the dashboard type is in app we have this layout tab so this layout tab is where we add our different reports uh onto our dashboard so to do that what we want to do is we want to add a number of widgets to the dashboard give our widget a name let's say project by user um then we have this type so there's a few different options uh for our type so we can have report data which just Returns the table uh based on the report that has been rendered report chart that's only applicable if you've configured a chart against your report but if you have like we just did with the uh the bar chart you you can then return that chart by a widget um we can also do a report counter which is basically um uh an aggregate function against something in our report so we can count records sum a column or average a column if we're summing or averaging a column we then have to specify the column name as it is in the report and then you know Halo we'll do the rest uh and we've got a few other options in here not directly applicable to projects but very useful to have on a dash dashboard things like uh activity feed feature menu uh ticket ticket lists right um and we end up with something that looks like this where we've got our sort of feature menu at the top we've got some um reports below we've got some news articles down here we've got a ticket list here um and we've got a the report Table right at the bottom here and then the the recent activity right at the the very bottom um and so you can use various different types of widgets to um to compose your your dashboard so it's not just all raw report data uh there a few cool little ways that you can sort of make these dashboards more generalized now this screen might look quite familiar um it's this screen that we have here and again so this uh this is the motivation for including some of those more miscellaneous widget types the feature menu the ticket counter the activity feed and the reason for that is because you can return these dashboards on the home screen of Halo there's two you can set a global default and you can set an agent by agent default as well so the global default is in uh config reporting general just here is home sheet sorry dashboard to show on the home screen and then on an agent by agent basis via the agent preferences our home screen option can be overridden there and of course this is very important because you the uh the chances are and this is my um this is part of the project that I mentioned um the idea is that you're GNA have a a global project management dashboard a global sales dashboard a global profitability dashboard and a global service desk kpi dashboard uh and then based on the uh the agents logging into Halo you know if we're talking about the um the COO logging into Halo they might want to see the um um service desk dashboard or the projects dashboard but your sales executive might want to see the global sales dashboard so again depending on the individual logging into Halo they have the dashboard on their home screen returning the data that they need to see yeah and it's it's important to say at this point I mean even though you know the the engineer May or the the who you know whoever it is who's loging in the agent may have their dashboard related to them specifically there is still menu option in Halo where they can go and see all of those other dashboards if they need to as well yeah absolutely yeah so that's um there's a a module in our feature menu namely dashboards yeah where they can yeah yeah that's it and that's the thing you know and that's really nice because then you can go well the dashboard I need in you know in my day-to-day job is this particular one but actually I also do use the management dashboard but I don't use it as much so maybe I can just click in here and then um and then access that dashboard yeah absolutely yeah um couple of other points to mention um firstly you can see that straight in the trials there's 172 reports uh immediately there at your disposal the chances are if you're looking to track a particular metric someone else has already asked for that metric to be tracked and there'll be a support that is either exactly what you need or very close to what you need um you know at which point you can reach out to support and say I just need to make this minor tweak to this report can you help me out and we'll get that sorted um but these are the 172 reports that are loaded into trials uh in your local repository there's actually a much greater repository um that we at Halo have added to over the years and you can access that via the online repository of uh and in our online repository there's 739 reports um um so you know that's a um that's a late a late night for for those individuals that are looking to find their reports fortunately we've got uh search capabilities so I might want to look for specific asset reports um I might want to look for you know an expire expiry alarm perhaps we've got a warranty asset warranty expiry um contract expiry that kind of stuff um the point being there's over 700 reports in our online repository uh you can find the the report that you're looking for and if it Returns the data that you need if it if it's something that you want to add to your library you can simply click this add to my library option um at which point we exit out of the online repository by clicking back into the report section and there is that report in my local repository yeah so it's really good to have those um you know those online reports at your disposal in terms of um you know I and I have a lot of people ask me about how how do we edit those reports that are in the in the online area and that's exactly as you've just done here right is is make that into a local report and then once it's here in your in your you know local reports you can then edit it you can do whatever you like with it you can see the the SQL Source you can see everything on that report um so so what I do with mine is I tend to only have reports in there that I need and then if I if I need another report I go and find it um you know on on the online repository bring that in play around to do and then um and then either use the report or discard it and um you know so so yeah it gives you gives you a huge amount of um flexibility in terms of of what you can do with the reporting yeah absolutely um one final point to mention um I'm sure we'll do another video on this maybe focusing on presenting dashboards VI the self service portal but one final thing to mention uh in here uh in this session is our composite reports again personally um I would rather return a dashboard to my customers uh and let them check in on their metrics in real time however uh if a customer is looking for a qbr and they're looking for a PDF of report data to be sent to them on a monthly quarterly you know by anually basis what we can do for that is we can use our composite report capabilities so composite reports to uh you know put it plainly um composite reports are HTML templates that have report data loaded into them so they're configured in the compite report section believe it or not where in here we have uh a few different tabs now the the tabs that we see here are quite reminiscent of our uh PDF template configuration tabs uh that's because this is fundamentally a PDF template there are a couple of uh distinctions from the standard PDF templates one of which is this reports table so of course our composite report will be uh a template composed of individual reports those individual reports are added in here and when you add your reports in here you have two variables that have been returned for you uh one variable the the data variable will return the table of information based on the report and the chart variable will return the chart if you've configured a chart uh and we add these reports into our composite report into our PDF template via the pages tab where in here we've just got some uh javascri sorry we just got some HTML that we've uh added added in um and importantly we have included the respective variables that we need that correspond to the charts slts of contents um yeah and yeah that's that's as that's as simple as that and this is similar to the way there's another video we recorded a few weeks back um which I'll link to there or there or wherever it is um I never know which way around I am when I'm looking at the camera um but we're we're link to it where we did the same thing with um uh you know invoices where we kind of attached a report onto the back of it it's the same kind of idea as as these composite reports where you can take that PDF invoice and attach the the report directly to that so if you haven't looked at that go and have a look at that video this will kind of make a lot more sense then on what we're trying to do here as well yeah absolutely yeah and I think as um as an introduction to reports I think that's more or less all you guys will need to to get going so um you've got the online repository of over 700 reports you can add those reports to your local library you can create your own custom report groups uh you can restrict access to reports based on groups we've got data sources baked in that you can use to uh leverage sort of pre-built SQL queries um you've got the the ability uh to sort and group and filter and total um within the report itself we can create charts for our reports send them out on a scheduled basis and we can compose uh a series of different reports in either a dashboard that we can return uh in Halo or the self service portal we'll probably look at that next um or we can return those reports onto a PDF via our composite report capabilities yeah and one of the things just to mention in here you can see the the icons at the top you know um we've got the import from Json and the export to Json as well so you could actually export this um you you know this whole report kind of as a Json and and I think you can do that with any of the reports um and and then and then upload that into another version of Halo so I've done that before with um you know know with customers where I've created a a report for them um exported it down to Json and they've just gone in and imported the report um and and it's worked so um so there is that option where if you wanted to you know if you were part of a peer group or some kind of community and you wanted to share these reports amongst each other that would be a really good way to do it it's kind of you know just use that export and and import functions so yeah yeah no that's great I think that's an an excellent overview of of reports um if anyone wants you know has any further questions in this or needs any help putting together these reports you know I can help you with this Halo can help you um you know we can help you kind of get these reports written um you know and and create dashboards and all that kind of thing what we're going to do on the next video as Morgan s suggested as well we'll talk about how do we get these in front of our customers right this these are great reports for us as internal um uh you know as an MSP but how do we actually get our customers to see this information so we'll show you how to publish this onto the the client portal um but yeah I mean you know really appreciated Morgan um as always and appreciate that you know Halo's allowing us to do these videos um and thank you so much for your time as always um and uh really appreciate it so thank you very much and we'll uh we'll see you all on the next video yeah always a pleasure as long as you keep calling me Mr Halo I'll keep turning up so I will keep doing I'm I'm literally did you yeah no no uh you know seriously as long as a community are getting value from these which I believe they are um we will continue to do them and there there's no end in sight Halo is a massive platform there's so many things that we we haven't discussed um so yeah there's there's no stopping this train excellent great thank you so much again and uh we'll see you all on the next one cheers guys see you soon by bye bye