Pretty (and Useful)! Building and Using Excel Dashboards (Recorded Webinar)

TRANSCRIPT

Jess: Okay, let’s go ahead and get started. First off, thank you so much for joining us today. My name is Jess Mooney and I run the webinar series for Encore. We do have a lot of different topics that are gonna be coming up throughout the year. This is, of course, “Building and Using excel Dashboards.” We do have a lot more coming up around Dynamics specifically, but we do have like some of these more general sessions as well. If you’re interested in looking at other topics we have available, just go to our website, encorebusiness.com, and in the upper right-hand corner you’ll see a link that says “Events.” Click on that and you can see all of the webinars and also our in-person events that we host throughout the year. So definitely take a look at that, it’s a great resource to get live training just like today and get access to the presenters during the webinar in a live Q&A, so really good stuff there, definitely take a look.

And before we get into a lot of the good stuff that you guys actually registered for, I’m just going to do a little bit of housekeeping. So the first is that GoToWebinar panel that you see most likely on the right side of your screen, if you want to pop that out of the way, just click that little orange arrow at the top, that way you’ll be able to see all of the presentation and get that out of your view. If you want to ask a question to the presenter though or even one of me, make sure that you add that to the questions pane right here at the bottom, you just want to enter that in and hit send and I will be collecting those through the webinar and then at the end I’ll feed those to the presenter one by one.

We did it this way because we do have a lot of great topic to cover today and we don’t want to get off track, so this helps us stay on time and make sure that we are addressing questions each individually. But if we do get close to time or if we don’t get to your question because we ran out, there’s no problem in us taking it offline. I will be giving you the about the link to the recording today as well as the presenter’s information as soon as the webinar is over and you feel free to reach out to him or to myself to get any information or answers that you need.

So a little bit about Encore I know that most of you are familiar with us but I do see a few new names in the attendee list here. So Encore is a full service Microsoft Partner. We have expertise across the entire platform that supports Microsoft Dynamics. We are chiefly a Dynamics partner, but we have a very strong cloud practice, Power BI, quite a few different things but we do support, as I said, all those different complimentary and peripheral platforms and applications that do support a dynamic system.

And just to give you kind of an overview here these are our core areas. We’ve got Dynamics CRM which is now Dynamics 365, Dynamics GP, NAV, and AX, and as I said before with the BI Suite we bring in Tableau, Prophix and others. And then our Cloud Productivity is quite a deep practice with a lot of information there. So if you’re interested in any of these other areas, please do reach out and we do have webinars about all of these areas as well coming up, so do go to our website and take a look at coming topics.

So that’s enough for me. I’m gonna hand this over to Jason Buckingham. He is the Practice Lead of our Dynamics GP organization and also kind of one of our excel masters. So he’s the one that came up with the topic for today and it’s definitely a pretty jazzy one. We got a lot of great response on it, so looking forward to seeing what he’s got going here. Jason, can you hear me okay?

Jason: Yup, I can hear you, Jess. You can hear me?

Jess: Perfect. And I’m gonna send you the presentation right now.

Jason: Okay, sounds good. Let me get my screen shoot. All right, perfect. Well, again, just thanks a lot for the introduction here. We’re gonna get started just jumping right in here. So, you know, excel, utilizing excel with Dynamics GP, throughout my career in Dynamics GP I’ve always been leaning towards the side of if you’re doing something in excel let’s find a way to do it in GP, why work in two systems. I would say in the last five years that mindset has changed quickly when we can realize the power of utilizing a simple tool that most people are familiar with like Microsoft excel to take the data out of GP and show it in a way that’s informative and easy to understand and easy to use. There’s a lot of power to be found there.

So today’s presentation we’re gonna be focusing specifically on creating dashboards but some of the lessons that we’re going to cover are also going to show you other ways that you can use excel for refreshable excel reports or other ways to pull data into excel or from other data sources combined with your GP data, yeah, into one reports within excel, so. Today’s material we’re going to just go through an overview of dashboard components. We’re gonna talk a little bit about some things you need to think about when you’re considering designing a dashboard. And then we’re gonna actually step through, you know, what does it take to actually build a dashboard. And, again, these are gonna be kind of at a higher level but it’s going to at least get you started with using dashboards and pulling data into Microsoft excel from your SQL database continued GP data.

And just so you have something to walk away from, something to walk away with, I should say, we’re gonna go through a quick start guide. So at the end of this webinar, I want everybody attending to be able to log in to Dynamics GP and actually export a dashboard that you can start using today out of the systems. So we’re gonna be going through all four of these components here and just a quick tip here, we’re not going to be stuck in PowerPoint presentation mode throughout this whole demonstration. I definitely would rather show you in the systems, so we’ll kind of be switching in between PowerPoint presentation and some live examples in my development environment, so.

