[53] – Reporting in Autotask

Welcome to PSA Impact, your podcast for all things PSA, RMM, and MSP, with your hosts, Rayanne Buccianico and Chris Tim. Learn how to get the most out of your PSA tool and manage your business by maximizing profitability and increasing efficiency within your MSP business. Let's listen in. Hi, everybody, and welcome to another episode of PSA Impact with your hosts, myself, Chris Tim, and my co-host, Rayanne Buccianico. Hey, Rayanne.

What's up? Oh, not too much. I'm just, I'm having one of those technology weeks where I feel like I'm in another technology black hole week, but I'm certain that I'm going to climb out of it any second now. How are you? Oh, very good.

Thank you. Nice and nice and warm, but I'm having one of those weeks as well. I'm actually building a new desktop. I bought a new HP Elite Desk desktop. So, yeah, I'm spending some time building that, but otherwise, all is good.

Awesome. So, what have we got on the agenda for today, Rayanne? Well, you know, I was thinking that one of the topics we haven't talked yet was reporting. Inside Autotask, and, you know, because I'm an Autotask kind of gal, but inside Autotask, they have these stock reports, and, of course, they have the Live Reports Designer. And the Live Reports Designer seems to confuse a lot of people, and it takes a bit before you wrap your brain around the way the Live Reports Designer thinks so that you can write your own reports.

So, what are your thoughts on that? Do you use the Live Reports, and do you consult on that much? Well, Rayanne, firstly, again, you know what I'm going to say. It's my absolute favorite thing to talk about as far as Autotask is concerned, but then we know that everything about Autotask and PSA is my favorite thing, right? Of course.

So, yeah, I mean, reporting is a big one, right? As you said about Live Reports, so there is the Live Reports Designer built into Autotask. For those listeners who don't really know about it, it's a report designer that allows you to pretty much design your own reports from the ground up. So, you can pretty much design anything you want through the Live Reports Designer, but it does come with a whole bunch of canned reports and a whole bunch of kind of out-of-the-box stuff that most MSPs would use. Right.

So, you know, you can see the system reports. As a matter of fact, you can't even change the system reports. They're all locked. But you can copy them so that you can alter the reports and edit them for something that you might need specifically. Do you have any specific reports that are kind of your go-to reports?

What sort of reports are your customers looking at? So, I'm getting, you know, obviously a lot of requests for things like profitability reports, which we know there are built-in profitability reports. But, you know, again, those reports will only work if you've actually got the system set up for profitability, right? So, things like you've got your burden rates entered in properly and you've got your, you know, the cost of your services, the cost of your products, all those kinds of stuff because otherwise Autotask isn't really going to be able to work out the profitability properly on those. So, I get a lot of those or things like, you know, resource utilization reports, those sorts of things, which are all built into the system, right?

So, in those system reports that you mentioned, there is a whole long list of those already built into the system, which, like I say, you can just copy and, you know, mess about with and do whatever you want with them. Right. So, I agree about the having the costs entered into Autotask. And even if you're doing this inside ConnectWise or any of the other PSAs, you have to have your costs in there. So, let's say you have various subscription services through Pax8.

You can connect your Pax8 account to your Autotask. It's going to update your contracts with the number of seats, not only the number of seats, but also the price of that subscription and the cost of that subscription. All of these things will automatically get updated so that you can run some decent profitability reports. So, have you had any, like, odd requests for reports that maybe Autotask can get you half the way there or 90% of the way there, but can't get you all the way to the end? Yeah, absolutely.

And I think that's one of the limitations of live reports, right, is you can only report on one category at a time. So, for example, you wouldn't be able to report on, you know, employee or resource utilization as well as things like profitability within the same report because they're in two different entities on the system. So, it's going to be difficult to kind of run a report that's going to give you all that information, at least out of the box, right, at least in the live reports designer. So, there's actually a couple of ways that you can get around that problem. I actually was working with a client just the other day who asked me that, you know, he was trying to do a report that we couldn't report across entities.

And one of the things I think a lot of our listeners probably are not aware of, but there's actually a data warehouse facility within Autotask. So, essentially, what Autotask does is they provide you with a read-only version of your SQL Server database, which is updated every 24 hours. So, what you can do is it is a subscription service. So, obviously, you pay for it on a monthly basis. But essentially, it gives you access to your SQL Server tables on the back end.

