View this video and many more on Sales, Marketing, Operations, HR for running your MSP! Register for Chartec C.O.R.E for all your online resources. Level up your skills today!

Sign UpCall Us

Using Excel for Down and Dirty Reporting

Dan Martin - Director of Operations

Video Transcript

" Welcome everyone to our webinar on some tips and tricks to get data out of Connect wise and into Excel and how to manipulate that data for some quick reporting. So what we're going to do is we're going to start out inside of Connect wise. And this is something that you can do, really in any of the list views that you have inside of connectwise. So wanted to start with, you know, the typical issue that people come up with, or questions that you may be asking is, okay, I've got a bunch of this data, I may not have the report that tells me exactly what I want, I want to do some analytics on my service tickets that have that I've done, let's say, for the last 90 days. So we're going to take that example and just see how easy it is to do just that. So I'm going into the service ticket search, and go ahead and set this to be the last 90 days. And we go ahead and hit Search, that of course is going to go ahead and bring up all the data that we have for the last 90 days for inside of connectwise. Now once that happens, we wait for this to pop up here. Now what we're going to do is go ahead and export that data. So once you hit export, then that is going to go ahead and pull all of that down into a nice little Excel spreadsheet in your downloads folder. Now, since that can take a couple minutes, I've actually already downloaded that data. So we'll skip the waiting for Connect wise to download portion of it. And go ahead and open up the spreadsheet that gets created when you download that data. So here we have just kind of that raw information of everything in Excel, all kinds of data can be a little bit difficult to play with. So what I want to show you is how we can leverage what's called a pivot tables, some of you may already be experienced with them. If not, these things can change your life. So I'm going to go to the Insert tab in Excel and just hit pivot table. By default, it's going to select everything that I have in this particular workbook, or this particular table. And all I have to do is just hit OK. So it is going to take all of that information and put it into what is called a pivot table. Now once I have the pivot table created, now I've got all of these fields that are available, these are all the columns that were in that list view that you can notice here, It even includes like a custom item that I had in the ListView. So it's gonna show me everything that we've got. So now let's, let's start kind of doing some analytics of where we're spending our time on tickets. So to start with, I want to go ahead and say, Well, I want to see this by service boards, when I go ahead and click on the board. And connect wise, I'm sorry, Excel does its best to try to put it in the right location between the filter rows, the values, etc, of where it thinks that it should be. So you may have to manipulate that. And we'll I'll show you some of that here in a few minutes. So I've got all the boards now that are active inside of arcs Connect wise that I can now look at. So let's see what are the total hours that are being spent? Well notice that by just clicking on total hours, that automatically put it into the values and made that a sum. And that will most likely in this case, it's going to be exactly what we're looking for. So let's say I really only care about what's going on in the service department. And I just want to actually pick really three particular boards, I'm going to move board up to the filters area. And I'm going to just the easiest way to do that. And I'm going to hit the little drop down here. And I can now select multiple items. So if I undo this, say I really only want to look at the events that are coming in, I want to look at problems and I want to look at requests, those are the only three that I really care about. I'm going to go ahead and hit OK. And you'll notice that the sum changed here. Now I can I can drag this back down into the rows area. And you can see I'm only looking at those and I have the little filter icon there. There's multiple ways that you can actually do the filtering. But I usually just dry, drag it up into the filter section, just because that's easier for me. Alright, so now we've got the total hours that we spent. And let's look at this by time. So I want to put this based on the date entered. So if I click date entered, you'll notice that it had actually added two different rows. One is the actual date entered. And then the other is that it figured out that I probably may want to have months in there. So that's exactly what I want. But I don't care about the specific day. So I'm going to move this up to the filters area just to get it out of the way. And now I'm going to add also how many tickets were there in this list. So I'm going to go up to the ticket number and I'm just going to add the ticket number. Now you'll notice that it put that in as a sum of the ticket number, but that's not really what I want, right. So I want to want to go head down To this ticket number area where it says sum, and I'm going to change this and the value field settings from a sum to account. Okay, so now we can see how many tickets we had over those different timeframes. Now I can also manipulate this data in some other ways. Let's say we also wanted to look at maybe the average hours that we had per ticket, so I can click on the average. And now you can see where we have our averages being spent, right, so we're our average for this time frame was point three, eight hours for incidence, 1.4 hours for problems, and 1.44 requests, or there's a total amount of time that's being spent on those by average. Alright, so another thing that we can do to manipulate this is I can take this over to the columns area. And now you can see I'm starting to really build something that looks like a report. So a couple really nice things about this, it's really easy to go into any ListView that you have inside of connectwise, or really any application where you can get a list view, export that out into Excel, pop it in and go ahead and leverage the pivot tables to do this. Now, something that's really nice here is I also have the ability to drill down on these. So I can look at we see we have 10 tickets here for March. And the problem by wanting to look at what those specific 10 tickets were, I can double click on it. And now it's going to give me those specific 10 tickets, so I can do some further analysis on it. Cool. All right. So that's the first way that we can get some information out of Connect wise. But sometimes we don't have really a list view available or some other information that we might be looking for inside of Connect wise. So I want to show you another way that you can pull this data out. And it's not too not too difficult. So I'm not going to save this. And I'm going to go into what is called the web report, creator. So everyone has access to this, whether you are inside of the hosted version, or a on prem platform, the first thing that you need to do is create an integrator login inside of connectwise. If you don't already have one, there's lots of information, you would just go into your setup tables integrator, login and create an integrator login, and make sure to give it access to all of the different areas inside of connectwise. I'm not going to go through this at this point, if you need some help on that. We do have some some data on how to create an integrator login. Alright. So before I go into this, I want to show you one of the reasons why you might want to use the integrator login, instead of using a list view. So if I take a look at, let's just take a look at service. Actually, let me start back up one moment here. So the way that you get to your integrator or where you get to your web reporting, once you've created your integrator login is you simply go to the way that you would normally log into your connectwise on the web. And after the the V 400. Before underscore six release, then you just add in this little area into the URL, which is that forward slash web report forward slash URL creator forward slash Create Report URL dot ASP x. And we can give that to you. So you don't have to pause this and write that down. Alright, so once you've done that, it will go to this area, it will ask you for your integrator login credentials, and you'll put those credentials in and takes you right to this page. Now, this is going to show you all of the different web reports that are available inside of your connectwise. As you can see, there are a ton of them way more than what you would see for as far as available inside of listviews inside of Connect wise. So I want to go ahead and take a look first at the service one. So if we look at service, and we'll give it here a second to update. Well, maybe to get the right one. I'm sorry, it is of course it's doing that why we are coming in here live. Alright, so it should be service templates. Well, that's really annoying. Let me switch this to a different type just for a minute. Okay, so that did load and let's go back to service again. Alright, that's what I was trying to get to. Sorry about that. Okay, so once we load service, I added this in he'll just kind of get rid of that. So this is going to come up by default with it's going to limit it to 100 and it has a default limit. Five seconds. So you're probably going to have to change that. I'll talk about that here in a moment. But why would we want to use the web reporting URL for this instead of the ListView, is you'll notice in this list of available fields, there are a lot more fields that are available in this web reporting view than are available in your list. So one good example would be say that you're wanting to do a report that is based on what your SLA numbers were for a particular board or for a particular date range. You can use that you know, what was our resolve minutes, what were our responded minutes, who did it, you know, all that kind of stuff, lots and lots more information here that you can leverage to do the same thing that I just showed you in that pivot table, but more data that's available for the service table. Up. So since we already did one on service, I do want to, let's go ahead and take a look at something that's hard to find is the surveys. So if we pull up the surveys, you'll see, this is going to allow me to pull any of this data from surveys that is inside of connectwise. Now, whether you're using the survey module inside of connectwise, or if you're using smile back, if you are using smile back, I highly recommend that you have it update the surveys inside of Connect wise because it just gives you some easy ways to leverage that data. So first off is I'm going to add a condition because I don't want to create or I don't want to bring in every single survey that's ever been done. So I had the the condition of the survey year, that's one of the available fields here. And I wanted to only look at the survey year where it is equal to the year 2020. Okay, and there's a a sort of helpful link, you can go into developer Connect wise comm for more information on how to do different conditions, you can also reach out to us if you have a quick question, some of these can be a little bit of a pain based on the the type of conditions that you're doing. If you're trying to get a little bit more complex, then you can do an order by field, I don't really care about that. And then it has a limit. So by default, the web, the web reporting is going to limit you to 100 Records, that you just simply delete that. So you get that we're going to bring back everything that is going to hit this condition. The other thing that you're probably going to want to change is the query timeout, I usually set that to be about 30 seconds. So it should be more than enough time to pull everything that you need. And then I'm going to, I'm just going to add all the fields that are available into, you know, to be available in this web report that I'm pulling back. Next thing we're going to do is go ahead and just click the Create Report URL. So that just gives you this big long URL, and you can test it. And in a new window, especially if you're using conditions, I highly recommend you do that. So if you hit that test button, you'll see Yep, it's pulling data. So that's really what's important, we're getting information in there. So what I want to do is go ahead and just capture all of this information into my clipboard. So now that I've copied this URL, now I'm going to pull this into Excel. So the way that we do that, go ahead and open up Excel. Alright, so I'm going to go ahead and do a, just a blank Excel document. And now I want to pull in that web URL. So there's some really cool things that we're going to be able to do with this that I'll show you here in a second. So you're going to go to the Data tab, and then you're going to click from web. And that's going to bring up the dialog for the URL that you want to pull data from. At least in theory, it pulls up that dialog. Okay, so it's going to bring up this place. And all you do click in the URL. And I did not actually get the URL and go back over there and try that again. Now I've got the URL. And I'm going to go ahead and just click OK. That's going to this is going to validate that this URL is correct. If you had done something improperly, and you hadn't tested it before, it may return something that you know you had an invalid information or no data was available. Once we hit that, then we will wait for it to go ahead and load this in the navigator. And you'll see here that we have in this display options. We have something here that says row. We're simply going to click on that. And you don't have to wait for the preview but you'll be able to see that you are getting data here and then click Load. Now what that is going to do this is going to be saving the connection that you've put into place that's going to have your credentials are also going to be saved to access this particular web report into this spreadsheet. So I do want to be careful the same way as you don't want to be giving people something with your data, you want to probably not give them something that has a live web report in there for somebody to try to deconstruct your authentication information for all your connectwise. Alright, so now we have data. So we're going to be able to do exactly the same thing that I did on that other pivot table report that I was showing you, we're going to go to Insert, we're going to go to pivot table, go ahead and select OK. And once again, we've got the same information here that we can now work with. So maybe I want to go ahead and look at this. Let's see, I want to go ahead and make this the look at this by this by type. Make this the, you know which quarters that we pulled in here. So I've got two quarters, I want to go ahead and put in buy points that we have in here, I can go ahead and make this again, instead of a sum, I can make this an average. And so we can see how we're doing on each one of these. So if I wanted to look at this by company to see how we're doing, again, by company, I can go ahead and pull my company information in here. And I can go ahead and move my you know, again, my quarters up here. So I get a good idea of how to be able to play with this data. So a couple things that are really cool, though about using a web report, instead of using that list, the export out of connectwise is that this is more this is live, right. So every time I want to do an analysis, by exporting a list, I have to go back into connectwise, go find that list, export it go through that whole process. The cool thing about doing this in the web reporting is all you have to do once every time you've created your report, and it has what you're wanting to see, you can go to the Data tab, and you can hit refresh. And that is going to do a live query to refresh exactly the way that you created, that URL is going to be pulling in new data. So anything that's changed will automatically be updated for you inside of this inside of this pivot table. So that's really cool, gives you some really nice information to work with. And is it's a pretty simple thing to be able to do. These are even with clicking a couple wrong things. We did this in just a couple of minutes. And one other thing that I want to mention. Obviously, we use Connect smart and are big fans of leveraging Connect smart that we have the same ability, it's one of those little known little known facts that there is the ability for you to connect directly to your data and connect smart as well. So one of the disadvantages, and why would leverage Connect smart. For instance, if I was doing this based on the like on the surveys is you'll notice that I only have what was available in that survey report, right, it doesn't show it by service board, for instance. So if I wanted to see this by service board, I would leverage I would pull this straight out of Connect smart and do a do an Excel connection to my data and connect smart and then be able to pull that has a lot more information that's, that's linked together in what's called a view inside of the Connect smart data. And those can also be customized, even if you are a hosted Connect wise person, you can still have that same kind of access to your data as if you were on prem. So I hope that this has been something that's helpful for you that you can see how to easily get some information, pull data out of Connect wise, be able to manipulate it inside of Excel. And one thing I do want to mention that you do want to be careful of there's a lot of stuff that you can do. But you want to make sure that you're always validating your data. So make sure you're double checking it Excel. One of the reasons that some people don't like to use Excel is if you start doing a lot of manipulations of the data. And maybe you're summing something that you thought was an average, or maybe you had a min or a max or you had something else filtered out, make sure that you are looking at the data that you think you're looking at before you're making your business decisions on those. And if you want some more information on how you can get more and more information out of your connectwise we've got a couple resources that are available for you. One, of course, is in our classic memberships, you have the ability to talk to your account manager, they can get you pointed in the right direction. Connect smart is always a wealth of data. Pun intended. So whether it's reports, whether it's the dashboards, whether it's accessing your data and being able to manipulate it in Excel, those are all available. And we also have our premium memberships, where in those operations, we're doing some of this information, this kind of thing for you, right? So we're going to be building these reports, creating the information that you need to see helping you understand what's going on in your organization. And we're just doing that for you and mentoring you along the way All righty. I know that was a lot of information. Thank you guys for, for hanging in here with us. I do have a couple questions that I'll go ahead and answer. And if you have any other questions, please go ahead and toss those into the chat. One I know that was, you know, going through some things that may be new for you. And, you know, are we going to have this available for you to listen to? Again, the answer is yes, we are going to be putting this up on core. So you will see this in the courses under operations and some sort of how to get data out of out of connectwise or how to manipulate data in general regardless of connectwise. Another one was custom fields I mentioned really quickly about custom fields on a ListView one thing that you do need to to leverage if you're adding custom fields into really any area and connect wise it's going to show up there is a checkbox checkbox in the setup tables that is do include in a ListView so if you want like that one I had the it glue article, I want the art it glue article to show up in a list view so that I can export it need to make sure to check that box of including ListView many people didn't realize what you could actually ever leverage that for another one was little having to do with Connect smart and custom views, we do have the ability to really create any kind of view so view is how you kind of pull data together so that could be a survey score tied in with agreements or agreement profitability on the 14th I'm going to be talking about agreement profitability and one of the ways that we do that a lot is actually leveraging Connect smart because we can get that monthly profitability by agreement and so that I can pull that in to have an understanding of revenue and profitability, etc and then chart or graph it or put it into into Excel. And then Michael also had a question about integrator login for CW automate. So this actually is going to be for manage is where you have the integrator login for if you're using Connect wise, if you're using Connect smart you just need to contact your Connect smart support for for some data on how to set up what's called your read only user to access the all the data that you have inside of Connect smart. So I hope this has has been of some benefit for you guys. Like I said, we will put this up on core so you can not have to maybe take as many notes and be able to review it again. But thank you very much and hope you have a great day. "