Welcome to Building Dashboards with Microsoft Dynamics GP 2013 and Excel 2013. Executives today want information faster and in an easily digestible format. That's where a dashboard comes in. The idea is to present key information that's timely and easy to understand. In this book, using the power of Microsoft Excel 2013, we cover the process of building an easily refreshable dashboard with information from Microsoft Dynamics GP 2013.
Throughout the course of this book, we're going to build a dashboard that looks like the following screenshot:
Chapter 1, Getting Data from Dynamics GP 2013 to Excel 2013, looks at nine major ways to get data out of Microsoft Dynamics GP and into Excel as a source for our dashboard.
Chapter 2, The Ultimate GP to Excel Tool: Refreshable Excel Reports, will walk us through the time spent with one of the best and simplest options for getting information from Dynamics GP into Excel 2013 the Excel reports included with GP 2013, after looking at all the other options.
Chapter 3, Pivot Tables: The Basic Building Blocks, will discuss the basic building blocks of any dashboard that are pivot tables. These tables summarize and group data in ways that make analysis easier. They are the core that the graphical elements rely on.
Chapter 4, Making Things Pretty with Formatting and Conditional Formatting, will explain Excel's conditional formatting that provides ways to add additional context to pivot tables and other elements by adjusting the way things look based on the information. Nothing spices up a pivot table like adding some conditional formatting.
Chapter 5, Charts: Eye Candy for Executives, will enable us to use a picture that is worth a thousand words. The right chart could be worth millions if it helps executives make the right decision. Charts provide the connections and revelations that are to present with just text.
Chapter 6, Adding Interactivity with Slicers and Timelines, will provide guidelines on a static dashboard that is just a fancy report. Users need the ability to interact with the information to discover new insights. Slicers and Timelines provide that controlled interaction.
Chapter 7, Drilling Back to Source Data in Dynamics GP 2013, will walk you through the great thing about dashboards that often provokes more questions. Questions that require details. Adding the ability to drill back to the detail behind the numbers adds tremendous credibility. It's even better when that drill-back takes you right to the transaction in Dynamics GP 2013.
Chapter 8, Bringing it All Together, will help us to finish up our dashboard, tie up all the loose ends, and really make it look good.
Chapter 9, Expanding Pivot Tables with PowerPivot, will explain us that just because our dashboard is done doesn't mean that we're finished. PowerPivot is an advanced Excel 2013 feature that takes pivot tables to places you can't imagine. You might not use them for your first dashboard, but you'll want them for your second one.
Chapter 10, Slightly Crazy Stuff, will acquaint us with the nature of this book, building a dashboard together, means that some things didn't quite fit for a specific dashboard but are useful for other scenarios. Those items get covered here.
The following show the software prerequisites that are required:
Microsoft Office 2013 Office Professional Plus is currently required for the PowerPivot functionality (blame Microsoft for the last-minute change)
Microsoft SQL Server 2008R2 or 2012
Microsoft Dynamics GP 2013 with the Fabrikam sample company deployed
A web browser for links
A willingness to think a little creatively
Caffeine; if you really get into dashboard building, it can be a little obsessive
This book is for the person that the CFO keeps asking about building a dashboard. It's for the controller, the analyst, or the senior accountant who knows that there is a treasure of information hiding in Dynamics GP, if they can just get at it. It's for the Excel power user who is tired of being held back by exporting data from GP and rebuilding information every month. If you're ready to start getting as much information out of Dynamics GP as you put in, this is the book for you.
In this book, you will find a number of styles of text 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: "Save this file to your desktop as PowerPivotSample.xlsx."
A block of code is set as follows:
create procedure dbo.seeGLCurrentRatioKPI @UserDate datetime = NULL,
@TimeUnit varchar(10) = 'Period'
as
declare @CurrentRatio numeric(19, 5),
@PreviousCurrentRatio numeric(19, 5),
@LastYearCurrentRatio numeric(19, 5),
@CurrentBalanceAssets numeric(19, 5),
@PreviousBalanceAssets numeric(19, 5), New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: "Select the first option on the top right, Clustered Column – Line and click OK."
Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.
To send us general feedback, simply send an e-mail to <[email protected]>, and mention the book title via 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 on www.packtpub.com/authors.
Now that you are the proud owner of a Packt Publishing book, we have a number of things to help you to get the most from your purchase.
You can download the code support files for all Packt Publishing books you have purchased from your account at http://www.packtpub.com. You can also download the dashboard application along with the code bundle of this book. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.
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 would 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 on our website, or added to any list of existing errata, under the Errata section of that title. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.
Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt Publishing, 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.
You can contact us at <[email protected]> if you are having a problem with any aspect of the book, and we will do our best to address it.