And then you can use any kind of reporting tool, you know, such as Power BI or SQL Server reporting services, whatever it is, you can actually use that to report directly against that SQL Server database. Interesting. I know that a number of people out there are using the API for subscriptions like Bright Gauge, where you can build your own dashboards, you know, based on information that's inside Autotask and extract some of the data. So, is that what you're talking about? But there's a separate subscription in order to access the SQL database information?

Yeah. So, what you're talking about is something completely different. Obviously, there's the API. So, you can do reporting through the API user. So, you can use tools like Bright Gauge and, you know, any other kind of reporting tool that integrates with Autotask.

So, you can create those dashboards and all of that kind of stuff. You can also use things like Power BI, which is the Microsoft Business Intelligence platform. So, you can use that to build reports using the Autotask API. So, what I'm talking about is something completely different outside of that. So, there's actually a subscription in Autotask.

It's known as Data Warehouse. And all it is, is they literally give you a copy of your SQL Server database. So, in the same way as if you then opened up SQL Server Management Services or however you're going to look at that table structure in SQL Server, that's what you're going to see. You're just going to see a whole bunch of SQL Server tables. And then from there, you have the ability to then report against that.

And then it opens up an entire world of reporting options because now you're reporting against the SQL Server database rather than reporting against either, you know, something that Autotask is locking down in some way like the live reports or, you know, having some kind of limitations on the built-in dashboards, all of those kinds of things. This gives you the ability to report directly from those SQL Server tables. And it's a really, really good way to be able to do it because what you can then do is because it's just SQL Server, you can then kind of bring in cross entities. So if you wanted to, like I said, if you wanted to kind of have resource utilization and profitability and all kinds of things in the same report, you can absolutely do that. Would you have to export that information on a regular basis in order for it to update or would it automatically update?

Can you connect it directly to your SQL Server to keep it updated so that you can run reports as needed? Yeah. So the data isn't live. It's updated. Autotask pushes an update to your data warehouse server essentially every 24 hours.

So the data is 24 hours old. So, you know, if you wanted to do any kind of live live report, as in, you know, you want the data in real time here and now, then the data warehouse isn't going to be the right solution for you. But if you want to do something like, you know, weekly trends and seeing how stuff is reporting over the last six weeks or whatever the case might be, absolutely, you can just use data warehouse for that. So yes, the data is 24 hours old and it's updated every 24 hours, I should say. And that's automatic from Autotask.

So once you pay for that subscription service, it's just automatic and you get the data warehouse. That's amazing. I've been using Autotask for 13 years, and this is the first I've ever heard of data warehousing. Yeah. How long has this been an option?

Well, since the beginning of time, really. So in my times when I worked at Autotask, it was an option and I worked there, well, at least three years ago. And I was there for five years prior to that. And it was an option then. So it's been around for a long, long time.

Now it's obviously changed over the years because essentially, you know, the database is changing in the back end. So, but yeah, you have the ability to just phone up your Autotask rep, get access to the SQL Server database and then just report against it. So, and that's actually one of the really nice things about it. I know there's a few of our listeners that might be ConnectWise folks that might have, you know, an on-prem server, so they can do that kind of thing anyway, because they're going to have the SQL Server database in-house. But this is a really good option for Autotask customers that don't want to use the live reporting or can't use the live reporting for whatever limitation or don't want to use a dashboard, whatever the case might be, and then can actually use the data warehouse facility.

And then you can start to use things like Power BI. So, I actually built some reports for a client using Power BI and the data warehouse. So, it just gives you so much more flexibility. Obviously, you would need to understand how SQL Server works in order to pull all of those reports and do all the joins and all that kind of good stuff. But yeah, essentially, you know, you have the ability to do that.

Well, you know, thinking about our listeners and people that are using the various PSAs, so many times I get people ask me, what reports should I be running? On a monthly basis, what should I be looking at? They know, generically or generally, that they need to be watching their profitability, right? So, you can get the profitability by customer, you can get the profitability by contract, you know, you can get the details. Summary reports are always a challenge in live reports.

