Microsoft Dynamics GP 2010 Reporting

Table of Contents

Microsoft Dynamics GP 2010 Reporting

Credits

Foreword

About the Authors

Acknowledgement

Acknowledgement

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 example code

Errata

Piracy

Questions

1. Meeting the Reporting Challenge

Trends in reporting

Increased flexibility

Reporting through all levels of an organization

Increased access to report generation process

Challenges in developing and writing reports

Intended audience

Data sources

Latency

Formatting and presentation

Ad hoc queries versus traditional reports

Security

Network access and general IT infrastructure

Developer resources

Summary

2. Where Is My Data and How Do I Get to It?

DYNAMICS database versus company database

What is the DYNAMICS database?

Company database

Dynamics GP table naming/numbering conventions

Tables versus table groups

Physical table naming/numbering conventions

Alpha code

Table type

Sequence

Variant

Locating Dynamics GP data using the Resource Descriptions windows

Tables

Fields

Windows

Table Import

Accessing data at the table level using SQL Management Studio

Locating Dynamics GP data with additional tools

Dynamics GP 2010 Software Development Kit

Downloading the Software Development Kit

Using the Software Development Kit

Support Debugging Tool

Downloading the Support Debugging Tool

Using the Support Debugging Tool

Summary

3. Working with the Builders: SmartList and Excel Reports

Satisfying basic reporting needs with canned SmartLists

Structure of SmartList

Basic SmartList tools

Narrowing our result list with search criteria

Adding new columns

Changing the number of records returned

Achieving faster export to Excel with a simple dex.ini switch

Extending SmartList data to Excel by deploying Excel reports

Deploying Excel reports

Maintaining security for Excel reports

Shared network folder permissions

Database level permissions

Accessing and using Excel Reports

Connecting to Excel Reports via Windows Explorer

Connecting to Excel Reports via GP 2010

Creating and publishing new SmartLists by using SmartList Builder

Understanding the SmartList Builder window

Creating a new SmartList via SmartList Builder

Creating and publishing new Excel Report formats using Excel Report Builder

Understanding the Excel Reports Builder window

Creating a new Excel Report via Excel Reports Builder

Additional tips and tricks for using the Builders

Summary

4. Report Writer

Understanding the reports dictionaries and how Dynamics GP treats original versus modified reports

Storing Report Writer reports

Storing reports dictionaries locally

Storing reports dictionaries on a network share

Accessing Report Writer reports

Setting the Dynamics GP launch file

Setting security to custom/modified reports

Opening and navigating the Report Writer windows

Setting security permissions for using Report Writer

Opening Report Writer

Report Writer Resource windows

Fields

Data types

Formats

Pictures

Strings

Report Modification windows

Report Definition

Report Table Relationships

Sorting Definition

Report Restrictions

Layout

Properties

Toolbox

Modifying all reports in the application by using global modifications

Data types

Formats

Pictures

Strings

Modifying an existing Dynamics GP report

Common modifications

Modifying a report: Adding aging buckets to Payables Trial Balance Report

Importing and exporting customized reports

Exporting customized reports

Importing customized reports

Using modified reports from the Reports Library

Rendering reports in Microsoft Word by utilizing the new Dynamics GP 2010 Word template feature

Enabling Word templates

Installing the Dynamics GP add-in for Microsoft Word

Understanding the Word template modification process

Modifying the presentation of a default Word template

Applying simple formatting changes to an existing template

Setting the default Word template for a company database

Adding fields to an existing Word template

Adding new fields to the Check Remittance template

Summary

5. SSRS Report Library

Getting started with SQL Server Reporting Services

Deployment configurations

Standard model

Scale-out model

Prerequisites for installing SQL Reporting Services

Configuring IIS for Reporting Services

Configuring IIS for Windows Server 2003

Configuring IIS for Windows Server 2008

Installing and configuring Reporting Services

Installing Reporting Services

Configuring Reporting Services

Starting Report Manager

Deploying SQL Reporting Services reports and metrics