So the first thing we want to talk about is just overview of dashboard components. You know, what are the things that actually make up a dashboard and I like to use the financial dashboard as an example of that because there’s a lot of information in this financials dashboard. Okay. So when we look at this, you know, first of all, we see up at the top, all right, we see a nice little KPI that is meaningful to me in my organization. It shows me my net sales, my cost of goods sold, my gross profit, and my operating expense, and gives me a breakdown of my net income, all right.

I also see a date slider. So when I open up this dashboard, I can actually choose the dates or a range of dates where I want to display the information for. So it’s a nice little filter bar that I can slide and I can interact with this dashboard to adjust the time of the reporting, the reporting periods. All right, I have a nice little operating expense pie chart on this one here that’s going to break out, you know, my operating expense by percentage of total, okay, so I can just take a quick look and see, you know, in the company that I’m extracting this information from 91% of my operating expense is coming from salaries expense and that might be normal, you know, and if I wanted to check what it was last year, I can slide my little date selector back one year for quarter one, 2016. And I can compare the two. Okay.

I have a nice little bar graph here that’s showing me my account balances by account category. I have a nice little display down here of financial ratios. So for quarter one 2017, again, looking at my data selector, I’m on quarter one of 2017, I’m instantaneously looking at all of my current ratios that me as a controller or somebody who has influence over the financial direction of my organization, I can take a look here and I can see all my ratios that make sense to me, that mean something to me, all in one view.

All right. And then also I want to see what our current cash account balances are. So I have all my checkbooks listed out as well as a view. So in the display, I have all of these pieces that are showing up in one excel spreadsheet. I don’t have to log in to Dynamics GP to view this. I opened up an excel spreadsheet, it authenticates me as a user and then I’m able to see this information and interact with it by selecting certain dates. And as the dashboard, you know, the display component of the dashboards, you know, because we’re in excel I have all these tabs at the bottom as well, so I can see the first tab I’m looking at is my dashboard. And behind the scenes, okay, our additional tabs and those tabs are where the data resides, all right.

So anybody who’s familiars with excel you’re probably familiar with creating graphs, your dashboard is basically an excel workbook with a bunch of data tabs either pulling directly from Dynamics GP or it’s some kind of a formula or a custom calculation that you’re doing in a different tab and then you’re building a graph off of that and all those data tabs together are pulled into visual displays into a dashboard. Okay, so pretty simple, pretty straightforward, all of those different components. But when we actually go in and take a look at what’s required to get those components to work together, we need to take a look at the two main areas, the data tabs and the display. So the data section of it is customizable data in one or more tabs of the excel spreadsheet, pretty simple. How do we get the data there? That’s the big question.

We use a data connection and that’s a standard function of Microsoft excel. We can use data connections to pull information from Dynamics GP SQL, data source, other data sources or a combination of sources. So if you have the information that is important to you sitting in another excel spreadsheet and access database maybe it’s another system altogether. Using those data connections, you can pull information across systems or across data sources into one usable dashboard. Okay?

The data sources of the data connections can be customized to refresh automatically or manually. So if you have a date selector, you might choose your data connection to refresh the information every time the date changes or you might have one setup that’s going to refresh every 15 minutes or you might say, “You know what, I want to manually click the refresh button and then pull the information as of right now.” Okay.

The connection properties that we’re gonna take a look at will allow you to authenticate the connection prior to running, all right? So when you open up the excel spreadsheet, depending on what your security requirements are, because that’s typically a big question around, all right, we have this excel spreadsheet that’s connected to my SQL data, what kind of authentication is going on? There’s a couple of choices there. You have, I’ll call it a remember this user kind of authentication that you can set up in the backend or you can actually tell the data connection on the properties to authenticate where you have to type in your username and password when you open this sort of every time you refresh the data, all right?

Now, to become really good at using data connections, I recommend becoming a little bit familiar with T-SQL. There are some wizards that we’re actually gonna through today that will help you create a query against your data so that you can define what you want to pull in. But when you really want to get good at what you want to pull from SQL and be specific about the fields of the tables that you want to pull in, T-SQL is the language that can be used to pull that information in, all right?

So once we have the data in, in the excel spreadsheet, we use that data to display the information on the dashboard, all right? And that brings us to the second major component to display. And that’s just a visual representation of data that makes the information easy to interpret. Okay? So two main components of the dashboards and we’ll be taking a look at both of those here in a live example. Before we do that, I’m gonna move in to how the data connection works.

