![]()
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 Marketpla...
