29,99 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Fachliteratur
- Sprache: Englisch
Microsoft Dynamics GP is a complete ERP solution that is extremely beneficial for small to midsize organizations in helping them grow exponentially.
The book shows you in detail how to build great-looking dashboards with Microsoft Dynamics GP that enhance a company’s decision-making processes.
This guide will take you from the basics of setting up and deploying to creating secure, refreshable Excel reports. Using a whole host of tools available within Microsoft Dynamics GP and Excel, this tutorial will show you how to visualize your data using simple conditional formatting techniques and easy-to-read charts, and allow you to make your data interactive with slicers.
We will also cover core topics such as Business Analyzer, Microsoft SQL Reporting services reports, BI360, and more. You will find out to use Power BI, share and refresh data and dashboards in Power BI, and use Power BI Query Editor.
By the end of this book, you will have all the information required to build interactive dashboards using Dynamics GP.
Das E-Book können Sie in Legimi-Apps oder einer beliebigen App lesen, die das folgende Format unterstützen:
Seitenzahl: 334
Veröffentlichungsjahr: 2017
Ähnliche
Table of Contents
Building Dashboards with Microsoft Dynamics GP 2016 Second Edition
Building Dashboards with Microsoft Dynamics GP 2016 Second Edition
Copyright © 2017 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 authors, 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
Second edition: March 2017
Production reference: 1030317
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham B3 2PB, UK.
ISBN 978-1-78646-761-4
www.packtpub.com
Credits
Authors
Belinda Allen
Mark Polino
Reviewer
Vaidhyanathan Mohan
Commissioning Editor
Ashwin Nair
Acquisition Editor
Denim Pinto
Content Development Editor
Siddhi Chavan
Technical Editors
Kunal Chaudhari
Abhishek Sharma
Copy Editors
Zainab Bootwala
Karuna Narayanan
Project Coordinator
Izzat Contractor
Proofreader
Safis Editing
Indexer
Aishwarya Gangawane
Graphics
Jason Monteiro
Production Coordinator
Aparna Bhagat
Cover Work
Aparna Bhagat
About the Authors
Belinda Allen is a Microsoft Most Valuable Professional for Dynamics GP (MVP) and a GPUG (GP User Group) All-Star. Belinda is currently the Business Intelligence Program Manager for the new Azure Cloud-based SaaS ERP offering, PowerGP Online. This is an amped-up version of the GP we all love, running in the Microsoft Azure Cloud. In this role, she helps both Partners and Customers design and implement BI methodologies, allowing businesses to make decisions based on timely and accurate information.
Belinda was one of the co-founders of Smith & Allen Consulting, Inc. (SACI), a New York City based firm with over 25 years' experience specializing in business intelligence, analytics and ERP software. On April 1, 2016, SACI joined forces with Njevity, Inc. (www.NjevityToGo.com). Now she gets to spend time in her favorite place, the world of Business Intelligence (BI). NjevityToGo offers solutions for ERP, CRM, BI, and much more in the cloud. Njevity is also the force behind PowerGP Online.
Belinda's first book, Real-world Business Intelligence with Microsoft Dynamics GP was co-written with Mark Polino. It's a dive into where to Implementing a Business Intelligence Methodology with Microsoft Dynamics GP.
Currently a member of the Credentialing Council for the Association of Dynamics Professionals, Belinda was the first Council Chair. In this role, she led a team of community leaders providing guidance and insight in the delivery and development of credentials for both Microsoft Dynamics GP and Microsoft Dynamics NAV.
Belinda is also an inaugural member of the Board of Advisors for PBIUG (Power BI User Group.) In this role she provides her experience to the User Board Members, while they create the foundation for this new organization. The goal is to make Power BI a critical tool in the user's organizations.
Belinda began implementing ERP systems so long ago that Windows was not an operating system but an application. And at that time, larger businesses used main frames with monitors that projected green type on black backgrounds, and smaller business did their accounting by hand. Having seen the evolution that has taken place over the years from sheets of paper to integrated analytics, Belinda still gets excited every time she helps a business improve.
Belinda is also well known for her blog, www.BelindaTheGPcsi.com. On her blog, she shares really useful information about the product quickly and succinctly. She has earned the nickname GP CSI because she excels at reviewing GP problems and figuring out what went wrong… and why. With followers from all over the globe, she is able to share knowledge and achieve her mission--"To improve the lives and business success of my followers." Belinda has just started her new blog, www.BIbelinda.com, which is devoted to Microsoft Power BI.
When not delving into GP problems and spearheading business success for clients, Belinda enjoys sailing, crochet/knitting, sewing/quilting, reading, and turning wood.
For all the good things in my life, I would like to thank my husband, Richard Allen. He's been my best friend and the love of my life for close to 30 years. He's one of the few people I know who loves learning as much as I do. Besides that, he gets my odd sense of humor.
I'd like to thank Mark Polino for trusting me to update his words and ideas. I'm not only honored by this trust, I'm grateful for the opportunity. Counting you (and Dara) as my friends is something I cherish.
Finally, I'd like to thank my parents. Their support for me has only grown stronger as time goes by. I'm thankful they taught me to think for myself, love to learn new things, and laugh.
Mark Polino is CPA, with additional certifications in financial forensics (CFF) and information technology (CITP). He is a Microsoft MVP for Business Solutions and a GPUG All Star who has worked with Dynamics GP and its predecessors since 1999.
He works as the Director of Client Services for Fastpath, and he runs the DynamicAccounting.net website dedicated to all things Dynamics GP. He is a regularly featured speaker at Dynamics GP related events.
This is Mark's tenth book, and his seventh GP related book with Packt Publishing. His work includes eight technology-focused books and two novels.
He is also the author of the best-selling Microsoft Dynamics GP 2010 Cookbook, the spin off Lite edition, and a co-author for updated GP 2013 and 2016 editions, all from Packt Publishing.
First, a huge thank you to Belinda Allen for taking on this project and extending it into places I wouldn't have had the courage to go. You were the driving force behind making this book a reality.
Second, to all my friends in the greater GP community, thank you for putting your trust in me through the website, presentations, and books like this. It is not something I take lightly.
Finally, to my wife, thank you again for your infinite patience as you listen to me blather on about book projects I'm working on.
About the Reviewer
Vaidhyanathan Mohan is a certified Senior Microsoft Dynamics GP/CRM Implementation & Product Consultant, with expertise in Microsoft Dynamics GP and related technologies. He has worked on various challenging Dynamics GP customizations and implementations.
He's an active participant on all Microsoft Community forums. His blog, Dynamics GP - Learn & Discuss (http://vaidymohan.com/) has been listed on Microsoft's official Dynamics GP blog space. He has also reviewed several books on Dynamics GP, such as Developing Microsoft Dynamics GP Business Applications, Microsoft Dynamics GP 2013 Cookbook, etc.
He is who he is now because of his devoted parents, his brother, his wife, and his daughter. He is an avid photography enthusiast (http://500px.com/seshadri), loves music, lives on coffee, travels to learn different cultures and nature, and is immensely interested in anything related to Microsoft Dynamics GP.
www.PacktPub.com
For support files and downloads related to your book, please visit www.PacktPub.com.
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.
https://www.packtpub.com/mapt
Get the most in-demand software skills with Mapt. Mapt gives you full access to all Packt books and video courses, as well as industry-leading tools to help you plan your personal development and advance your career.
Why subscribe?
Customer Feedback
Thanks for purchasing this Packt book. At Packt, quality is at the heart of our editorial process. To help us improve, please leave us an honest review at the website where you acquired this product.
If you'd like to join our team of regular reviewers, you can email us at <[email protected]>. We award our regular reviewers with free eBooks and videos in exchange for their valuable feedback. Help us be relentless in improving our products!
Preface
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.
What this book covers
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!
What you need for this book
The following list is software prerequisites that are required:
Who this book is for
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.
Conventions
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:
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."
Note
Warnings or important notes appear in a box like this.
Tip
Tips and tricks appear like this.
Reader feedback
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.
Customer support
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.
Downloading the color images of this book
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
https://www.packtpub.com/sites/default/files/downloads/BuildingDashboardswithMicrosoftDynamicsGP2016SecondEdition_ColorImages.pdf
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 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
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.
Questions
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.
Chapter 1. Getting Data from Dynamics GP 2016 to Excel 2016
In more than 25 years of experience working in enterprise resource planning (ERP) consulting, every customer I've worked with put something of importance in Microsoft Excel. Why? The same reason you are reading this book. We all know how to use it, we like using it, and the people we share reports with know how to use it. However, we all want to know more about Excel, we all want to use it better, and (I cannot state this in big enough font) we want to impress someone with how great we are at using it. Yes, this means we want to share files and reports that make our colleagues and managers say, "Wow, how did you do this?," all while creating and managing these reports in less time.
Together, we will explore the power of Microsoft Excel 2016 and Microsoft Dynamics GP 2016 where we will build simple dashboards that looks anything but simple. Don't worry; we will not be doing any of the "developer-ish" stuff such as named ranges, macros, or VLOOKUPs. Microsoft has added so many amazing features to Excel that you can create amazing reports and dashboards all using native tools.
Before we can build a great Excel-based dashboard using the data in Dynamics GP 2016, we have to get the data out of GP and into Excel. This chapter covers eight major ways to get data from Dynamics GP into Excel, with a few extra options thrown in at the end.
By the end of this chapter, you should be able to get data into Excel using:
Tip
Occasionally, we will reference either the Rich Client or the Web Client for Dynamics GP 2016. If you are not sure which option you have, follow the directions for the Rich Client.
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 for the Dynamics Rich Client, follow these steps:
To export from a SmartList to Excel for the Dynamics Web Client, 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. 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. SmartLists 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 that you only have to do it once. Let's record our Excel macro using these steps:
Creating an export solution
Now that we've prepared our Excel 2016 workbook to receive a SmartList, we need to set up and run the SmartList Export Solution using these steps:
Tip
There is a length limit of 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.
Tip
You will have to unselect the Account Summary in the SmartList window and click back for the new export option to appear.
Get and Transform – formerly known as Power Query
Without a doubt, this is a personal favorite method of getting GP data into Excel. "Why?" you ask. The reason is with Get and Transform you can:
And all of this can be done from within Excel without ever logging into a SQL tool such as the SQL Server Studio. You can have developer results while thinking like an Excel user and without being a developer.
Tip
There is a big advantage to learning this tool. It is the same tool that is used in Microsoft Power BI. So, learning this one tool in Excel gives you a huge advantage in Power BI.
In Excel 2013 and Excel 2010, this feature can be installed as an add-on called Power Query. Note that this feature only works on specific versions of Excel, so check the system requirements before downloading.
Tip
A table is a file that holds a set of records in the SQL Server. Imagine your chart of accounts being stored in an Excel spreadsheet, which could be a single table for some applications. However, many complex applications (such as Dynamics GP) often break up the information across several tables for efficiency. GP separates the chart of accounts into seven separate tables. Separating the data is good for the application, but confusing to non-developers or database administrators who just want a good Excel report.
To make it easier for users, often these virtual tables are created for the purpose of reporting to combine the data together and making the field names logical. A view is what a virtual table in the SQL Server is called. The chart of accounts information in GP, for example, can be found in an out-of-the-box view called Accounts.
Let's extract our list of General Ledger Accounts. Fortunately, Microsoft has already created this as a view in the SQL Database. This view has a lot of fields in it, but let's assume we want to make sure all of the accounts are set up with the correct type (Balance Sheet or Profit and Loss) so that when we close the year in the General Ledger, only the Balance Sheet accounts will roll forward into the new year. Follow these steps:
Tip
If you do not know the server or database names, consult your IT department or your GP Partner. Usually, the server name is the name of the machine on which the SQL Server is installed. The database name can be found in the upper-right corner on the Company Setup window in GP.
Tip
Note that if you select a table, the query will be named Query1, then Query2, and so on. If you have multiple queries on a spreadsheet, it can get confusing as to what they represent. This is why renaming them is important and should be our first job.
This is only a tiny fraction of what Get and Transform can do. You'll learn more about this great feature in Chapter 12, Sharing and Refreshing Data and Dashboards in Power BI.
Office Data Connection files
Excel has its own method of importing external data from a variety of sources, including data in the SQL Server. This method can be embedded directly in the workbook or stored in a separate file, the Office Data Connection (ODC) file. When this .odc file is created, it can be reused over and over for a quick connection between your GP and Excel that is refreshable.
Creating an .odc file
Let's create an .odc file to bring in our vendors using these steps:
Tip
If you do not know the server or database names, consult your IT department or your GP Partner. Usually, the server name is the name of the machine on which the SQL Server is installed.
Tip
The database name can be found in the upper-right corner on the Company Setup window in GP.
In the Excel Data Connection Wizard window, first, SQL Views are displayed in alphabetical order; then, SQL Tables are displayed in alphabetical order.