Installing the SQL Server Reporting Services Wizard

Deploying predefined Reporting Services Reports and metrics for Dynamics GP

Listing SQL Server Reporting Services reports in the Dynamics GP Report List

Using the predefined SSRS reports

Viewing the charts and KPIs from within GP

Configuring security for Reporting Services

Assigning access to the Reporting Services website

Setting up database security for predefined SSRS reports

Modifying the canned reports with Visual Studio

Creating a new reporting metric via Report Builder

Summary

6. Designing Your Analysis Cubes for Excel Environment

Understanding the components of the Analysis Cubes environment

SQL Server Database Engine (data warehouse)

Connecting to the data warehouse

Understanding the tables in the data warehouse

SQL Server Analysis Services database

SQL Server Integration Services packages

SQL Server Agent job

Multiple tier installations

Installing Analysis Cubes

Pre-installation checklist

Installing the Server Configuration Wizard

Using the Server Configuration Wizard to deploy the cubes

Populating the data warehouse and processing the cubes

Granting security access to the cubes

Exploring the Analysis Services database

Objects in the Analysis Services database

Data source view

Dimensions

Measure groups

Cubes

Security roles

Advanced objects (KPIs, Translations, Perspectives, Partitions, and so on)

Modifying our Analysis Cubes environment

Renaming existing attributes in a dimension

Adding new attributes to a dimension

Modifying the Account Category hierarchy by editing the GLAccountCategory table

Adding GP budgets and forecasts to the cubes

Modifying the GLBudgetSetup table

Adding the Budget Measure to the Financials cube

Summary

7. Utilizing Analysis Cubes for Excel for Dynamic Reporting

Using an OLAP viewer to connect to the SSAS database

Creating a connection to the cubes

Creating a new connection

Storing connection files on network share or locally

Repointing to a different data source

Changing data source for a single pivot table

Changing data sources for an entire workbook

Using an existing connection to connect to a cube

Excel Pivot Table-Analysis Cubes Interface

PivotTable pane

PivotTable Field List

Fields Section

Measure Groups

Dimensions

Incompatible dimensions and measures

Areas Section

Creating ad-hoc reports

Using PivotTable design features to change report appearances

Changing the Report Layout

Applying styles to PivotTables

Using slicers to filter PivotTable data

Utilizing the Excel cube formulas

Basic cube formulas

CUBEMEMBER

CUBEVALUE

Combining the CUBEVALUE and CUBEMEMBER formulas

Building a Top Ten table

CUBESET

CUBERANKEDMEMBER

Creating the table

Adding a chart for visual effect

Creating dashboards from Analysis Cubes data—a few thoughts

The seven default cubes

Financials cube

Accounts Receivable cube

Accounts Payable cube

Sales cube

Purchases cube

Inventory cube

Analytical Accounting cube

Summary

8. Designing Financial Reports in Management Reporter

Management Reporter architecture

Installing and configuring Management Reporter

Installation overview

Registering Management Reporter

Configuring a data provider

Management Reporter security

Users

Groups

Companies

Navigating the Management Reporter report designer interface

Menu bar

File

Edit

View

Format

Company

XBRL

Go

Tools

Window

Help

Navigation Pane

Working with the Management Reporter building blocks

Row definitions

Column definitions

Reporting Tree definitions

Report Definitions

Report

Output and Distribution

Headers and Footers

Settings

Tools for reducing building block sprawl

Group building blocks in the Navigation Pane with the use of folders

Creating building block groups in environments with multiple entities

Finding and eliminating unused building blocks by using building block associations

Management Reporter Feature Pack 1

Summary

9. Viewing Financial Reports in Management Reporter

Overview of Report Viewer for Management Reporter

Report Library permissions

Generating reports to the Report Library via Report Designer

Navigating the Report Viewer for Management Reporter interface

Overview of Report Viewer interface

Menu bar

File

Edit

View

Go

Tools

Window

Help

Navigation Pane

