Microsoft Dynamics GP 2010 Reporting电子书

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