So with the data connection we’re just gonna cover this material now and when I go through an example of building the dashboard, we’ll kind of circle around and show this as a live example. So the data connection itself has a couple of diffident tabs. There’s a definitions tab and a usage tab when we create this data connection. And the definition tab, that’s where we actually define a connections string. Okay? The connection string helps defines where the data is gonna be pulling from and how to connect to the data source. Then we’re gonna have a command text box and that’s going to define the query. Okay, so it’s going to be a query, like, if you’re talking T-SQL, it’ll be something like Select * from PM10100, PM10100 being the SQL table we’re pulling the data from. Okay.

And then we’ll also be able to take a look at here once you create a data connection you can export the properties of the data connection and import it into another excel spreadsheet if you wanted to, you know, combine data connections that have already been created. You don’t have to reinvent the wheel. You can reuse these data connections in other excel spreadsheets. Additionally, on the data connection, we have a usage tab and the main part I want us to walk away with on the usage tab is that refresh control and that just lets you control how frequently you want the dashboard to refresh, whether it’s manually, whether it’s automatically and then the increments you can pick on that.

So really quick here, I am going to exit out and we’re just going to jump right into my live system and I’m gonna go…I’m actually on that financials dashboard right now and I’m gonna show you on the ribbon up on top on the data option, this is where we find those data connections. Okay? So these are the data connections that currently exist in this workbook. And if I click on “my connections,” I can see here that I have a data connection called account transactions and I know that that account transaction, actually account transactions two is feeding information into my account transaction data tab. And then the information in this data tab is feeding one of my graphs. If I go into the properties, this is where I see that usage tab and I see that definition tab, all right?

So on the definition tab, we take a look at this connection string. Now, gurus who are very, very good at excel are going to know that, hey, I can come in here and I can actually type this out instead of going through a wizard to create my data connection and really what this data connection is doing is it’s saying, “Okay, the provider is going to equal a SQLOLEDB connection. And security, we’re gonna be using a security, specific security protocol, all right, security information is gonna equal true. Catalog, the initial catalog equals two, that’s the database, the company database I’m connecting to, the data source when PPQP with all these additional characters on here, that’s the actual SQL database that we’re connecting to itself. All right, I go into SQL server management studio, I’ll see through open up here. Here we go, there’s my server name. All right. So that data connection is actually calling that SQL server database is here, this is the company database, all right, and it’s telling this excel spreadsheet how to connect to the data.

All right. So all of these may look confusing and cumbersome, but it will make more sense once we actually go through a wizard and have excel help us create all of this information. This can be automated and it’s a pretty simple process when we go through the wizard. All right. For this data connection my command type is going to be SQL, all right, so we’re using that SQL language and then we have the command text, this is the actual script query that we’re going to be using to pull the information in. It’s saying, select and it’s calling out a field name in a specific table, all right, so journal entry, transaction date, account number, all of these fields I want pulled in from, and then it’s defining the actual table that we want to pull the information from.

Now, in this example, this is a view of data in SQL, so we’re calling it from account transactions and if we actually went back into SQL expanded my databases, remember, we connected to these instance of SQL, we’re connecting to this database and if I went into my views and expanded this out I would find a view called account transactions. All right, so that’s where it’s getting the information from. All of these defined our fields that I want to pull into that view. If I look at the usage tab, this is just some additional things that we can do to customize our data connection. Again, the refresh, all right, so we can enable background refresh. We can refresh this where it’ll automatically reach out, pulling new information every, whenever we want. If we want it every one minute, every 60 minutes, every, you know, 24 hours, whatever that case maybe. Or if we leave it unchecked to refresh this without changing the date, I would just go to data and I would choose to refresh and right there just refresh all of them. Okay. So I’ll click “refresh all,” now it’s going out to SQL, it’s pulling in the information, it’s going to refresh all of my data tabs, okay, and it’s going to, you know, update everything with current information, all right? And we see here running in the background queries, so there’s a lot of information on this dashboard, it’s refreshing. It’s working in my slow system, all right?

So again, looking at those data connections, you can see I have a lot of data connections that make up this dashboard, all right. So I have a lot of hidden, you know, data in the background to bring that in and that’s typically what you’d see in a dashboard. Unless you want to see the same information displayed differently, you may be able to get away with having one data connection and one data source but typically you’re going to see a lot of different, you know, pieces of information, maybe calculated in a different way, all right? So here’s a revenue reports in excel that’s playing from a data connection. Here’s are ratios, so all of these ratios are actually being calculated based off of a specific formula, all right, but it’s all being, you know, built in to excel in one specific way.