Inserting external files to create report packages

Understanding Version Control

Differences between Management Reporter and FRx

64-bit compatibility

Enhanced stability and performance

SQL Server

User experience

Summary

10. Bringing it all Together

Looking back at what we've covered

Chapter 1: Reporting trends and challenges

Chapters 2-9: Reporting tools for Dynamics GP

Viewing our reporting tools in light of reporting challenges

Intended audience

Data sources

Latency

Formatting and presentation

Ad hoc queries versus traditional reports

Security

Network access and general IT infrastructure

Developer resources

The future of reporting for Dynamics GP

Summary

A. Comparing the Dynamics GP Reporting Tools Against Different Reporting Challenges

Comparing SmartList Builder, Excel Report Builder, and Report Writer

Comparing SSRS Reports Library, Analysis Cubes, and Management Reporter

Index

Microsoft Dynamics GP 2010 Reporting

Microsoft Dynamics GP 2010 Reporting

Copyright © 2011 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: April 2011

Production Reference: 1180411

Published by Packt Publishing Ltd.

32 Lincoln Road

Olton

Birmingham, B27 6PA, UK.

ISBN 978-1-849682-18-3

www.packtpub.com

Cover Image by David Guettirrez (<[email protected]>)

Credits

Authors

David James Duncan

Christopher Liley

Reviewers

Amy Walsh

Christina M. Belding

Acquisition Editor

Kerry George

Development Editor

Maitreya Bhakal

Technical Editor

Ajay Shanker

Project Coordinator

Leena Purkait

Proofreader

Aaron Nash

Indexers

Hemangini Bari

Rekha Nair

Graphics

Geetanjali Sawant

Production Coordinator

Arvindkumar Gupta

Cover Work

Arvindkumar Gupta

Foreword

As the business management solution tool of choice for over 42,000 customers, Microsoft Dynamics GP continues to drive better business processes and improve organizational performance. As a comprehensive enterprise resource planning solution, Dynamics GP offers a wide-range of functionality that can be tailored for organizations in a variety of industries. With a Dynamics GP solution in place, end users can enter and track the details of day-to-day business processes. As soon as this data is recorded in Dynamics GP, organizations possess a unique advantage over others in their industry: raw data.

But this data is useless if some level of insight cannot be gained from it. By using built-in reporting tools for Dynamics GP, as well as other familiar products, the Dynamics GP organization can turn this raw data into a more meaningful picture of trends and key performance metrics. This, in turn, allows for better decision-making, the opportunity to improve current business processes, and the opportunity to proactively lead competitors into new markets. To facilitate this process, reporting tools for Dynamics GP are designed with the end user in mind. Rather than waste time learning a clunky reporting tool, the native reporting tools for Dynamics GP are intuitive and can be put to effective use immediately. Additionally, the Dynamics GP solution offers over 200+ built-in Microsoft Excel reports that can further jump-start the end user's ability to analyze and make sense of their Dynamics GP data.

While many of these tools are intuitive enough to use, training is still a vital component to learning how to gain the most from the reporting tools employed by your organization. Resources such as the book you now hold in your hand are critical to taking the extra step in uncovering the trends locked deep within your data. Not only will this book offer insight into the many reporting tools currently available for GP, it will also offer a unique perspective on how each reporting tool can be used to meet specific challenges faced by your organization. For example, if you've ever wondered what the most effective reporting tool is for an environment that contains multiple data sources, or how to identify the GP reporting tool with the best board-room quality formatting and presentation functionality, then this book has the answer for you!

As you read through this book, remember that reporting for Dynamics GP is increasingly about taking the onus of creating and developing reports out of the traditional IT department's hands and placing it into the hands that matter most: the end user. In fact, this concept is evident with the latest Roadmap for Dynamics GP. This roadmap shows future versions of Dynamics GP adding new functionality in the area of role tailored and contextual business intelligence and reporting tools. This is seen in GP 2010 R2, the latest release for Dynamics GP 2010, which will feature new reporting tools and updates to existing reporting tools such as:

