39,59 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
Accounting systems like Microsoft Dynamics GP 2013 hold a wealth of information. Excel 2013 provides a great tool for linking to, extracting, analysing, and presenting that rich data to help companies make better, faster, and smarter decisions.Building Dashboards with Microsoft Dynamics GP 2013 and Excel 2013 covers how to get the rich, detailed information contained in Microsoft Dynamics GP 2013 and present it in an attractive, easy-to-understand way using Excel 2013. The book shows in detail how to build great-looking dashboards that enhance a company's decision-making process.This book shows you how to get at the rich, detailed information contained in Microsoft Dynamics GP 2013 and present it in an attractive, easy-to-understand way using Excel 2013. This guide will take you from the basics of setup and deployment to creating secure, refreshable Excel reports. Using a whole host of tools available within Excel, this tutorial will show you how to visualize your data using simple conditional formatting techniques, easy-to-read charts, and allow you to make your data interactive with Slicers.
Building Dashboards with Microsoft Dynamics GP 2013 and Excel 2013 provides a way for you to easily build that interactive dashboard that your CFO keeps asking for.
Das E-Book können Sie in Legimi-Apps oder einer beliebigen App lesen, die das folgende Format unterstützen:
Seitenzahl: 231
Veröffentlichungsjahr: 2013
Ähnliche
Table of Contents
Building Dashboards with Microsoft Dynamics GP 2013 and Excel 2013
Building Dashboards with Microsoft Dynamics GP 2013 and Excel 2013
Copyright © 2013 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.
First published: March 2013
Production Reference: 1180313
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham B3 2PB, UK.
ISBN 978-1-84968-906-9
www.packtpub.com
Cover Image by Abhishek Pandey (<[email protected]>)
Credits
Author
Mark Polino
Reviewers
David Duncan
Jivtesh Singh
Acquisition Editor
Martin Bell
Lead Technical Editor
Mayur Hule
Technical Editors
Kaustubh S. Mayekar
Dominic Pereira
Project Coordinator
Esha Thakker
Proofreader
Jonathan Todd
Indexer
Monica Ajmera Mehta
Production Coordinators
Pooja Chiplunkar
Manu Joseph
Cover Work
Pooja Chiplunkar
About the Author
Mark Polino is a Microsoft MVP for Dynamics GP, a Certified Public Accountant, and a Microsoft Certified Information Technology Professional. He is the author of the premier Dynamics GP related blog at DynamicAccounting.net and the creator and presenter of the successful presentation series 50 Tips in 50 Minutes for Microsoft Dynamics GP. Mark has worked with Dynamics GP and its predecessor, Great Plains, for more than a dozen years.
He is also the author of the best-selling Microsoft Dynamics GP 2010 Cookbook, and the spin off Lite edition, both from Packt Publishing.
To my wife Dara and my children Micah and Angelina, thank you again for letting me take on another crazy project.
I want to offer a huge thank-you to Andy Vabulas, Dwight Specht, and Clinton Weldon of I.B.I.S., Inc. for their support. This book would not have been possible without their commitment to Dynamics GP.
To David Duncan and Jivtesh Singh who were kind enough to serve as reviewers for this book, thank you again for all of your support and suggestions. This is a much better book because of you.
About the Reviewers
David Duncan is a senior consultant with I.B.I.S., Inc., a Microsoft Gold Certified Partner based in Peachtree Corners, GA. David, who holds several certifications for Microsoft Dynamics GP and SQL Server, is also the co-author of another Packt Publishing title, Microsoft Dynamics GP 2010 Reporting. In addition to experience with implementing Dynamics GP, he has extensive experience in designing and providing business intelligence and reporting tools for clients who use Dynamics GP and Microsoft SQL Server. David has also served as a content provider for the GP portion of the Sure Step 2010 Methodology.
He has developed custom SSAS cubes for several GP modules such as Project Accounting and Fixed Assets that seamlessly integrate with Microsoft's Analysis Cubes for Excel product. David's combined experience with Dynamics GP and Microsoft SQL Server has enabled him to assist numerous clients in analyzing their strategic business plans by designing business intelligence solutions that allow them to incorporate data from multiple applications into a single reporting environment.
David, who holds a degree from Clemson University, resides in Rocky Mount, N.C. with his wife, Mary Kathleen.
Jivtesh Singh is a Microsoft Dynamics GP MVP, and a Microsoft Dynamics Certified Technology Specialist for Dynamics GP. Through his blog, which is widely read in the Dynamics GP community, he covers Dynamics GP tips and tricks and news.
He is a Dynamics GP Consultant and Systems Implementer and has been associated with the Microsoft Technologies since the launch of Microsoft .NET framework. Jivtesh has over 10 years of experience in development and maintenance of enterprise software using coding best practices, refactoring and usage of design patterns, and Test Driven Development. Jivtesh recently built a Kinect interface to control the Microsoft Dynamics GP 2010 R2 Business Analyzer with gestures. Later, he built a part of the GP Future demo for Convergence GP Keynote.
Jivtesh has set up a custom search engine directory for Dynamics GP Blog at www.gpwindow.com to help with easier access of Dynamics GP resources for the GP Community. With MVP Mark Polino he has also set up a Dynamics GP product directory at www.dynamicsgpproducts.com. Here are his blogs and website:
www.PacktPub.com
Support files, eBooks, discount offers and more
You might want to visit www.PacktPub.com for support files and downloads related to your book.
Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.PacktPub.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at <[email protected]> for more details.
At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a range of free newsletters and receive exclusive discounts and offers on Packt books and eBooks.
http://PacktLib.PacktPub.com
Do you need instant solutions to your IT questions? PacktLib is Packt's online digital book library. Here, you can access, read and search across Packt's entire library of books.
Why Subscribe?
Free Access for Packt account holders
If you have an account with Packt at www.PacktPub.com, you can use this to access PacktLib today and view nine entirely free books. Simply use your login credentials for immediate access.
Instant Updates on New Packt Books
Get notified! Find out when new books are published by following @PacktEnterprise on Twitter, or the Packt Enterprise Facebook page.
Preface
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:
What this book covers
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.
What you need for this book
The following show the software prerequisites that are required:
Who this book is for
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.
Reader feedback
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.
Customer support
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.
Downloading the support files
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.
Errata
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
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.
Questions
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.
Chapter 1. Getting Data from Dynamics GP 2013 to Excel 2013
Microsoft Dynamics GP 2013 is a terrific enterprise reporting package. But when it comes to analyzing data, few tools can compare to the power of Microsoft Excel. When you put the two together and use Microsoft Excel to analyze the data collected in Dynamics GP, you can build something magical. By magical, I mean a dashboard that the CFO keeps asking about. Together, we will explore using the power of Excel 2013 and GP 2013 to build a straightforward dashboard.
We are going to build a great-looking, financial-oriented dashboard. Don't worry; we won't be doing any programming, and there are no Excel macros. This dashboard is built using nothing but native Excel functionality such as charts, pivot tables, and conditional formatting.
Before we can build a great Excel-based dashboard, using the data in Dynamics GP 2013, we have to get the data out of GP and into Excel. This chapter covers nine major ways to get data from Dynamics GP into Excel with a few extra options thrown in at the end. Some of these methods pull data from the interface in Dynamics GP; others bypass the interface and pull data directly from SQL Server. Generally dashboards are designed to pull from the database for the best performance, but sometimes you have to use what you have access to. In Chapter 2, The Ultimate GP to Excel Tool: Refreshable Excel Reports, we will start building the dashboard using my favorite way to get data out of Dynamics GP refreshable Excel reports.
By the end of this chapter you should be able to get data into Excel using:
We will briefly look at some third-party options, and you'll also learn about licensing requirements around using Dynamics GP 2013 data with Microsoft Excel.
SmartList exports
Exporting from a SmartList to Excel is the easiest and most commonly used method in Dynamics GP to get data to Microsoft Excel. We'll practice with an Account Summary SmartList.
To export from a SmartList to Excel, follow these steps:
Tip
In 2010, Microsoft revealed a previously unreleased Dex.ini switch that can dramatically improve the time it takes to export SmartLists to Microsoft Excel. The Dex.ini file is a launch file used to control system behavior, and this switch changes the behavior of an Excel export. Instead of sending data to Excel one line at a time, the switch tells Dynamics GP to bundle the SmartList lines together and send them to Excel as a group.
This switch is unsupported and can render the results differently than the default export process. Please test this in your test system before using in production. The Dex.ini file is located in the Data folder of the Dynamics GP installation directory. To use this switch, add the following line to the Dex.ini file and restart Dynamics GP:
SmartList Export Solutions
While SmartList exports are great for sending Dynamics GP data to Excel for analysis, they aren't an ideal solution for a dashboard. SmartList sends data to a new Excel file each time. It's a lot of work to export data and rebuild a dashboard every month. An improved option is to use a SmartList Export Solution.
SmartList Export Solutions let you export GP data to a saved Excel workbook. They also provide the option to run an Excel macro before and/or after the data populates in Excel. As an example, we will format the header automatically after exporting financial summary information.
Getting ready
We have a little setup work to do for this one first. Since these exports are typically repetitive, the setup is worth the effort. Here is how it's done:
Creating macros
A SmartList Export Solution allows you to run an Excel macro before or after the data arrives to format or manipulate the information so you only have to do it once. Let's record our Excel macro.
Creating an export solution
Now that we've prepared our Excel 2013 workbook to receive a SmartList, we need to set up and run the SmartList Export Solution:
Tip
There is a length limit of eighty (80) characters for the document name and path. This can be a little on the short side, so it can be difficult to point an export solution to a file deep in a network file tree.
Navigation List export
Dynamics GP includes a feature called Navigation Lists. These lists provide centralized information views and access to tasks around common areas. For example, the Account Transactions List includes the ability to review journal entries, drill back to additional information, and enter transactions, all from a single screen.
Navigation Lists don't have a large Excel button like SmartLists, but they do include the ability to export to Excel.
To export a Navigation List to Excel:
Tip
Like the Dex.ini switch that can improve exports to Excel from SmartLists, there is a similar, unsupported switch to speed Navigation List exports to Excel. To activate this switch, add the following line to the Dex.ini file and restart Dynamics GP 2013. The same caveats apply; test this on a test server first.
Report writer
The core reports in Microsoft Dynamics GP 2013 are still rendered using the included Report Writer application. More and more reports are available in formats that make it easy to bring them into Excel, but sometimes the information you need is most easily accessed via a Report Writer report. This is particularly true for reports that use temporary tables as part of the report generation process.
Report Writer can't export directly to Excel, but it can export to a comma-delimited file, a tab-delimited file, or a text file, any of which can be brought into Excel.
To demonstrate getting Report Writer data in Excel:
For longer reports, you may need to sort the report to push page headers and footers together to make it easier to delete them.
