Building Dashboards with Microsoft Dynamics GP 2016 - Second Edition

By Mark Polino and Belinda Allen

About This Book

• This book provides a core foundation for you to understand the ever-changing Microsoft Power BI
• Through this book, you'll understand how data flows and is secured between Microsoft Dynamics GP and Microsoft Excel
• You'll see how to create amazing dashboards using various tools such as Excel 2016, Power BI, Jet Express, and more

Who This Book Is For

This book is geared up for analysts and accountants keen on building and maintaining professional dashboards with Microsoft Excel 2016 for Microsoft Dynamics GP 2016 data, and building financials with visuals using the New Jet Reports Express Tool for Dynamics GP. An introduction is provided for those who want to maintain dashboards in Microsoft Power BI.

• Language: English
• Publisher: Packt Publishing
• Release date: Mar 10, 2017
• ISBN: 9781786464040

Mark Polino

Mark Polino is a Certified Public Accountant, a Microsoft Certified Business Management Professional, and a Microsoft MVP for Dynamics GP. He has been working with Dynamics GP as an administrator and consultant for more than ten years. Currently Mark is a Principal Consultant with Microsoft partner I.B.I.S., Inc. and he runs the premier user-focused Dynamics GP blog at DynamicAccounting.net.

Book preview

Table of Contents

Building Dashboards with Microsoft Dynamics GP 2016 Second Edition

Credits

About the Authors

About the Reviewer

www.PacktPub.com

Why subscribe?

Customer Feedback

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Downloading the color images of this book

Errata

Piracy

Questions

1. Getting Data from Dynamics GP 2016 to Excel 2016

SmartList exports

SmartList Export Solutions

Getting ready

Creating macros

Creating an export solution

Get and Transform – formerly known as Power Query

Office Data Connection files

Creating an .odc file

The location of the .odc file

Reusing an .odc fie

SQL Server Reporting Services

Jet Reports Express for Excel

Basic financial data

Table Builder

SQL Server Management Studio

Analysis Cubes

Third-party solutions

Licensing

Summary

2. The Ultimate GP to Excel Tool – Refreshable Excel Reports

Security

Network share security

Database-level security

Excel 2016 security

Running Excel reports

From Dynamics GP 2016

From Excel 2016

Manual versus auto refresh

Excel refreshable reports via SmartList Designer

Create a new SmartList object

Publish to Excel

Summary

3. Pivot Tables – The Basic Building Blocks

Creating pivot tables from GP 2016 Excel report data

Getting data to Excel

Building a pivot table with a calculated field

Creating pivot tables from GP 2016 data connections

Building a revenue pivot table

Copying pivot tables

Building a net income pivot table

Creating a cash pivot table

Creating connected pivot tables from inside Excel

Building the sales pivot table

Adding a receivables pivot table

Summary

4. Making Your Data Visually Appealing and Meaningful with Formatting, Conditional Formatting, and Charts

Recap

Preparation

Get pivot data

Revenue

Net income

Formatting

Icon sets

Sparklines

Preparing for Sparklines

Adding Sparklines

Sparkline idiosyncrasies

Deleting Sparklines

Changing Sparkline data

Data bars

Bar chart with trend line

Pie chart

Speedometer chart

The green/yellow/red limit

Building a doughnut

Cutting the doughnut in half

Building a needle

Finishing it off with Sprinkles

Slicers and timelines

Slicers

Creating slicers

Connecting slicers

Timeline

Some more formatting

Summary

5. Drilling Back to the Source Data and Other Cool Stuff

Recap

Slicers and timelines options

Slicer orientation

Slicers and timelines color and alignment

Slicer additional options

Learning about hyperlinks

Using drill downs in GP 2016

Drill down background

Using drill downs

Drill down link structure

Other cool Excel stuff

Adding a logo

Good design

Refreshing the data

Sharing

The quick option – e-mail

Network sharing

Hosting via OneDrive

Downloading via OneDrive

Downloading via SharePoint

Hosting via SharePoint Office 365 services

Microsoft Power BI

Summary

6. Introducing Jet Reports Express

Recap

What is Jet and why should I use it?

Prebuilt reports

Jet Views and Friendly Names

What is Table Builder?

Create a general ledger trial balance

Other Jet offerings

Summary

7. Building Financial Reports in Jet Express for GP

Recap

Building a balance sheet

Building a profit and loss statement

Summary

8. Introducing Microsoft Power BI

Recap

Power BI Desktop versus Service versus Mobile

Power BI Desktop

Power BI Service

Power BI Mobile

Other ways to view Power BI

Power BI Professional (paid) versus Power BI (free)

Typical workflow of Power BI

Update speed

Summary

9. Getting Data in Power BI

Recap

Getting data from files

Using Excel reports in Power BI

Connecting to data in Dynamics GP

Direct SQL Connect

SQL statement

OData

Content packs

Online Services

Organizational

Getting data from folders

Summary

10. Creating Power BI Visuals

Recap

Using Filters

Formatting as a tool

Understanding standard visuals

Getting quick information with cards

Making a Gauge have more meaning

Getting down with drill down charts

Carving out better data with a Slicer

Adding final touches

Development options - if you have a techie on staff

R - what is it and do I need it?

Summary

11. Using the Power BI Service

Recap

Publishing to the service

Creating a dashboard

Getting to know the Q&A feature

Importing an Excel report

Summary

12. Sharing and Refreshing Data and Dashboards in Power BI

Recap

Power BI Template

Content Packs

Online Services

Refreshing data

Types of refresh

Online Services

Organizational Content Packs

One-Drive and SharePoint Online

OData

Gateway

Summary

13. Using the Power Query Editor

Recap

What is a query?

Exiting Query Editor

Naming queries

Using Applied Steps

Removing unnecessary column(s)

Formatting column Data Types

What is the M language?

Using Replace Values

Transforming data

Trim a little off the top, the right, the left, and so on

Formatting with Case

Working with dates and times

Merging columns

Splitting columns

Merging queries

Appending queries

Summarizing with Group By

Formulating with DAX

Summary

14. Bonus Chapter

Recap

Excel 2016

Sharing Excel reports via IM

Sharing Excel reports via live presentation

Jet Express for GP

Eliminating values that should be zero

Refreshing with an option window

Microsoft Power BI

Map of customer balances

Getting data from a folder

Summary

Index

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 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?

Fully searchable across every book published by Packt

Copy and paste, print, and bookmark content

On demand and accessible via a web browser

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 on this book's Amazon page at https://www.amazon.com/dp/1786467615.

If you'd like to join our team of regular reviewers, you can email us at [e-mail]. 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:

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!

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:

=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.

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 <[e-mail]>, 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