Performance improvements to the existing Report List functionality

The ability to generate Word Templates for any standard report in GP

A Report Builder Integration tool that offers the ability to view SSRS reports from within Dynamics GP

Additional out-of-the-box SSRS reports that can be deployed for instant visibility into your organization's data

And much, much more!

Happy Reporting!

Errol Schoenfish

Errol Schoenfish has been a member of the Dynamics community for over 24 years.

About the Authors

David Duncan is a Microsoft Dynamics GP certified consultant with I.B.I.S., Inc., a Microsoft Gold Certified Partner based in Norcross, GA. In addition to experience with implementing Dynamics GP, David has extensive experience in designing and providing business intelligence and reporting tools for clients who use Dynamics GP. David has also served as a content provider for the GP portion of the Sure Step 2010 Methodology.

David has developed custom SSAS cubes for several GP modules such as Project Accounting and Fixed Assets that seamlessly integrate with Microsoft's Analysis Cubes for Excel product. David has also assisted numerous clients in analyzing their strategic business plans by designing business intelligence solutions that allow them to incorporate data from multiple applications into a single reporting environment. David also has a degree from Clemson University.

When not on-site with a client, David resides in Decatur, GA with his wife, Mary Kathleen.

Acknowledgement

First and foremost, my thanks go to my colleague and co-author, Chris Liley. Without him, this book would not have been possible.

I have had the special privilege to work and learn under Andy Vabulas, Dwight Specht, and Clinton Weldon while at I.B.I.S., Inc. All three of them have mentored me and provided me many of the opportunities that enabled the creation of this book.

I am especially grateful for my colleagues at I.B.I.S., Inc., including Tan Le, Mark Polino, Abby Moore, and others. All have left an indelible impression on me.

Along with being exceptional colleagues at I.B.I.S., our technical reviewers Christina Belding and Amy Walsh deserve the highest praise for stepping forward to help Chris and I complete this project.

Special thanks is reserved for Kerry George, Maitreya Bhakal, Leena Purkait, and the rest of the Packt Publishing team for guiding us every step of the way in producing our first publication.

And last, but certainly not least, this book would not be possible without the loving support and patience of my wife, Mary Kathleen, whose encouragement helped me through many late night sessions with the more challenging chapters of this book!

Chris Liley is a Principal Consultant with I.B.I.S., Inc.; a Microsoft Gold Certified Partner based in Norcross, GA, and is a Microsoft Certified Information Technology Professional for Dynamics GP. He is a graduate of Georgia State University with a B.B.A in Accounting and has worked with the Dynamics GP product since 2001. Chris has also previously participated in the Dynamics Partner Advisory Board.

Chris' experience ranges from financial analysis, software implementation, data conversions and integrations to designing and developing customizations in both the functional and technical area of consulting for Dynamics GP.

In addition, Chris has extensive experience in designing Business Intelligence solutions and has assisted numerous clients in analyzing their business plans with these solutions.

Acknowledgement

First and foremost, my thanks and appreciation go to my colleague and co-author, David Duncan. We spent many nights in hotel lobbies working on this book and without this dedication; this book would not have been possible.

I would also like to thank our incredible management team at I.B.I.S., Inc. for allowing us the opportunity and support in writing this book.

I am especially grateful for my colleagues at I.B.I.S., Inc. It is truly a privilege to work with them each and every day. All have left an indelible impression on me as consummate professionals.

No book is the sole work of just the author and along with being exceptional colleagues at I.B.I.S., our technical reviewers Christina Belding and Amy Walsh deserve the highest praise for stepping forward to help David and I complete this project. We truly appreciate their candid comments and the professionalism it takes to critique your fellow colleagues.

Special thanks is reserved for Kerry George, Maitreya Bhakal, Leena Purkait, and the rest of the Packt Publishing team for guiding us every step of the way in producing our first publication. We could not have done this without you.