Let me jump back to my PowerPoint presentation. Because after looking at, you know, a data connection, it’s very important for us to take a look at, you know, what goes in to actually designing a dashboard? Okay. And the key component to creating a successful dashboard is planning, we need to plan ahead of time. If you just go into excel and you start building information or building data connections and pulling data into one tab and then data into another tab, before you get to actually creating a graph, you’re gonna look back and you’re gonna say, you know, why was I pulling in this information, it’s very confusing. I need something else, so planning is the biggest key here.

The questions I typically ask myself is what do you want to see on your dashboard? That’s where I start. Okay? If the answer is very straight, very specific, then I can start going in, “Well, how do you want to see that information or how will the information be displayed? Is it gonna be a pie chart? Is it going to be a bar graph? Is it going to be, you know, custom calculations or anything like that? Once I know what you want to see or once you understand what you want to see, and how the information will be displayed, what data is required. All right? This is probably going to be one of the bigger stall points for anybody getting started with creating dashboard, what data is required, and then where that data is stored, all right?

So understanding where we can find data in the SQL database, we’ll cover that lately in the live example here when we build our dashboard where you can find some of that information and then I’ll also give you some additional resources where you can go to find table listings and what’s included in those tables and where to find that information, all right? So once we have that we should be at a point, you know, we understand what we want to see, we understand how we want to see it, what data is required, and then where is that data stored. Now, we need to think does this require additional calculations to provide data not found in SQL? Okay. And then do I need to create a SQL view? Does that make sense or can I create my own calculations within a separate excel tab to get the information calculated the way I need to see it. Okay.

So building the dashboard just continuing on with those, you know, design questions that I ask myself, we’re gonna go through an example here. So what do I want to see on my dashboard? I would like to see a rolling X number of month trend of my sales, my overall sales, all right? How do I want to see this information? I want to see a bar graph with a trend line, you know, so I can look over a period of time and see, you know, are my sales going up, are they going down. What does that look like going out into the future? Anything like that. What data is required? Well, I need a detail of sales invoices for at least a rolling 12 months, all right. So that data, I know we need that in order to build that bar graph.

So now I need to think to myself, where is the data stored? And me and my, you know, approaching 10 years experience working with GP, I can rattle off, “Well, that information is stored in SQL in RM20101, open transactions, or in RM30101 which is my historical transactions, question mark, question mark, I need to go back and define on this rolling 12 months. Am I looking for more current information where I can look at open transactions? Or do I need to go back and look at the history tables and pull in historical information?

So this should be defined before I even think about going through and start building my data connection, so I can actually write tables or I can make the decision, can I create a data connection that’s going to pull in information from both RM20101 and RM30101. Or do I need to create a separate SQL view to combine those and then connect to that SQL view, all right? So in my example here I’m keeping it simple, I’m answering the questions myself, no, I can pull the data, the detailed data in graphic directly from SQLRM20101 open transactions. Okay.

So this might be a little bit redundant but I think it’s important to kind of pay attention to the process. It makes a much smoother project if we kind of go through stepping through what’s required and where the information is found. We called it analysis work before we engage in our project. And it’s the same if we’re going to be creating a dashboard as well. Understanding what information we want to see, where the information is stored from getting that basic understanding and then jumping into building. Okay?

All right. So I’m going to just switch tabs here real quick, so I have my notes in front of me. All right. And we’ll just go through. Okay. So we’re gonna step through the steps in PowerPoint and then I’m gonna re-step through these steps in excel and show you in a live demonstration of actually creating these data sources and then building at least one piece of the dashboard and then, you know, we can replicate that for each component we want to see on the dashboard. All right, so step one, we need to create the data connection. In my example I’m gonna use Microsoft Query. We’re going to create a new data source. We’re gonna chose the options that you’ll need to connect to your data. And as a reminder, you will need to know your server name and have security access to connect to that data, all right?

So we’ll walk through those pieces in my live example. Once we’ve created the data connection and return the data to excel, then we’ll actually move into creating the visual representation of your data. If you’re not as familiar with excel, there’s a lot of great training material that you can find on excel or we can certainly help provide that information as well. But we’ll be creating graphs, pivot charts, we can filter the information as needed and even add our own calculations to the data, all right?

Step three, we want to make sure our data is reporting accurately. You may have a vision or a basic understanding of what’s required order of this dashboard. We build this dashboard and we published this dashboard to all of our users or maybe it’s the CFO. And then three months down the line we find out, “Oh, we are missing a very important piece of information that actually makes this accurate.” Okay. So checking before we publish this dashboard out to users is very important and, again, we can verify that by, you know, cross-comparing the dashboard to what’s in Dynamics GP or other reports or looking up that information in SQL. It’s very, very important step.

