Reporting. Microsoft Dynamics GP enterpri se. Dynamics GP. Christopher Liley. Create and manage business reports with.

Microsoft

_Dynamics

GP 2010

Reporting

Create and manage

business

_reports

with

Dynamics

GP

David Duncan

Christopher Liley

[

PUBLISHING

BIRMINGHAM-MUMBAI

Table of Contents

Preface

1

Chapter

1:

_Meeting

the

_{Reporting Challenge}

7

Trends

in

_reporting

8

Increased

_flexibility

9

Reporting

through

all levels of an

organization

10

Increased access to

_{report generation}

_process

11

Challenges

in

_developing

and

_{writing reports}

11

Intended

audience

12

Datasources 14

Latency

15

Formatting

and

_presentation

17

Ad

hoc

_queries

versustraditional

_reports

18

Security

20

Network

accessand

general

IT

infrastructure

21

Developer

resources

23

Summary

24

Chapter

2: Where Is

My

Data and How Do I Get to It?

25

DYNAMICS

database

versus

_company

database 26 What is

the DYNAMICS database?

26

Company

database

27

Dynamics

GP table

_{naming/numbering}

conventions

28

Tables versus table groups

28

Physical

table

_{naming/numbering}

conventions

29

Alpha

code 30

Tabletype 31

Sequence

33

Variant 33

Locating

Dynamics

GP

data

_using

the Resource

_Descriptions

windows

34

Table

_of

Contents

Fields

37

Windows 38

Table

_Import

40

Accessing

data at the

table

level

_using

SQL

Management

Studio 41

Locating

Dynamics

GP

data with additional

tools 42

Dynamics

GP 2010 Software

_Development

Kit 42

Downloading

the SoftwareDevelopment Kit 43

Using

the SoftwareDevelopmentKit 43

Support Debugging

Tool

44

Downloading

theSupport

Debugging

Tool 44

Using

theSupport DebuggingTool 45

Summary

49

Chapter

3:

_Working

with the Builders:

SmartList

and

Excel

Reports

51

Satisfying

basic

_reporting

needs with

canned

SmartLists 51 Structure

of SmartList

52

Basic

SmartList

tools

53

Narrowing ourresultlist with search criteria 54

Addingnewcolumns 56

Changing

the number of records returned 59

Achieving

fasterexportto Excelwith a

simple

dex.ini switch 60

Extending

SmartList

datato Excel

_{by deploying}

Excel

_reports

61

Deploying

Excel

_reports

61

Maintaining security

for Excel

_reports

65

Shared network folderpermissions 65

Database level

permissions

66

Accessing

and

_using

Excel

_Reports

67

ConnectingtoExcel

_Reports

via Windows

_Explorer

67

Connecting

toExcel

_Reports

via GP 2010 69

Creating

and

_publishing

new SmartLists

_{by using}

SmartList Builder 69

Understanding

the

SmartList

Builder window 70

Creating

a new

SmartList via SmartList Builder

71

Creating

and

_publishing

new

Excel

Report

formats

using

Excel

Report

Builder 77

Understanding

the Excel

_Reports

Builder window 78

Creating

a newExcel

Report

via Excel

Reports

Builder 79

Additional

_tips

and tricks for

_using

the Builders 85

Summary

85

Chapter

4:

_Report

Writer

87

Understanding

the

_reports

dictionaries and how

_Dynamics

GP treats

original

versus modified

reports

88

Storing

reportsdictionarieslocally 89

Storing

reports dictionariesona network share 89

Accessing Report

Writer

_reports

90

Setting

theDynamicsGPlaunchfile 90

Setting security

to custom/modified _reports 90

Opening

and

_navigating

the

_Report

Writer

windows 92

Setting security permissions

for

_using

_Report

Writer

92

Opening Report

Writer

92

Report

Writer

Resource windows

94

Fields 94

Data_types 95

Formats 96

Pictures 97

Strings 98

Report

Modification windows 99

ReportDefinition 99

Report

TableRelationships 100

Sorting

Definition 101

Report

Restrictions 102

Layout

102

Modifying

all

_reports

in the

_{application by using global}

modifications 104

Data

_types

105

Formats 106

Pictures 106

Strings

107

Modifying

an

existing Dynamics

GP

_report

107

Common modifications

108

Modifying

a

_{report: Adding aging}

buckets to

Payables

Trial

Balance

_Report

108

Importing

and

_exporting

customized

_reports

117

Exporting

customized

_reports

117

Importing

customized

_reports

119

Using

modified

_reports

from the

_{Reports Library}

120

Rendering

reports

in Microsoft Word

_{by utilizing}

the

new

Dynamics

GP

2010 Word

_template

feature

121

Enabling

Word

_templates

122

Installing

the

_Dynamics

GP add-in for Microsoft Word 124

Understanding

the Word

_template

_{modification process} 125

Modifying

the

presentation

of

a default Word

_template

126

Applying simple formatting changes

toan

existing template

126

Setting

the default Word

_template

foracompanydatabase 129

Adding

fields to an

existing

Word

template

131

Adding

newfields to the Check Remittancetemplate 132

Table

_of

Contents

Chapter

5:

SSRS Report

Library

139

Getting

started

with

SQL

Server

_Reporting

Services

140

Deployment

configurations

140

Standardmodel 141

Scale-outmodel 141

Prerequisites

for

_installing

SQL

Reporting

Services

142

Configuring

IIS

for

_Reporting

Services 142

Configuring

IIS for Windows Server 2003 142

Configuring

IIS for Windows Server 2008 143

Installing

and

_{configuring Reporting}

Services

144

Installing Reporting

Services 145

Configuring

ReportingServices 146

Starting Report Manager

152

Deploying

SQL

_Reporting

Services reports

and

metrics

152

Installing

the SQL Server

_Reporting

Services Wizard

153

Deploying

predefined

Reporting

Services

_Reports

and metrics for

Dynamics

GP 154

Listing

SQL

Server

_Reporting

Services

reports

in the

_Dynamics

GP

_Report

List

157

Using

the

_predefined

SSRS

_reports

159

Viewing

the

charts and KPIs from within GP

161

Configuring

security

for

_Reporting

Services

163

Assigning

accessto the

_Reporting

Services website 163

Setting

updatabase

security

for

predefined

SSRS reports 166

Modifying

the canned

_reports

with Visual Studio

169

Creating

a new

reporting

metric via

_Report

Builder 176

Summary

182

Chapter

6:

Designing

Your

Analysis

Cubes for Excel Environment 183

Understanding

the

_{components of the}

_Analysis

Cubes environment

184

SQL Server Database

_Engine

_(data

_warehouse)

186

Connecting

tothedatawarehouse 186

Understanding

the tables in thedata warehouse 186

SQL

Server

_Analysis

Services database

188

SQL

Server

_Integration

Services

_packages

189

SQL

Server

_Agent

_job

190

Multiple

tier installations

190

Installing Analysis

Cubes

192

Pre-installation

checklist 193

Installing

the Server

_{Configuration}

Wizard 194

Using

the Server

_{Configuration}

Wizard

to

_deploy

the

cubes 196

Populating

the

data warehouse and

_processing

the

cubes 200

Exploring

the

_Analysis

Services database

203

Objects

in the

_Analysis

Services database

204

Datasourceview 204

Dimensions 205

Measure groups 205

Cubes 205

Securityroles 205 Advancedobjects (KPIs, Translations, Perspectives, Partitions, and soon) 205

Modifying

our

Analysis

Cubes environment

206

Renaming

existing

attributes in a dimension 206

Adding

new attributes to adimension 208

Modifying

the Account

_{Category hierarchy}

_by

_editing

the

GLAccountCategory

table

209

Adding

GP

_budgets

and forecasts

to

the cubes

212

Modifying

the

GLBudgetSetup

table 212

Adding

the

_Budget

Measure to the Financials cube 213

Summary

214

Chapter

7:

_{Utilizing Analysis}

Cubes for Excel for

Dynamic Reporting

215

Using

an OLAP viewer to connect to the SSAS database 216

Creating

a connection to the cubes 216

Creating

a newconnection 216

Storing

connection files on network shareor

_locally

217

Repointing

to

a

different data

source

218

Changing

data sourcefora

single pivot

table 218

Changing

data sourcesforanentire workbook 218

Using

an

_existing

connection to connect toa cube 219

Excel Pivot

_{Table-Analysis}

Cubes Interface 219 PivotTable pane 220 PivotTable Field

List

221

Fields Section 221

Areas Section 224

Creating

ad-hoc

_reports

226

Using

PivotTable

_design

featuresto

_{change report}

_appearances 227

Changing

the

_{Report Layout}

228

Applying stylesto PivotTables 229

Usingslicers to filter PivotTable data 229

Utilizing

the Excel cube formulas

230

Basic cube formulas

231

CUBEMEMBER 231

CUBEVALUE 232

Combining

the CUBEVALUE and CUBEMEMBER formulas 233

Building

a

"Top

Ten" table

234

CUBESET 235

ljibl£_o[

Contents

CUBERANKEDMEMBER

236

Creating

the table 236

Adding

achart forvisual effect 238

Creating

dashboards from

_Analysis

Cubes data—a few

_thoughts

239

The

_seven

_default

cubes 240

Financials

cube

240

Accounts

Receivable cube 241

Accounts Payable

cube 241

Sales cube

242

Purchases

cube

242

Inventory

cube

243

Analytical

Accounting

cube 243

Summary

243

Chapter

8:

Designing

Financial

Reports

in

Management

Reporter

245

Management Reporter

architecture

246

Installing

and

_{configuring Management}

_Reporter

247

Installation

overview

248

Registering

Management Reporter

248

Configuring

a data

_provider

249

Management

Reporter

security

252

Users

252

Groups

254

Companies

255

Navigating

the

_{Management Reporter}

_report

_designer

interface

256

Menu

bar

258

File ₂₅₈

Edit ₂₅₈

View ₂₅₉

Format 259

Company 259

XBRL ₂₆₀

Go ₂₆₀

Tools ₂₆₁

Window 262

Help

263

Navigation

Pane 263

Working

with the

_{Management Reporter}

_building

blocks 264 Row definitions

₂₆₄

Column definitions

₂₆₆

Reporting

Tree

definitions

268

Report

Definitions

269

Report 270

Headers and Footers 272

Settings

273

Tools for

_{reducing building}

block

_sprawl

274

Group building

blocks in the

_Navigation

Pane with the

use

of folders

274

Creating

building

block groups in environments with

multiple

entities

276

Finding

and

_eliminating

unused

_building

blocks

_{by using building}

block

associations

280

Management Reporter

Feature Pack 1 281

Summary

281

Chapter

9:

_Viewing

Financial

_Reports

in

_{Management Reporter}

283

Overview of

_Report

Viewer for

_{Management Reporter}

284

Report

Library

permissions

284

Generating

reports

to the

_{Report Library}

via

_{Report Designer}

287

Navigating

the

_Report

Viewer for

_{Management Reporter}

interface 290

Overview

of

_Report

Viewer interface

290

Menu bar 290

NavigationPane 293

Inserting

external files to create

_{report packages}

294

Understanding

Version Control

298

Differences

between

_{Management Reporter}

and FRx

299

64-bit

_{compatibility}

299 Enhanced

_stability

and

_performance

299

SQL

Server 299

User

_experience

299

Summary

300

Chapter

10:

_Bringing

it all

_Together

301

Looking

back at what we've

covered

302

Chapter

1:

_Reporting

trends and

_challenges

302

Chapters

2-9:

_Reporting

tools for

_Dynamics

GP

303

Viewing

our

reporting

tools in

light

of

reporting

_challenges

303

Intended audience

304

Data

sources 306

Latency

307

Formatting

and

_presentation

309

Ad hoc

_queries

versustraditional

_reports

310

Security

311

Networkaccess

and

general

IT

infrastructure

312

Developer

resources 314

The

future

of

_reporting

for

_Dynamics

GP 315

Table

_of

Contents

Appendix: Comparing

the

_Dynamics

GP

_Reporting

Tools

Against

Different

Reporting Challenges

319

Comparing

SmartList

Builder,

Excel

Report Builder,

and

_Report

Writer

320

Comparing

SSRS

_Reports

_Library,

Analysis

Cubes,

and

_Management

Reporter

321