They seem to want to spit out a ton of detail at you, you know, when you might really just want to see your income for the month. Gross revenue, gross invoiced revenue by customer. Don't give me 35 pages of detail for this customer. How much did I invoice, you know, for the month? Who did I invoice?

And give me a total because I want to take my total revenue from Autotask and I want to match it up to my total revenue in QuickBooks and I want to make sure that they are in alignment here. And something very simple like that, like, hey, you know, if I've billed $200,000 out of Autotask last month, I want to be able to open up my QuickBooks and see a $200,000 worth of revenue for last month, right? So what are some of the go-to reports that maybe you recommend? Yeah, so, I mean, I'm getting that kind of question quite a lot as well on that type of report. But from a profitability point of view, I get that a lot.

People want to know how much are my contracts worth? You know, how much time am I spending against the contracts? Now, those kinds of reports actually, you don't even really need to use Live Reports Designer to get that kind of report and you don't even need to use the Data Warehouse to get that, right? Because one thing we haven't talked about is, of course, Autotask has the dashboards and widgets built in. So out of the box, by default, there's actually a finance tab, if you like, or a finance dashboard, whatever you want to call it, that gives you the ability to see all of that information just out of the box.

So just literally turning on that dashboard gives you the ability to straightaway see your profitability, which contracts are the most profitable, you know, all of that kind of information. So there's some really, really good ways of being able to pull that information out. And then, of course, you've got other tools like Bright Gauge and those kinds of things. So, you know, one of the things you'll find that Autotask obviously can't do, and this will probably be something you can talk to, is a lot more advanced financial reports. So things like EBITDA and those sorts of reports, you're not going to be able to pull out of Autotask, even if you were using the Data Warehouse or Live Reports Designer or anything like that.

But something like Bright Gauge may be able to actually help you with that, because Bright Gauge is also going to use your, you know, a feed from your accounting package too. So you may be able to kind of actually build that sort of report in something like Bright Gauge. So I'd be interested to get your take on kind of how a customer would build something like an EBITDA report in Autotask. I don't know that it's even possible to build that EBITDA report in Autotask, because you need the overhead operating expenses built into that calculation, and Autotask is not designed to handle those internal or G&A operating expenses. So the best that you could do, you can get to your gross profit margin through Autotask.

But when we're talking about things like the light bill and the phone bill and the building rents, and all of those other, even payroll I suppose is kind of covered in Autotask, but there's a lot of operating expenses, dues and subscriptions and travel and entertainment and all of those other things that never see the light of day inside Autotask. That is strictly an accounting system expense, but those are important expenses in order to get to that EBITDA. And for anybody who's not familiar with the term, EBITDA is E-B-I-T-D-A, Earnings Before Interest, Taxes, Depreciation and Amortization. And this is a number that many companies use in order to come up to a value in their business. So they're very conscious of making sure that their EBITDA numbers stay above a certain percentage of gross revenue.

Again, here's what I would probably do. I would run and get the gross profit reports or the profitability reports out of Autotask, pull the operating expenses from the accounting system, and make sure that you pull out interest, taxes, depreciation and amortization. And then you'll have your operating expenses before interest, taxes, depreciation and amortization. And that's going to help you get to your EBITDA number. And then they could probably use BrightGauge to build a dashboard or a widget such as that.

And I think there's probably other even applications for your phone or even on the web that you could tie into your QuickBooks. We use Fathom. There's another couple of apps out there. There's Monit. There's Cash Flow Tool.

There's some others that will calculate your EBITDA for you on a rolling basis, on an ongoing basis. Yeah, yeah, absolutely. Spoken like a true accountant. Oh, sorry. Yeah, I guess I kind of went off.

No, I didn't. Completely lost me with that. But I'm sure many of our listeners, you know. I think I saw that glazed look go over your eyes. Yeah, yeah, exactly.

Me yawning and putting my hand in front of my face, there was a reason for that. No, I'm just messing with you. I know. No, I mean, you know, and I think that's the thing I hear a lot is people want that kind of report out of all the tasks and it's just not possible. And I don't actually think that's possible out of any PSA tool because the PSA tool isn't capturing any of those operating costs, operating expenses that you're going to have inside an accounting tool.