Forms should always follow a function, that’s a pretty basic law of design, so get your information working first and then go back in and make it look pretty. Okay. So step forward as touchup, make the display look good and easy to interpret, all right? So some pretty basic, you know, steps to building the dashboard. I think once we go through the example, you can have a much better understanding of what this looks like, all right? So what I’m going to do is I’m going to jump back into my development environment here and I’m going to start a new excel spreadsheet and using that example that we went through before where I went through these steps, the considerations to designing my dashboard, what do I want to see. If you remember, I said I want to see a rolling month trend of my sales. Okay. This request might be in your brain, it might be in the CFO’s brain, it might be in somebody else’s brain. So defining what is needed to be seen, that’s the first step.

And then defining that a little bit further how do you want to see this? Again, we’re going to be ultimately ending up with a bar graph. Okay. That’s one of the components of our dashboard. All right, and then we also define that, you know, we want to see details of sales invoices for a rolling 12-month. All right, so to answer this question a little bit further for those of you who aren’t as familiar, within Microsoft Dynamics GP I might know that the information I want to see I can find that in receivables transaction inquiry, okay, as an example. All right, so I can see the information I want to see, here’s all of my work in open transactions and it gives me a list of it, where is this window getting this information from. That’s a really good place to start by answering the question, where is the data stored. Okay.

So in GP I can see the information, where is this system getting this information from. There’s a nice little tool in all of these windows under integrate called Table Import. Okay. Table Import is going to give me a window name, it’s gonna be the window that you’re in and it’s gonna tell me the window tables, all right? So I can kind of scroll though here and I can see all of the table names that this window was using to get its information, customer master, RM Open file, RM history file. Okay. So these are all good indicators right here of where I want to start looking for my information. I happen to know the RM work file is where I want to start. I don’t want to see history, I just want to see work or open transactions. So I’m gonna start with this RM Open file. I’m gonna click “cancel” there and if I go into SQL and I started looking around in SQL, all right, expand all my tables, I’m not gonna see anything in here that says RM Open file. I’m going to see numbers and letters that represent RM worker open. So that’s gonna push me back into my window and I’m gonna go to resource description. And I’m gonna go to windows, I’m actually gonna go to tables, all right?

So now I can start looking for from the sales module by table display name. I’m gonna look for RM Open. And as I kind of go through here I know I can. All right, so here’s RM Open, RM Open file. That was the display name of the table that I want to get my data from. The physical name is what’s going to be the SQL dbo.RM20101 and the table group is Receivables Open Transaction Files, all right.

So that’s how I’m kind of drilling in or using GP to tell me where that data is in SQL if I don’t know where that’s being held automatically, all right. So with just this information from Dynamics GP, I could write a select statements, select * from RM20101 and it should return all the information that’s being held in that SQL database. To prove that point, instead of an excel, I can type the same thing in SQL server management studio. Okay. By creating a new query, I can kind of test my query and I can just Select * from RM20101 making sure I have the right company database selected and then I can execute that query and boom bam, it’s returning data. So this is the data that’s actually in that table and I can come in here and I can validate.

Well, does this really have the information I want to build my dashboard out of, okay? And it does, you know, it has the customer name, it has the transaction source, it has document number, it has document date, it has a whole bunch of information that I want to pull into my report. So I have the original transaction amount, the current transaction amount which means that it’s been paid already. There’s the sales amount, there’s the cost amount, tax amount, so all of this information is here. It’s good. That’s how I’m identifying where I want to pull the data from. Main point of that little blurb is that if you’re having trouble finding where data is stored in Dynamics GP SQL, you have the tools right in Dynamics GP, then integrate Table Import gives you a listing of all of the tables that this window is pulling that information from. And then we also have this resource description where we can actually look at the tables based off of those table names. Or I can go into windows here, okay, and I can pull the information in for windows.

This is a fresh install, but here I can go back into sales, and window display names. I can look up that window and then it will tell me the table names here as well. So either way that information is readily available. All right. So going back into excel and we’re gonna start building this dashboard. We know where the information is gonna be coming from, the first thing we need to do is we need to get that data connection built. So if I go into my data on that ribbon up on top I can see here that I can get external data from this group, all right? And I like to typically choose from other sources because this is what’s going to give me the ability to connect to the SQL server, so from SQL server. If I have other analysis services like SQL Server Analysis Services Cubes, I can pull information from there, the Azure marketplace, oData, Data Feeds, XML Data Imports, from Data Connection Wizard or from Microsoft Query. Microsoft Query has been around for a long time. A lot of people are familiar with it. It’s easy to use, so that’s the one we’re going to use in this example. When I select that it’s going to kind of walk me through a little bit of a wizard to help me create that data connection. It’s going to say, “Hey, we have all of these data connections already created, do you want to use one of these or do you want to create a new data source.”

