Building Dashboards with Microsoft Dynamics GP 2013 and Excel 2013

Microsoft Dynamics GP and Excel are made for each other. With this book you’ll learn to use Excel to present the information contained in Dynamics in a data-rich dashboard. Step-by-step instructions come with real-life examples.

Building Dashboards with Microsoft Dynamics GP 2013 and Excel 2013

Mark PolinoMarch 2013

Microsoft Dynamics GP and Excel are made for each other. With this book you’ll learn to use Excel to present the information contained in Dynamics in a data-rich dashboard. Step-by-step instructions come with real-life examples.

Mapt Subscription

FREE

$29.99/m after trial

eBook

$21.00

RRP $29.99

Save 29%

Print + eBook

$49.99

RRP $49.99

What do I get with a Mapt Pro subscription?

Unlimited access to all Packt’s 5,000+ eBooks and Videos

Early Access content, Progress Tracking, and Assessments

1 Free eBook or Video to download and keep every month after trial

What do I get with an eBook?

Download this book in EPUB, PDF, MOBI formats

DRM FREE - read and interact with your content when you want, where you want, and how you want

Access this title in the Mapt reader

What do I get with Print & eBook?

Get a paperback copy of the book delivered to you

Download this book in EPUB, PDF, MOBI formats

DRM FREE - read and interact with your content when you want, where you want, and how you want

Access this title in the Mapt reader

What do I get with a Video?

Download this Video course in MP4 format

DRM FREE - read and interact with your content when you want, where you want, and how you want

Book Details

ISBN 139781849689069

Paperback268 pages

Book Description

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.

Table of Contents

Chapter 1: Getting Data from Dynamics GP 2013 to Excel 2013

SmartList exports

SmartList Export Solutions

Report writer

Microsoft Query

SQL Server Reporting Services

Management Reporter

SQL Server Management Studio

Third-party solutions

Licensing

Summary

Chapter 2: The Ultimate GP to Excel Tool: Refreshable Excel Reports

Security

Running Excel reports

Modifying Excel reports

Excel Report Builder

Summary

Chapter 3: Pivot Tables: The Basic Building Blocks

Creating pivot tables from GP 2013 Excel report data

Creating pivot tables from GP 2013 data connections

Copying pivot tables

Creating connected pivot tables from inside Excel

Excel Report Builder pivot tables

Creating Power View reports

Chapter 4: Making Things Pretty with Formatting and Conditional Formatting

Recap

Preparation

Get Pivot Data

Icon sets

Data bars

Color Scales

The green/yellow/red limit

Some more formatting

Summary

Chapter 5: Charts: Eye Candy for Executives

Recap

Bar chart

Pie chart

Speedometer chart

Bar chart with trend line

Selecting charts

Sparklines

Summary

Chapter 6: Adding Interactivity with Slicers and Timelines

Recap

Learning about slicers

Timeline

Summary

Chapter 7: Drilling Back to Source Data in Dynamics GP 2013

Recap

Learning about hyperlinks

Using drill downs in GP 2013

Drill Down Builder

Summary

Chapter 8: Bringing it All Together

Adding headers

Cleaning it up

Adding a logo

Good design

Final cleanup

Summary

Chapter 9: Expanding Pivot Tables with PowerPivot

PowerPivot Basics

Bringing Dynamics GP 2013 information to PowerPivot

Creating a pivot table

Other source options

More PowerPivot options

Summary

Chapter 10: Slightly Crazy Stuff

Using built-in ratios

Current Ratio

Microsoft Dashboard

Negative data bars

Quick Analysis

Summary

What You Will Learn

Get Dynamics GP 2013 information into Excel 2013

Set up, deploy, and secure refreshable Excel reports in Dynamics GP 2013 to get live GP data to Excel

Consolidate and present GP 2013 data using Excel pivot tables

Improve the look and feel of a dashboard with conditional formatting

Enhance data clarity with charts and sparklines

Add interactivity to a dashboard using Slicers

Link dashboard data back to Dynamics GP 2013

Approach more complex dashboards and sources using PowerPivot

Authors

Mark Polino

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.

Table of Contents

Chapter 1: Getting Data from Dynamics GP 2013 to Excel 2013

SmartList exports

SmartList Export Solutions

Report writer

Microsoft Query

SQL Server Reporting Services

Management Reporter

SQL Server Management Studio

Third-party solutions

Licensing

Summary

Chapter 2: The Ultimate GP to Excel Tool: Refreshable Excel Reports

Security

Running Excel reports

Modifying Excel reports

Excel Report Builder

Summary

Chapter 3: Pivot Tables: The Basic Building Blocks

Creating pivot tables from GP 2013 Excel report data

Creating pivot tables from GP 2013 data connections

Copying pivot tables

Creating connected pivot tables from inside Excel

Excel Report Builder pivot tables

Creating Power View reports

Chapter 4: Making Things Pretty with Formatting and Conditional Formatting

Alerts & Offers

Series & Level

We understand your time is important. Uniquely amongst the major publishers, we seek to develop and publish the broadest range of learning and information products on each technology. Every Packt product delivers a specific learning pathway, broadly defined by the Series type. This structured approach enables you to select the pathway which best suits your knowledge level, learning style and task objectives.

Learning

As a new user, these step-by-step tutorial guides will give you all the practical skills necessary to become competent and efficient.

Beginner's Guide

Friendly, informal tutorials that provide a practical introduction using examples, activities, and challenges.

Essentials

Fast paced, concentrated introductions showing the quickest way to put the tool to work in the real world.

Cookbook

A collection of practical self-contained recipes that all users of the technology will find useful for building more powerful and reliable systems.

Blueprints

Guides you through the most common types of project you'll encounter, giving you end-to-end guidance on how to build your specific solution quickly and reliably.

Mastering

Take your skills to the next level with advanced tutorials that will give you confidence to master the tool's most powerful features.

Starting

Accessible to readers adopting the topic, these titles get you into the tool or technology so that you can become an effective user.

Progressing

Building on core skills you already have, these titles share solutions and expertise so you become a highly productive power user.