In this session, we answer your live Q&A about: Using Autotask New LiveReports to filter through complex data; advanced row and section suppression in LiveReports; formatting for online vs downloaded reports.
Do you need assistance with a complex task in Autotask? Are you looking to enhance your reports or optimize your Workflow Rules? Look no further! Join Dustin Puryear, Autotask expert and founder of Giant Rocketship, for his weekly office hours. It’s a complimentary opportunity for you to pick the brain of an expert and get things done. All you need is your curiosity and a willingness to learn. So, don’t hesitate, sign up today!
00:27 All right. Hello everybody. So the first thing I always like to do is introduce myself and then do a quick mic check. Actually, I like to do it the opposite way. So if somebody can just confirm that they can hear me to make sure I don’t just speak to the other, and then we’re going to go from there.
00:48 So just in the Q and A say, I can hear you. Doesn’t you sound fan fantastic. And then I’ll start moving forward. And if somebody says you can’t hear me, that’s even worse. So definitely let me know. Perfect.
01:00 Audio is working great. All right, so Dustin Per year giant rocket ship. We automate help desk and Autotask. This is my weekly office hours. So I spend 30 minutes going over some component of Autotask.
01:15 This is mostly Q and a Driven. That said, I have decided to come in here with a little bit of an agenda every week. And so for questions, absolutely, I’m going to answer those. But also I want to start having little focus areas so it’s not quite as chaotic.
01:36 And so what I’m going to be focusing on today is new live reports. There’s things that people do not do in new live reports that you should be doing. And so I want to go over those things and so boring when I’m answering questions.
01:52 What I’m going to do is create a report that shows tickets by create date. Not difficult, but then from there, I’m going to do something that people don’t realize you can do in new live reports, which is you can filter in and out rows.
02:07 So I’ll give you some examples where you may have tickets. And then you want to only show tickets with time entries. And then you might want to not show tickets that have time entries. Right. And that can be anything.
02:20 You might want to show all tickets that. Have some set of configuration items against it. There’s all kind of options here and it’s more powerful than just using a report filter. It’s a row level filter.
02:35 And honestly, of all the people I’ve ever kind of shadowed when they do new live reports and I’m helping them with something, I almost never see anybody use this feature, but it’s extremely powerful.
02:48 And so let’s get going here. Let’s make sure I got this puppy go up perfect. Okay, so let’s go ahead and do that. And again, if you have questions, just let me know what we’re going to do. Again, we’re going to focus on some new live reports, an area of weakness for most people.
03:06 And so first thing we want to go over is the fact that Autotask comes with some of these default reports. But you can’t really edit these. When you want to create your own custom reports, you need to go to reports and then designer a heads up.
03:20 And I find this confusing, but it is what it is. Publisher and designer are separated. And so when you want to actually make a report appear here under Live Reports, you still have to publish it from a different button.
03:32 So just a reminder if you can’t figure out where it’s at. And let’s see. I like those two things as tabs easier to manage it. Now, working with New live Reports is pretty fast. It’s not as slow as when you go into the workflow designer, but it can pause sometimes.
03:49 In fact, you can see it pausing right here. And also, I was having some internet speed issues earlier. I don’t think that’s going to impact us, but let’s find out. Usually pretty fast. I’m on Fiber, but you never know.
04:03 And let’s see if we’re having issues anywhere else. And if New Live Reports is going too slow, we’ll just pivot and do something else. Well. There you go. Now, actually, I saw something come up in the Facebook Autotask group.
04:20 And what kind of made me think about this was somebody will say, hey, I need a report. But it’s kind of exhausting looking at all the system reports. The reality is most people do create reports when they don’t need to.
04:33 Autotask comes with a plethora of reports for you. And this little lock means you have to clone it right to copy, and then you’re going to want to tweak it. But that’s kind of boring. We’re not going to do that.
04:46 So what we’re going to do is we’re going to create a customer port. And then again, I’m going to start doing some more complicated, sophisticated changes to it. Let’s start simple here. And so very common for a service desk manager or even an owner who’s looking at profitability and time spent.
05:04 But let’s say a service manager just wants a nightly report of tickets by Create Date. That’s what came up in the Facebook, right? Buy tickets by create date. Now, I’m going to walk through a couple of best practices as I go through this, right?
05:26 That said, the key thing to understand I have a really good blog on this, by the way. Be sure to subscribe Rocket blog. But a key thing to understand with new live reports is it’s driven by the first category you choose.
05:40 And it makes sense. All reporting systems, when you are reporting on X and you want to report on Y, there has to be a connection between those two things. And so the second you say, I want to report initially off of X, if there’s no way to connect X to another thing, the report can’t combine the data.
06:00 And so it’s important to understand that because sometimes people go to the wrong category and they start building up reports. They’re like, well, it’s not creating the rows right or whatever. It’s because you chose the wrong initial category.
06:13 This is an easy one. This is just take us by create date. Now let’s go ahead and create this puppy. Notice this. That’s just saying this is the core item here. That little asterisk. Now that we have this initial one, what we want to do is let’s connect a couple of things.
06:32 I find it interesting with Autotask. I deal with the API a lot when we’re developing it and fine tune in the rocket ship app. In the API, it’s company in the interface, it’s account. And also in the old soap API, they called it Account, but it’s actually company in our API documentation.
06:52 So I just have to remember, wait, that’s actually the same as company. Okay, so the next thing is, and this is the trick, and this is where I’m going to get into some more details. Notice you can’t really link to time entries here that are specific to resources.
07:09 It forces you to only say ticket assigned to. And I’ll go over that. The reason is because you chose the ticket as your initial entity. If I had chosen a work entry or time entry is my initial category, then it would have flipped it, right?
07:25 It would have said, hey, look, time entries are my primary driver, and then we can back into the ticket. We’re going to do that later. But let’s just go ahead and start with these categories. And they wanted to know, hey, how do I get one off of Create date?
07:40 So let’s go ahead and do that. Create date time is the sort by you want to sort by create date time, not just create date because you want things that come in at 08:00 A.m. To come before in your report in 10:00 A.m., right?
07:52 So sending order is fine. Filter, filter by. Obviously, we do not want to see every ticket ever created in history of histories. And so we’re going to do is we are going to do a between. Super common to do a between.
08:08 And so what we’re going to say here is we’re going to use their little functions and we’re going to say. Today -30 days or actually let’s do it for the current week, right? So first day of current week and then today.
08:34 Now this is where it wants you to actually kind of create the layout. I don’t care for this and so I like to just do it by hand. But particularly when you’re getting started, that definitely can help.
08:46 And so next we’re going to go ahead and create this report. Take us by created created the little heading for us, which was nice of them. Now the only downside to doing this by hand is we have to create the header rows.
09:00 But that’s okay. So notice how it breaks us out. Now what’s important to understand is this row because my initial category drives the report, therefore my detail row is the ticket. It’s not going to be the account or the ticket assigned to, but I’m going to show you how you can get around that.
09:20 So what we want to do here is they do have a search. Now title and almost always people want the number, ticket number. Let’s put account name and create date. So again, you have to put this in manually because we chose to do it out using the wizard to create this create date and account name.
09:56 What I like to do here is I like to bold that and then I like to underline it. It just makes the report much easier to understand. Now let’s go ahead and run this and then let’s get a little bit more complicated with it.
10:10 I’m going to show you how to get around this. This initial limitation that all the rows are driven by the ticket instead the next thing I’m going to do is I’m going to show you how to use that same report and drive the report by the account.
10:26 Okay so I’ve only created once this is probably a bad filter for me. We only create a couple of test tickets here and there and so let’s just say today -30 days check if we have any questions. Oh, yes.
10:44 Okay. All right, so I’ll answer some of that later. Okay, so let’s create this again. And I like to just save every once in a while. You just never know what’s going to hang up. All right, so we’re going to run this and again this is going to be a super simple just a linear report that doesn’t show us much.
11:05 And what we’re going to do next is get a little bit more complicated with it. So this is a great report kind of sort of the problem is it’s kind of hard to read this thing particularly if you have a help desk with several people.
11:17 This report is going to be like 40 pages long right? So not super helpful. So let’s get a little bit more complicated with it. Now what we want to do is let’s go ahead and group this by the company so that in fact I’m going to leave that open so you can see it.
11:34 So we’re going to do here is we’re going to create groupings. I’m going to show you something that we don’t have set yet and we will set it shortly group header and notice it just wants it to do that.
11:47 Now what’s important to understand here is what’s showing up in the group options the primary ticket and the create date time. So recognize that what it’s doing is giving you the ability to group by what you have sorted.
12:03 So count and account name now look, if we’re going to group tickets um. Under account. You have to sort the account first, right? Because you could have yesterday I had a B and C account but I want a one two, three, B, one two, three.
12:25 Right? So we’re going to sort first by account name and then under that we’re going to sort by the ticket create now right now that only is going to create a sorted report by the account name which is not going to be super helpful for us.
12:44 And this is not it by the way. This is that old one that’s not going to be super helpful to us. So what we’re going to do is we’re going to create a group header and it’s showing this now because it’s sorted.
12:57 That’s why it’s doing that. Now what we can do is move this over here. So this is going to start looking like a report you’re used to seeing. And what you want to do when you do this is you just want to merge these cells, right?
13:19 And so now watch our report phase two. And what you’re going to see is a much more organized report but we’re going to get even more sophisticated from there. And so let’s take a look at that. Now look how pretty that is, right?
13:36 That’s a much more readable report. Now let’s do some summarizing. So what do we typically want to know when we’re looking at ticket counts? Right? If we’re looking at a gauge on a dashboard we generally want to look at rank rankings based on how busy we are based on certain customers.
14:01 That’s not always great intel because if you have a customer is paying you a million dollars and they generate 1000 tickets and you have a customer paying you 100. Dollars and they’re generating ten tickets.
14:12 If you just look at the raw 1000 ticket mark did I say 1000 tickets? I think I have that right. But anyway, he’s the one that’s paying you a lot of money. If they’re generating 1000 tickets, you still might be killing it and doing better with them than the other account.
14:33 So don’t hype or focus on one KPI. In fact, whenever we’re onboarding customers, I’ll tend to go into business talk. And one thing you always want to make sure you’re doing is focusing on two KPIs. If you ever look at just a single KPI, you’re wrong.
14:49 A single KPI is always wrong. There’s no KPI in the world where there’s one number and it shows you the data you need. Always wrong. You can never trust it. And the example I always give people is you have sales and you tell your sales exec, hey, go sell $1,000 for the month for your quota comes back.
15:13 He sells 1000 screwdrivers. That didn’t help me as a company whatsoever. But he hit his quota. You got to pay him out. That one number is not sufficient. You have to govern a single KPI with another KPI in this situation or in this situation that sells, that second KPI would be average sale, right?
15:33 So you could say your average sales would be no less than $500 per sale, and then you have to sell no less than $1,000. Okay, good. Sells two cheap laptops or notebooks or something. The key thing to understand, there is never a single KPI anyway.
15:49 So let’s take a look at this and we know it’s doing this. And now we’re going to summarize this with a single KPI just to get things going or report footer. And so now we want to do a count of how many tickets these people have generated.
16:07 They have some really nice functions here. And so typically I will do it by hand. But let’s just see what it looks like. So remember, what we’re trying to do here is count. How many tickets did this account create?
16:19 Right. And so we’re going to have that in the report and then we’re going to do more and more stuff with this. And so I must never go in here. It’s going to be account here somewhere. Count and, and field category to count.
16:42 Interesting. This is new. Okay. So generally you just want the unique value. So what’s the unique value for a ticket? In fact, I made sure to put it would be the ticket number. Right. Interesting. Okay, nice.
17:11 Here we go. And then, so count of tickets and it’s a report. So you want to do that. And let’s do a little formatting real quick. I want you to see how that works. Right now if you were to print look what it’s doing.
17:30 If you were to print this on paper or as a PDF, it would be half of the page. Super annoying. I don’t know why they don’t default these things. But I’m going to go to general and fit the page with right.
17:43 So notice we’re getting more and more complicated with these reports and it’s going to get hairier. As we go through this, our questions are going okay. And so one question is what I’m talking about when I say filter by rows and you’ll see what what I mean there.
18:03 Anyways, notice how we’re starting to fill out this and. See. So look, I did it wrong. I must have done a report footer. So count of tickets is 52. So it is getting the 52 but it’s not doing what we thought it would, which is fine.
18:20 Look, I put report footer because I’m not the brightest lad. And so look how easy this is going to be to fix. And also notice we decide we want a space there, kill this. We don’t total tickets. And what’s nice here is you can just copy and paste, you have to select into it.
18:45 I don’t know why. Okay, and so we want to put a little space above that, right? This is what you normally see with reports. The other thing we want to do, notice how it’s putting a decimal point, but this is really just an integer.
19:01 You can’t have 3.2 tickets. And so what we’re going to do now is we are going to format this thing and I wish there might be a quick way to do it. Now maybe they’ve made it better. But look, go to number zero and if it’s zero you can make it blank.
19:19 I’m not going to do that here. And then we’re just going to paint. Now we have an even better looking report. So a heads up, one thing to see. Look on, wait, count of tickets, 1010, 2340. Twelve. Okay, that just happens to be a coincidence.
19:56 You know what think it is. We auto generate tickets sometimes and so the test driver is just creating that number of tickets and then. You can tell here I must have done these by hand. Just twelve. I thought it was a little suspicious but no, in fact that’s just how it is now look how nice that is.
20:17 Now we have these grids. I don’t really know anybody likes to see grids and so in the report viewer show grid and that’s going to be specific to this report. Heads up. And so the next time we see it we’re going to see that and and what’s nice here is you can do this which is going to be section Shading.
20:39 If you turn up the grid it gets hard to see the difference and so what you want to do is you want to alternate the rows with shading and it’s going to start looking real professional, real fast. Okay?
20:57 So while that’s rendering, what I was going to say here is as you get more and more data for these reports it’s going to get slow right? And so the reporting module is not Autotask focus really it’s more on how interactive their platform is and so this is going to get the least amount of resources and so if you have big reports you really need to schedule them to run overnight.
21:22 They might only take four minutes to run if it’s a huge report but who wants to sit here watching that thing spin, right? And so now it’s producing that if it hung up we might have to start over and so after that is done what we want to see here is break it.
21:48 There we go. All okay so look this is starting to look much better right now really. We probably could put this on top. And so again we’re always kind of tweaking and trying to figure out what’s going to look best here and so watch this.
22:07 Insert after. And I’ll be totally honest with you, I’ve gotten pretty fast at building these. You’re going to develop your own style just like anything else. And so this is just what reports tend to look like when I build them out.
22:36 Now the next thing we want to do is how do you filter out rows a report that are specific to the row and not the overall category. So let’s say we don’t want to have to hard code a bunch of crazy filters in here.
22:56 Maybe we’re tabulating something. And remember we had count of tickets is ten. And let’s say we’re tabulating something but we don’t want to show it in the report under certain situations. So here’s the niftiness, right?
23:15 Watch. I’m going to select this puppy and I’m going to go to format cells. Look at this. Conditional. Almost nobody uses this and it’s super powerful. Watch what we can do here. Foreground color. What we’re going to say is if the cell value is greater than ten but you didn’t know you could do this.
23:42 If the cell value is greater than ten, I want to freak out. I want to make this red. So this is like excel. But you can actually do some really cool stuff that I don’t know how to do in though. And so now see what we got.
24:02 Okay, I’m answering that question for you about what we’re doing with conditionals. Now that twelve that I was concerned about is going to be better be red. Notice it’s red now. And so look, you can start highlighting big issues, but here’s something else you can do with this conditional formatting.
24:21 Maybe if, let’s say that the cell value is less than twelve, right? Not only do we’re not going to make it red, we’re going to hide the row. Watch this. So now we’re going to hide that entire row so we don’t have to look at it anymore.
24:47 Because we may agree as Services Manager, it could be time entries, it could be configuration items. Maybe we don’t want certain accounts. But we don’t care. Notice how it’s hiding that entire row. We only want to see something if it deserves our attention.
25:04 Now let’s see what happens. And I think it may have this, right? You could do page breaks, which are cool. Suppress section. Now with the suppressed section, it’s going to suppress this whole thing, which makes it useful.
25:24 Generally, if you have a huge like a bunch of stuff and you don’t want to create conditional formatting on every row inside of that section, it could be in your detail as well. And so you can do the entire section so that it doesn’t show anything.
25:43 So it’s hard to see it like that. But let’s do it like this. Before, if we were just suppressing that row, then it still would have shown the 11211. But now that it’s doing the section, it’s going to ditch that whole thing.
26:02 So notice it’s even getting rid of the line of the extra rows that we were using for white space. That’s kind of a big deal. And then it’s showing it here and so it gets really powerful. Let’s say you’re doing a kind of a complicated detail road and let’s say that you want it to hide any ticket that has some value and you don’t want to do do it at a filter level because maybe it gets really specific.
26:31 So maybe your cell value is this less than ten and they have a funky way of doing this. And let’s say we have a special rule here that says if the cell value is less than ten and the account is zero by the way, I always call that self account.
27:01 In fact, in rocket ship we call it a self and the CRM search rules you create. But what’s happening here is we’re just saying it for self, for your account. Almost never should you be using account name in a situation where you were going to reference your account.
27:17 Just say account is equal to zero and then let’s see that’s how you do it. I think I have this right. And so you would hide this entire section in that situation you would no longer see any detail records for self if that value was less than ten.
27:38 So again, that’s a feature somebody uses. One feature is probably what lets you create the most powerful sophisticated reports that people struggle with is conditional formatting because it lets you hide rows and sections and change things in the report dynamically instead of using the big hammer.
27:59 That is the filters. Because filters, if you’re going to do it, it gets very difficult, and it’s just unwieldy to do really sophisticated filtering when you can just do it at a row or section level. All right.
28:12 That’s new. Live reports. I hope you found that helpful. I’m going to make sure I post this on Facebook to answer that person’s question. But if you have any other questions, by all means, let me know.
28:22 Again, I’ll be coming in here with an agenda going forward every single week, and as you have questions, I will answer those as well. So I certainly invite you to my next one next Thursday. I have invitations going out weekly.
28:34 I look forward to seeing you there. Thank you.