I’m gonna select new data source and I’m gonna click okay. It’s going to say what name do you want to give this data source and that’s what’s going to display in this listing. So I’m going to say Training AR2, so I’ve already gone through this to make sure that everything works. The next thing we need to do is we need to select the driver type for the database and when we drop this down if you’re not familiar or comfortable with these pieces, okay, you might look at this and say, “Oops, this is out of my wheelhouse, I’m done.” But don’t let this stop you. If you’re connecting to a SQL database, I typically recommend selecting the highest SQL Server Native Client, okay, in my example it’s 11.0, all right, as your driver to connect to that information. Your network might tell you otherwise and I mean you’ll run into an error trying to connect to the data. It’ll tell you, you know, there’s something wrong with the driver and then you know we need to go back in and then select another one, but for novice users, I would typically recommend just selecting the SQL Native Client, the highest number that’s available to you. Okay.

Click “Connect” and enter any information requested by the driver, right? So this driver is going to say, “When I try and connect to that database, what do you need to see?” Server. So the first thing we need to know is we need to know what the name of the server is. If you remember on that data connection properties it requires that server name. All right, so that’s where we’re going to enter in our server name and I’m just going to quickly write on a post note WIN-PPQPGEO50AC, that’s the name of my server. Jumping back here I’m going to type in just as I wrote down WIN-PPQPGEO50AC, all right.

We have a checkbox that says used trusted connection. If we use trusted connection what it’s going to do is it’s going to automatically validate your windows user ID against the SQL database. And if I haven’t set up my windows users in the Dynamics GP company database I’m trying to connect to it’s going to fail authentication, so I go to users in here and I just take a look and I see who’s kind of setup in here, so at DBO that’s actually who I’ll connect with. I have a couple windows users or domain users that I could use to authenticate if I were logged on to this computer with one of these user names, it would authenticate. Otherwise, I would probably want to use trusted connection so I would have to log in as, you know, my Dynamics GP user Jason to get to this data, all right.

In this situation I’m not going to click the “use trusted connection” I want to authenticate every time I log into this data connection. So I’m going to just use my SA log in and SA password because I know they have access to that information and I’m gonna click “Okay” and now my data connection is authenticating. And then I come in here and I can select some default tables if I wanted to, I’m going to leave that unselected. Okay. So now I’ve created that data connection source and I’m going to select it and I’m going to click “Okay,” all right. And now what it’s going to do is it’s going to bring me into Microsoft Query. So I’m connected to SQL now, now I need to build my query, all right.

So the first thing I want to do is I want to select my database and I’m gonna look through here and I’m going to find my company database which is TWO, that’s where all my company information is. It’s gonna refresh my table listing and I’m gonna go through and I’m going to select my RM20101. Here we go. All right, so there’s my table. I’m going to add. All right, so now I can start dragging and dropping the fields I want. So on this one I’m going to keep it really simple, I’m going to go through customer. I want to go through my document number and we’ll put in original transaction amount to show me what that actual original transaction amount is. So really simple, you can choose what fields you want. Within Query, you know, Microsoft Query, you can add additional tables, you can link those tables together. You can use this tool to build this query.

All right. So my next step once I have the information I want to see I’m going to return data that’s that little door that has the arrow pointing out. I’m gonna return data and it’s gonna say, “Okay, where do you want to put this data?” Do I want to put this in a table, do I want a pivot table report, do I want a pivot chart,” I have all these options. I’m going to return the data in a new worksheet. Okay. Right now, that data connection is created, it’s working, it’s returning the information to my sheet for me. Okay.

And on this, if I actually go to data, go to connections, I’m going to see that I now have a data connection going to my properties, that wizard created my connection string, all right, it is already created my command text so it’s selecting RM20101.customer, RM20101.document number, so that text, the command text has already been created for me. I’m now ready to go and see it’s trying to authenticate again, all right? And I’m going to go and close that. And now I might rename this tab, just to make sure it makes sense. So this is going to be my data tab. And then I would rename this one, “My Dashboard.” All right, perfect.

So now what I can do is now that I have the data, I can start building by inserting all the charts that I want to see. So I might want to call them chart, my data range I’m going to specify. I want all this information here in my bar graph. Take my spark line off for now. We’ll actually see, just let me do it, cancel. All right. I must have broken something so what I’ll do is I’ll open up the one that I have finished already. In either example here under my sale dashboard, we can see on the dashboard here that I’ve taken something like my receivables age from an outstanding data connection just like this and basically I’ve created the bar graph and aligned it under my dashboard just as something like this. So it would show there on this pivot chart and the information will be shown, all right.

