Building Dashboards with Microsoft Dynamics GP 2013 and Excel 2013电子书

Building Dashboards with Microsoft Dynamics GP 2013 and Excel 2013

Table of Contents

Building Dashboards with Microsoft Dynamics GP 2013 and Excel 2013

Credits

About the Author

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers and more

Why Subscribe?

Free Access for Packt account holders

Instant Updates on New Packt Books

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Downloading the support files

Errata

Piracy

Questions

1. Getting Data from Dynamics GP 2013 to Excel 2013

SmartList exports

SmartList Export Solutions

Getting ready

Creating macros

Creating an export solution

Navigation List export

Report writer

Microsoft Query

SQL Server Reporting Services

Management Reporter

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 2013 security

Running Excel reports

From Dynamics GP 2013

From Excel 2013

Manual versus auto refresh

Modifying Excel reports

Reformatting Excel data

Modifying source data

Office data connections

Excel Report Builder

Restrictions

Calculations

Options

Publish

Summary

3. Pivot Tables: The Basic Building Blocks

Creating pivot tables from GP 2013 Excel report data

Getting data to Excel

Building a pivot table

Creating pivot tables from GP 2013 data connections

Building a revenue pivot table

Copying pivot tables

Building the 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

Excel Report Builder pivot tables

Creating Power View reports

Summary

4. Making Things Pretty with Formatting and Conditional Formatting

Recap

Preparation

Get Pivot Data

Revenue

Net Income

Formatting

Icon sets

Data bars

Color Scales

Adjusting Color Scales

The green/yellow/red limit

Some more formatting

Summary

5. Charts: Eye Candy for Executives

Recap

Bar chart

Adding a line

Pie chart

Speedometer chart

Building a doughnut

Cutting the doughnut in half

Building a needle

Finishing it off with sprinkles

Bar chart with trend line

Selecting charts

Sparklines

Preparing for sparklines

Adding sparklines

Sparkline idiosyncrasies

Deleting sparklines

Changing sparkline data

Summary

6. Adding Interactivity with Slicers and Timelines

Recap

Learning about slicers

Creating slicers

Connecting slicers

Slicer orientation

Slicer options

Timeline

Timeline options

Summary

7. Drilling Back to Source Data in Dynamics GP 2013

Recap

Learning about hyperlinks

Using drill downs in GP 2013

Drill down background

Using drill downs

Fixing the journal entry drill down problem

Drill down link structure

Drill Down Builder

Complex drill downs

Drilling down with GP 2013 and Excel 2013 on Citrix or Terminal Server

Drilling down to GP 2013 on Citrix with Excel 2013 installed locally

Other complex drill down scenarios

Summary

8. Bringing it All Together

Adding headers

Cleaning it up

Adding a logo

Creating backgrounds

The Fill Color feature

Inserting a picture

Inserting a background

Good design

Final cleanup

Refreshing the data

Sharing

The quick option – e-mail

Network sharing

Hosting via SkyDrive

Downloading via SkyDrive

Downloading via SharePoint

Hosting via SharePoint Excel Services

Summary

9. Expanding Pivot Tables with PowerPivot

PowerPivot Basics

Bringing Dynamics GP 2013 information to PowerPivot

Copying and pasting

Linking to a spreadsheet

Connecting via SQL Server

Learning about relationships

Creating a pivot table

Understanding the Excel data model

Other source options

About Atom feeds

SQL Server Reporting Services (SSRS)

Generating an Atom feed from an SSRS report

SSRS native connections

Windows Azure Marketplace

More PowerPivot options

Millions of rows of data

DAX formulas

SharePoint

Resources

Summary

10. Slightly Crazy Stuff

Using built-in ratios

Current Ratio

Microsoft Dashboard

Negative data bars

Quick Analysis

Summary

Index