So you're going to have to do two reports. You're either going to have to or you're going to have to have a dashboard of some kind that's going to link both those two data sources together and try and pull it out from there. So something like a Bright Gauge or Power BI and one of those kind of things or any of those tools that you've mentioned. And actually, you know, here's a shout out to any of our listeners. I'd be very interested actually to know how our listeners are pulling that kind of information.

You know, where are they getting that from? Is it coming predominantly from their accounting tool? Are they using something like Bright Gauge to pull some of that information out of all the tasks? Are people not even reporting out of all the tasks? Well, I mean, you know, because I hear it so many times in consulting calls that people don't report out of all the tasks purely because they just don't know how.

It's so valuable because there's so much information in there and it's all reportable. So there are so many tools to be able to allow you to get that information out in any way you like. So it's something I think people really should be exploring any of those reporting tools or reporting options that we talked about. I've even heard the same thing over and over again from ConnectWise users. So there's so many people out there even using ConnectWise.

You know, when you think that you may think that, oh, I've been using Autotask. And, you know, what if I could probably get better reporting if I was using another PSA instead of this PSA? Not the case. I think everybody struggles with reports regardless of whichever PSA system they're using, right? So but there is so much data in there.

And I think the concept is you must first know what you want to report on. So when somebody calls and says, hey, Rae-Ann, I really need a report. Can you build us a report in Autotask? Absolutely. What do you want it to say?

Well, I want a report that shows me this, this, and this. Okay, great. Here's what I want you to do. Get out a piece of paper and draw some columns and put a heading at the top of each of those columns as to what information you want on the report. It's just like a little mockup, right?

Pretend you're drawing the report on a piece of paper because that's going to help not only you but me organize the data and be able to, because the way Autotask Live Reports works is you choose this starting level, right? So let's say it's the customer level. So you start with the customer level and you have all of these options under the customer level. Then, based on the options that you choose, the next set of levels will be highlighted and grayed out because some things communicate well together inside Autotask and some things not so much. You really have to think through the entire report that you're getting ready to write.

And the smartest thing that you can do is draw it on a piece of paper so that you can start to build the report using that information. And in which order do you want that information? Is the high level the customer or is the high level the contract? And then what do you want? Do you want to know what tickets are underneath that contract or what tickets are underneath that customer?

What is the information that you're hoping to extract? Draw it out. Then you can build it. Absolutely. The thing I got out of that, Brianne, and I was just having a bit of a laugh to myself, is you're talking about designing it on a piece of paper.

So, for the UK customers, we use Excel and we will do the mock-up in Excel. So, any kind of modern-day customers that want to report, then Excel is your friend. But otherwise, a piece of paper would suffice as well. I would literally fall off my chair if somebody handed that to me in Excel. I'd be like, oh, my gosh, this is perfect.

I'm okay with a piece of paper and scribble. Use the back of a napkin. I don't really much care. As long as the information is organized, I can make it happen. Absolutely.

Yeah. And, I mean, you know, that happens to me so many times as well, right? Because I deal with clients all over the world. I get a scanned-in copy of something that somebody's drawn on a napkin or, you know, on the back of a cigarette packet or something. And, you know, I get a mock-up of this is what I want the report to look like.

And that's easy enough. Once you've got what you want the output to look like, then it's really easy to actually build a report through the Live Reports Designer to match as closely to that as possible. But, you know, there's so many options in Autotask now and in any PSA tool, for that matter, that, you know, you can build reports for anything and pretty much use any tool as well. Because, you know, they all have APIs, right? So, things like Power BI and, you know, some of these other reporting tools really have the ability for you to leverage Power BI and leverage the API to actually pull that information in as well.

So, you don't have to be limited just to the reporting tools that are built into the PSA. You can actually report in other ways as well. Okay. So, that was a really interesting conversation, Ryan. I really enjoyed that.

And I think there's quite a lot of information in there. So, if anybody wants to reach out to us, you can do that on any of the socials. We're on Facebook, Twitter, LinkedIn, or you can reach us at psaimpact.net. So, all that's left for me to say, Ryan, is your PSA is the key to your business success. Yep.

So, go out and make an impact on your world today. Thank you for joining us today on PSA Impact. We hope you've learned something and that you'll join us next time when we answer new questions posed by our listeners.