So now I have one component of my dashboard completed. If there are additional things I want to see maybe I can use that same dataset to create something like my top 10 customers or my top 5 customers. I can use that same data, that same information just by creating a different type of graph moving it into an area on my dashboard, all right, and just visually seeing that information in a different way, right? You may have something else you want to see that’s maintained in a different table, that’s when you can go through the same steps we just went through creating a new data connection, putting the data in a different tab and then building another chart off of that dataset instead of the first one. Okay.

All right, so we’ve gone through step one creating the data connection. We’ve gone through step two creating the visual representations of the data. Step three is taking that time to making sure your data is reporting correctly. Okay. So I might, you know, once I’ve created outstanding balances, I might want to take a look at the data that was returned. I might want to go into Dynamics GP running separate report and cross-compare it to to make sure the information is correct. If this dashboard is coming from like a request from another user, maybe work with them one on one to say, “Hey, this is what we built, this is the calculations we’re using, is this the information you want to see?” Try and get more than one eye on it. Make sure the information is correct now because as soon as you publish this dashboard, you’re gonna begin relying on this information that it’s correct. And again, you may have made a business decision based off of this information. If you find out six months down the road that, “Hey, we really should have been calculating something based off of 10% versus 8%,” you know, we want to make sure that that’s all correct ahead of time.

Step four is touching up the dashboard. You know, once we know everything is correct and everything looks good, now we want to make it look nice. So keeping everything kind of lined up in a specific order if I go into my view tab and I view, you know, some of those default things, this is what your dashboard’s originally gonna look like. You may want to come in to view, hide the grid lines, hide the headings, hide the formative bar. If you want to you can even hide, you know, all of this information up on here so that all you do when you open up this excel spreadsheet is you see your dashboard, right? Maybe you wanted to fill the screen or maybe you want to look good minimized, whatever, you know, whatever you want to look, how you wanted to look, now is your opportunity to kind of touch it up and make it look nice and make it look useful. Okay.

Once your dashboard is created, you can close it or save it first obviously, first rule in excel is save, save, save. Save that and when you open up your dashboard, it’s automatically going to typically ask you “external data connections have been disabled, do you want to enable your content?” Now, those data connections are ready and available for you to refresh based off of your criteria, all right?

So that’s my kind of live overview of building a dashboard and you might be going, “Well, that’s a lot more than I thought I was going to be. I thought I’ll just be able to kind of click, click, click, and get a dashboard.” It does take a little bit of work, but there are a few easy things that I want to be able to show you that will get you started quickly and using dashboards and even creating your own customized dashboards. You know, right out of the box when we’re done with this webinar, you’ll be able to walk away and be able to start using dashboards. So the quick start guide section, first of all, you’re closer than you think to using dashboards. Dynamics GP comes loaded with several excel dashboards out of the box that you can start using right away.

What we have to make sure is excel reports have to be deployed at first and that’s at a system level. So during your installation of GP if the time was taken to deploy your excel reports, great, you’re one step closer to using them, all right. So each module has a navigation list option called report or excel reports where you can find the report named Dashboard. So in my little screenshot here we can see that under the sales module in the navigation pane and if you’re not familiar with navigation list, well, we should probably do a webinar on those because these are awesome for Dynamics GP, but you can run that navigation list for excel reports. It will give you a listing of all the excel reports that have been deployed in your system. You’ll see for the sales module we have a sales dashboard, double click it, it opens up the dashboard. It already has all the data connections built in. And that’s a dashboard you can start using today. Okay.

Additionally, we have a whole listing of excel refreshable reports remembering that those data connections could be exported. So if you have five different excel refreshable reports that you want to combine into one dashboard. You can export those data connections, import them all into one workbook, build your charts off of that data and now you have a dashboard. Okay. Also if you’re familiar with SmartList Designer, Smartlist Designer can actually publish any SmartList design or Smartlist that youve built as long as it’s built off of a SQL view. You can publish that as an excel refreshable report, all right. And, again, the beauty of the excel refreshable reports is you don’t have to log in to GP to open up that excel refreshable report, get your data out of the system without having to log into GP and run any specific report. Okay. That’s all independent. I can run all of my excel refreshable reports just by opening up excel, all right.

So with that we’ll actually walk through that example. I’m back in Dynamics GP, I’ve selected sales and I’ve selected excel reports and the reason why I’ve done this ahead of time is my navigation list in my development system take a little bit longer to render, so instead of dead air space I already had that selected. And here I can see the sales dashboard. I can double click on that, it’s gonna go ahead and it’s gonna open that up for me. These have already been deployed in a repository, so it’s just accessing that from the repository where my excel reports have been deployed. Now, that I have this up on my system I can just do a file, I can do a “Save As” and I can save this on my local desktop, I can put this on my share drive, so other users can open it and use it as well. But that dashboard is there and readily available for me. If I don’t like something on this, I can change it based off of the data I already have. Okay.

