The first edition of Building Dashboards with Microsoft Dynamics GP 2013 and Excel 2013, written by Mark Polino, is an amazing tool for building dashboards in Excel. Since the release of this book, Excel has continued to increase its value to the GP Community. With GP having gone through two major updates and Excel having gone through one major update, Mark felt it was time to refresh his book. Luckily, Mark chose me to take the lead on the updating process.
Those of you who know Mark and me know that although we are close friends, we love to challenge each other and ourselves. We prove this year after year in an Excel Deathmatch we call The Excel Shootout. We invite another contestant for the audience, but it's really a duel between Mark and me. The same thing in our souls that force us to challenge ourselves made us think, what if we added some other Excel and dashboarding elements? The result: this second edition, including both the new free Jet Express for GP and Microsoft Power BI. More Excel, more dashboards, and more cow bell. Sorry for the cow bell joke, that was just for Mark.
Can you use this book if you do not have Dynamics GP? Of course! Although, the data samples come directly from GP, the Excel and Power BI sections are generic for any data. The Jet Express for GP section will work only for Dynamics GP and Dynamics NAV.
Chapter 1, Getting Data from Dynamics GP 2016 to Excel 2016, covers the first step to building a report or a dashboard and getting data. In this chapter, we discuss the many options of getting GP Data (and in some cases, any SQL data) into Excel.
Chapter 2, The Ultimate GP to Excel Tool – Refreshable Excel Reports, walks us through using prebuilt Excel reports in GP. This chapter also covers two other important topics—granting security to the GP (SQL) data and how to refresh the data in the reports once they are built.
Chapter 3, Pivot Tables – The Basic Building Blocks, explains the most important element of the dashboard, the Pivot Table. The Pivot Table is such a powerful tool; every Excel user should make it their go-to tool of choice.
Chapter 4, Making Your Data Visually Appealing and Meaningful with Formatting, Conditional Formatting, and Charts, guides you through formatting options. Formatting is more than just making a chart pretty. What's more valuable when you are driving in your car, the gas gauge or the number of miles you can drive with the amount of gas you have?
Chapter 5, Drilling Back to the Source Data and Other Cool Stuff, helps you add credibility to your report. Being able to look at the data in its source with a single click will make you the office champion. This chapter also covers slicers and timelines, which are essential for creating focus on your data.
Chapter 6, Introducing Jet Reports Express, explains not only why you would want to use this report to create basic financial statements inside Excel, but also why they are giving their product away for free. We'll even build an Excel-based General Ledger Trial Balance.
Chapter 7, Building Financial Reports in Jet Express for GP, walks us through building a simple Balance Sheet and a simple Profit and Loss report. Using the foundations learned in the chapter, you can become a lean, mean financial report writing machine.
Chapter 8, Introducing Microsoft Power BI, acquaints us with Microsoft's new pride and joy, Power BI. This chapter is an overview of the product itself. Understanding where and how the reports are consumed is essential in planning how to build them.
Chapter 9, Getting Data in Power BI, reviews (only) some of the options of getting data into Power BI.
Chapter 10, Creating Power BI Visuals, will probably be your favorite chapter. In this chapter, we will actually create the charts, cards, tables, and other visuals that display our data.
Chapter 11, Using the Power BI Service, is where we learn how to publish and consume our data on the Web and, therefore, our mobile devices. We will even learn how to combine individual visuals on different reports to make a single dashboard.
Chapter 12, Sharing and Refreshing Data and Dashboards in Power BI, followed by the summary of this chapter.
Chapter 13, Using the Power Query Editor, is probably the most important chapter in the Power BI section. Rarely our data is formatted (or modeled) exactly the way we need for reports. It could be that we just want to combine data from our GP with data from our CRM. This chapter covers how to edit or model our data.
Chapter 14, Bonus Chapter, is kind of the proverbial kitchen sink. We'll see two additional features for Excel—Jet Express for GP and Power BI. This was just for fun!
The following list is software prerequisites that are required:
Microsoft Office 2016 Professional Plus or Microsoft Office 365 Business
Microsoft SQL Server 2012, 2014 or 2016
Microsoft Dynamics GP 2016 with the Fabrikam sample company deployed
A web browser for links
Optional—being a data nut like me!
This book is for the person that always gets asked questions about their GP data. How much cash do we have? What's the Accounts Payables and/or Receivables balance? Who have we sold our product to? What's in Inventory? You get the point. Basically, if you spend time digging through data for answers, this is for you.
This book is also for the forward-thinking individuals who want to stay ahead of trends and competitors and get the raise they deserve.
In this book, you will find a number of text styles that distinguish between different kinds of information. Here are some examples of these styles and an explanation of their meaning.
Code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles are shown as follows: "The Dex.ini file is located in the Data folder of the Dynamics GP installation directory."
A block of code is set as follows:
=GETPIVOTDATA("Period Balance",Revenue!$A$3,"Year",$E$5,"Period ID",E$6)New terms and important words are shown in bold. Words that you see on the screen, for example, in menus or dialog boxes, appear in the text like this: "We need to turn on Developer ribbon in Excel. In Excel 2016, go to File | Options | Customize Ribbon."
Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or disliked. Reader feedback is important for us as it helps us develop titles that you will really get the most out of.
To send us general feedback, simply e-mail <[email protected]>, and mention the book's title in the subject of your message.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide at www.packtpub.com/authors.
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
We also provide you with a PDF file that has color images of the screenshots/diagrams used in this book. The color images will help you better understand the changes in the output. You can download this file from
Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you could report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/submit-errata, selecting your book, clicking on the Errata Submission Form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded to our website or added to any list of existing errata under the Errata section of that title.
To view the previously submitted errata, go to https://www.packtpub.com/books/content/support and enter the name of the book in the search field. The required information will appear under the Errata section.
Piracy of copyrighted material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works in any form on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.
Please contact us at <[email protected]> with a link to the suspected pirated material.
We appreciate your help in protecting our authors and our ability to bring you valuable content.
If you have a problem with any aspect of this book, you can contact us at <[email protected]>, and we will do our best to address the problem.