O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Building Dashboards with Microsoft Dynamics GP 2016 - Second Edition

Book Description

Learn to build professional and powerful dashboards with Microsoft Dynamics GP and Excel

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.

What You Will Learn

  • Use GP Data in Excel 2016 in a meaningful way
  • Build basic financial statements using Jet Reporting Express, including visualizations
  • Understand the foundation of Power BI and its components
  • Get and maintain data from Dynamics GP in Power BI
  • Find out how to use the Power BI Query Editor (the Get and Transform feature in Excel 2016)
  • Format basic visualizations to get better insights
  • Understand Jet Reports Express, which is used to create basic financial statements
  • Use the new Dynamics GP features, such as OData and adding Power BI tiles on the home page

In Detail

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.

Style and approach

The book takes a step-by-step approach and contains real-world examples that provide the building blocks to create engaging dashboards. This practical guide is all about doing. Get your data, open up Excel, and go!

Downloading the example code for this book. You can download the example code files for all Packt books you have purchased from your account at http://www.PacktPub.com. If you purchased this book elsewhere, you can visit http://www.PacktPub.com/support and register to have the code file.

Table of Contents

  1. Building Dashboards with Microsoft Dynamics GP 2016 Second Edition
    1. Table of Contents
    2. Building Dashboards with Microsoft Dynamics GP 2016 Second Edition
    3. Credits
    4. About the Authors
    5. About the Reviewer
    6. www.PacktPub.com
        1. Why subscribe?
    7. Customer Feedback
    8. Preface
      1. What this book covers
      2. What you need for this book
      3. Who this book is for
      4. Conventions
      5. Reader feedback
      6. Customer support
        1. Downloading the color images of this book
        2. Errata
        3. Piracy
        4. Questions
    9. 1. Getting Data from Dynamics GP 2016 to Excel 2016
      1. SmartList exports
      2. SmartList Export Solutions
        1. Getting ready
        2. Creating macros
        3. Creating an export solution
        4. Get and Transform – formerly known as Power Query
      3. Office Data Connection files
        1. Creating an .odc file
        2. The location of the .odc file
        3. Reusing an .odc fie
      4. SQL Server Reporting Services
      5. Jet Reports Express for Excel
        1. Basic financial data
        2. Table Builder
      6. SQL Server Management Studio
      7. Analysis Cubes
      8. Third-party solutions
      9. Licensing
      10. Summary
    10. 2. The Ultimate GP to Excel Tool – Refreshable Excel Reports
      1. Security
        1. Network share security
        2. Database-level security
        3. Excel 2016 security
      2. Running Excel reports
        1. From Dynamics GP 2016
        2. From Excel 2016
        3. Manual versus auto refresh
      3. Excel refreshable reports via SmartList Designer
        1. Create a new SmartList object
        2. Publish to Excel
      4. Summary
    11. 3. Pivot Tables – The Basic Building Blocks
      1. Creating pivot tables from GP 2016 Excel report data
        1. Getting data to Excel
        2. Building a pivot table with a calculated field
      2. Creating pivot tables from GP 2016 data connections
        1. Building a revenue pivot table
      3. Copying pivot tables
        1. Building a net income pivot table
        2. Creating a cash pivot table
      4. Creating connected pivot tables from inside Excel
        1. Building the sales pivot table
        2. Adding a receivables pivot table
      5. Summary
    12. 4. Making Your Data Visually Appealing and Meaningful with Formatting, Conditional Formatting, and Charts
      1. Recap
      2. Preparation
      3. Get pivot data
        1. Revenue
        2. Net income
        3. Formatting
      4. Icon sets
      5. Sparklines
        1. Preparing for Sparklines
        2. Adding Sparklines
        3. Sparkline idiosyncrasies
          1. Deleting Sparklines
          2. Changing Sparkline data
      6. Data bars
      7. Bar chart with trend line
      8. Pie chart
      9. Speedometer chart
        1. The green/yellow/red limit
        2. Building a doughnut
        3. Cutting the doughnut in half
        4. Building a needle
        5. Finishing it off with Sprinkles
      10. Slicers and timelines
        1. Slicers
          1. Creating slicers
          2. Connecting slicers
        2. Timeline
      11. Some more formatting
      12. Summary
    13. 5. Drilling Back to the Source Data and Other Cool Stuff
      1. Recap
      2. Slicers and timelines options
        1. Slicer orientation
        2. Slicers and timelines color and alignment
        3. Slicer additional options
      3. Learning about hyperlinks
      4. Using drill downs in GP 2016
        1. Drill down background
        2. Using drill downs
        3. Drill down link structure
      5. Other cool Excel stuff
        1. Adding a logo
      6. Good design
      7. Refreshing the data
      8. Sharing
        1. The quick option – e-mail
        2. Network sharing
        3. Hosting via OneDrive
        4. Downloading via OneDrive
        5. Downloading via SharePoint
        6. Hosting via SharePoint Office 365 services
        7. Microsoft Power BI
      9. Summary
    14. 6. Introducing Jet Reports Express
      1. Recap
      2. What is Jet and why should I use it?
        1. Prebuilt reports
        2. Jet Views and Friendly Names
      3. What is Table Builder?
        1. Create a general ledger trial balance
      4. Other Jet offerings
      5. Summary
    15. 7. Building Financial Reports in Jet Express for GP
      1. Recap
      2. Building a balance sheet
      3. Building a profit and loss statement
      4. Summary
    16. 8. Introducing Microsoft Power BI
      1. Recap
      2. Power BI Desktop versus Service versus Mobile
        1. Power BI Desktop
        2. Power BI Service
        3. Power BI Mobile
        4. Other ways to view Power BI
      3. Power BI Professional (paid) versus Power BI (free)
      4. Typical workflow of Power BI
      5. Update speed
      6. Summary
    17. 9. Getting Data in Power BI
      1. Recap
      2. Getting data from files
        1. Using Excel reports in Power BI
      3. Connecting to data in Dynamics GP
        1. Direct SQL Connect
        2. SQL statement
        3. OData
      4. Content packs
        1. Online Services
        2. Organizational
      5. Getting data from folders
      6. Summary
    18. 10. Creating Power BI Visuals
      1. Recap
      2. Using Filters
      3. Formatting as a tool
      4. Understanding standard visuals
        1. Getting quick information with cards
        2. Making a Gauge have more meaning
        3. Getting down with drill down charts
        4. Carving out better data with a Slicer
        5. Adding final touches
      5. Development options - if you have a techie on staff
      6. R - what is it and do I need it?
      7. Summary
    19. 11. Using the Power BI Service
      1. Recap
      2. Publishing to the service
      3. Creating a dashboard
      4. Getting to know the Q&A feature
      5. Importing an Excel report
      6. Summary
    20. 12. Sharing and Refreshing Data and Dashboards in Power BI
      1. Recap
      2. Power BI Template
      3. Content Packs
      4. Online Services
      5. Refreshing data
        1. Types of refresh
        2. Online Services
        3. Organizational Content Packs
        4. One-Drive and SharePoint Online
        5. OData
        6. Gateway
      6. Summary
    21. 13. Using the Power Query Editor
      1. Recap
      2. What is a query?
        1. Exiting Query Editor
        2. Naming queries
      3. Using Applied Steps
      4. Removing unnecessary column(s)
      5. Formatting column Data Types
      6. What is the M language?
      7. Using Replace Values
      8. Transforming data
        1. Trim a little off the top, the right, the left, and so on
        2. Formatting with Case
      9. Working with dates and times
      10. Merging columns
      11. Splitting columns
      12. Merging queries
      13. Appending queries
      14. Summarizing with Group By
        1. Formulating with DAX
      15. Summary
    22. 14. Bonus Chapter
      1. Recap
      2. Excel 2016
        1. Sharing Excel reports via IM
        2. Sharing Excel reports via live presentation
      3. Jet Express for GP
        1. Eliminating values that should be zero
        2. Refreshing with an option window
      4. Microsoft Power BI
        1. Map of customer balances
        2. Getting data from a folder
      5. Summary
    23. Index