So maybe I don’t want to see, you know, my top 5 customers, maybe I want to see my top 10 customers instead. Or maybe I want to move this information somewhere else. Okay. I maybe want to see this over here and this here. I can move all this around, I can save it, that’s just fine. I can kind of customize this. And also, if I wanted to create a new data connection and pull in additional information, I can always come in, I can get information from other sources, so this is just a place for you to start take a look at what was done and customize and tweak it a little bit to meet your own needs.

How do I tell if excel reports have been deployed in my Dynamics GP system? Well, first of all, if I were to run this excel reports navigation list, it would show results. Okay. So I would actually see results come in. If they haven’t been deployed, I wouldn’t see anything. If I go into tools set up system, go to reporting tools setup, this just takes half a minute to open, all right. Here’s where reporting tools setups, excel reports, this is where I can see my location, network share, all right, this is where I’ve deployed my reports dictionary, too, Encore GP 2015, Dyndata, excel reports. I can actually go to that location and I could see all the extra reports that have been deployed, all right. So if I go to my C drive, go to Encore 2015 Dyndata, excel reports, all right. Here is where I could see my reports on my data connections. Go to sales.

All right. So there’s all those excel refreshable reports and I would see that sales dashboard as well, all right, another way to get to it. If these have not been deployed, then you can simply mark and we can deploy reports. All right, so we can just simply select and deploy reports and get all those extra reports deployed as long as we have access to the installation media, all right, so that’s all at your fingertips. And I know we’re running really close to time here, we got seven minutes. So let me just do a double check here and make sure I run through all my material. I’ve given you an overview of the information. I’m building those dashboards. Yep, perfect. We’re at Questions. Six minutes remaining here if there are any questions I’d be more than happy to answer what I can through the webinar here if we can answer them we will certainly be happy to do a follow-up and try to answer those questions as well.

Jess: Great. That was great, Jason. Thank you so much. We did have one, not so much a question but a small issue that came through and just something I thought I would bring up because it might be beneficial for others, is during the webinar it looks like one of our attendees is looking through their own excel and saw that they believe that from other data source option was actually in a different area, so maybe good call out for people who have different versions of excel than what you’ve showed. Is that option still available in older versions or different versions?

Jason: Yeah, absolutely, yup. I don t know how far back but I do know, you know, I’ve had the ability to do data connections using Microsoft Query for quite a long time. So, you know, again, depending on the version of excel that you’re on you may have to do a Google search to find out where that, you know create data connection is actually at. Otherwise, if you’re running into problems certainly we can do some assistance or help find that information for you on creating those in the version of excel that you currently have.

Jess: Perfect, okay. That’s great. I just thought that might a good callout. I want to also just mentioned to everybody that yes, right after this webinar I will be sending out Jason’s slide deck as well as a link to the recording. Feel free to share any of those items. This is all free training that we want to provide to you and your colleagues so feel free to get those out. Also I wanted to mention again that we do have a lot more Dynamics GP focus webinars coming up as I mentioned before those are on our website so do go to encorebusiness.com and look for that events link in that upper right-hand corner and you’ll see all of the upcoming webinars and as I mentioned before some in-person events if you are in the Winnipeg, Vancouver, BC, Seattle, Washington, or Portland, Oregon areas.

And here is also Jason’s information, just popped up on that slide. Feel free to reach out to him directly. You’ll also get my information in that subsequent email. If you have any ideas for future webinars, items that you don’t see on our upcoming list that you would like to see, I think as Jason even called out another item while he was presenting that might be a good topic for us to focus on, things like that that you saw you’d like a deeper dive on, please do let me know. I’m constantly working with our resources to provide content so we can just get started on just about anything you’re interested in. It does definitely help to know where the interest is so we know where to focus our efforts.

And Jason, I don’t see any other question that has come through but as I’ve said they’ll be getting your contact information. Is there anything else you want to add?

Jason: Yeah, absolutely. Get in there and get your hands dirty. This is something I’m very passionate about. I love working with excel and creating these dashboards, creating these reports, and it’s definitely a hands-on thing that you can go in and start working on. Have fun with it, be creative, it’s definitely, if you’re my type of a person it’s definitely a fun thing to do, so absolutely.

Jess: Very cool. Okay. Well, no other questions that come through, if you have any questions, as I said, reach out and hope to see you on another webinar. Thanks so much for attending today and hope you have a great day.