And finally I want to thank my boss and my friend Clinton Weldon. It is because of his mentorship over the past 10 years that I have been able to learn so much about ERP products and bring that knowledge to every customer that I visit.

About the Reviewers

Amy Walsh is a consultant with I.B.I.S., Inc. and Microsoft Certified Business Management Solutions Professional. She is a graduate of Georgia Military College and Mercer University with a concentration in Accounting and Finance. She has over 15 years of experience working with domestic and international enterprises ranging from start-up to established global B2B companies. Her goal is to help businesses succeed in their endeavors through process streamlining, education, and finding the right ERP solution.

Christina is a Project Management Professional (PMP), Certified Information Systems Auditor (CISA), and Microsoft Certified Business Management Solutions Professional (MCP) with over 20 years of industry experience, many of those spent implementing and managing large ERP engagements.

Christina has spent a large portion of her career focusing on ERP implementations and management, but also has a detailed background in training and development—creating custom curriculums that focus on new business initiative deployments and enterprise wide process improvement and audit.

Before concentrating predominately with Microsoft systems in 2007, she previously worked for Automatic Data Processing (ADP), and her own practice Belding Associates, LLC. (a small professional services organization).

Christina has served as a subject matter expert (SME) and content editor on several other publications. She resides in Alpharetta, GA with her husband Dan and daughters Rachael and Jenn.

www.PacktPub.com

Support files, eBooks, discount offers and more

You might want to visit www.PacktPub.com for support files and downloads related to your book.

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.

http://PacktLib.PacktPub.com

Do you need instant solutions to your IT questions? PacktLib is Packt's online digital book library. Here, you can access, read and search across Packt's entire library of books. 

Why Subscribe?

Fully searchable across every book published by Packt

Copy and paste, print and bookmark content

On demand and accessible via web browser

Free Access for Packt account holders

If you have an account with Packt at www.PacktPub.com, you can use this to access PacktLib today and view nine entirely free books. Simply use your login credentials for immediate access.

Instant Updates on New Packt Books

Get notified! Find out when new books are published by following @PacktEnterprise on Twitter, or the Packt Enterprise Facebook page.

David's dedication:

This book is dedicated to the incredible team at I.B.I.S., Inc. It's a privilege to work with them each day.

Chris' dedication:

This book is dedicated to my incredible parents who have always taught me that by giving my all to everything I do, I could accomplish anything that I put my mind too. This book would not have been possible without their love and support.

Preface

Microsoft Dynamics GP 2010 is a sophisticated Enterprise Resource Planning (ERP) system with a multitude of features and options. Microsoft Dynamics GP enables you to create and manage a variety of reports that help small and mid-size businesses effectively manage their financial and operational data.

This book will show you how to create and manage reports, know what tools to use and when, how to use them and where to find the data based on how it's being entered into the system with Dynamics GP.

It will empower you with the tools and reports necessary to use Dynamics GP data in making key business decisions. The book addresses the many challenges and frustrations a company may face when preparing to build new reports. Then it moves on to explain how to find your data in the GP system and company databases. The book then dives deep into topics such as SmartLists, SL Builder and Excel Report Builder, Report Writer, SSRS Report Library, and Analysis Cubes Design and Management Reporter amongst others. With this knowledge in hand, you will be capable of selecting the most effective tool for the current reporting environment.

What this book covers

Chapter 1, Meeting the Reporting Challenge: Our opening chapter will provide commentary on the many challenges and frustrations a company may face when preparing to build new reports. Developers tasked with report creation must be aware of these challenges and select the most effective reporting tool, or tools, to satisfy the company's reporting needs. In addition to using the discussion of challenges faced with reporting as a springboard for the rest of the book, this chapter will also provide commentary on recent reporting trends in the Dynamics GP space.

Chapter 2, Where Is My Data and How Do I Get To It?: Before we can begin utilizing many of the reporting tools covered in this book, we must have a better understanding of how Dynamics GP stores data. This chapter will provide users with helpful tips for finding and locating their data in the GP system and company databases. Knowing where to begin is a critical first step for any technical resource setting out to develop a new report, and this chapter aims to make the process of beginning a new report an easier one.

Chapter 3, Working with the Builders: SmartList and Excel Reports: This chapter begins our discussion of our first reporting tools as we introduce the SmartList and the Builders: SmartList Builder and Excel Reports Builder. Users will briefly review how to use basic SmartLists for simple reporting. Readers will learn how to deploy the Excel Reports that duplicates the SmartList favorites in Excel format and offers a live data connection that makes the reports instantly refreshable. The final half of this chapter will focus on using SmartList Builder and Excel Reports Builder tools to create additional reports beyond the standard SmartList/Excel favorites.

Chapter 4, Report Writer: This chapter covers the built-in report-writing function of GP 2010 known as Report Writer. This chapter on Report Writer will introduce the reader to the basic layout and the various functions of Report Writer. By the end of this chapter, readers should be familiar with making basic modifications to standard GP reports. Additionally, readers will be exposed to the capabilities and limitations of the new GP 2010 Word Template functionality that allows existing reports to be rendered in Microsoft Word format.

Chapter 5, SSRS Report Library: This chapter introduces the concept of utilizing the well-known SQL Server Reporting Services (SSRS) tool with Dynamics GP data. This chapter opens with tips for installing SSRS in your environment before moving into a discussion on deploying the pre-defined SSRS Reports and Metrics designed specifically for GP 2010. Finally, we will cover the use of Visual Studio to make modifications to existing SSRS reports as well as to create new Report Metrics and KPIs that can be deployed on the GP 2010 Home Page.

Chapter 6, Designing Your Analysis Cubes for Excel Environment: This is the first of two chapters which will cover the extensive Analysis Cubes for Excel reporting tool. This chapter will cover the installation of Analysis Cubes and provide details on the various components that are created by the installation. Finally, this chapter will cover some simple modifications that can be made to the Analysis Cubes data warehouse and Analysis Services database to improve the end-user reporting experience.

Chapter 7, Utilizing Analysis Cubes for Excel for Dynamic Reporting: Many readers will be familiar with some of the concepts discussed in the early portion of this chapter. Excel PivotTables are widely used throughout many organizations, even those without GP 2010, but the first part of this chapter will explore the use of PivotTables specifically with the Analysis Cubes for Excel product. From here, we will explore the use of the lesser-known CUBE formulas that prove to be a useful skill-set to know when building static reports and dashboards based on Analysis Cubes data.

Chapter 8, Designing Financial Reports in Management Reporter: Like Analysis Cubes for Excel, content for the Management Reporter tool will span multiple chapters. This chapter will introduce readers to Management Reporter and basic report design. We will provide an overview of Management Reporter installations before providing tips for navigating the Management Reporter layout. Finally, this chapter will cover the use of the various building blocks of Management Reporter for report creation.

Chapter 9, Viewing Financial Reports in Management Reporter: This chapter continues the discussion on Management Reporter begun in Chapter 8. Here, we will cover information related to the Report Viewer component of Management Reporter. In addition to discussion on report-generation, this chapter also provides information on managing reports through the use of report packages and version control. Finally, this chapter provides some commentary on the differences between FRx and Management Reporter.

Chapter 10, Bringing It All Together: This chapter brings our book to a close by combining the discussion of reporting challenges and trends broached in Chapter 1 with the reporting tools discussed in the remaining chapters. Here, we will consider each challenge in light of the various reporting tools. By the end of this chapter and book, readers will not only be familiar with each reporting tool, but they will have a better understanding of how and when each reporting tool can be used most effectively in their organization.

Appendix A, Comparing the Dynamics GP Reporting Tools Against Different Reporting Challenges: The final section of this book contains a helpful table that can be used as a quick reference guide to see how the reporting tools measure up to the various reporting challenges we have already discussed. By presenting this data in table format, readers can quickly scan across a row to see how each tool meets a particular challenge, or they can scan down a column