Consider the Complexity of Your Data Sources When Designing Reports for Microsoft Dynamics GP Users

This article is adapted from Christopher Liley and David Duncan's new book "Microsoft Dynamics GP 2010 Reporting," which covers in detail the challenges of report development as well as the selection and use of various reporting tools for Dynamics GP.

Many of the common challenges in designing reports for an ERP audience relate to how the end user anticipates the report will be designed.  When the data required for a report has been spread across different sources in an organization, access to the data can become as much of a challenge as pulling together an accurate and useful final product.

Microsoft Dynamics GP has two types of databases: a system database (DYNAMICS) and company database(s). For first-time report developers and seasoned writers, knowing which of these databases stores a particular piece of information we need is crucial for an accurate report.

The first task of identifying where our data is located is making sure we are using the correct database! Microsoft Dynamics GP 2010 utilizes the Microsoft SQL Server platform as its database engine. When Dynamics GP 2010 is installed on our environment and a new company is created, the installation process creates several databases on a server that has been designated during the install. These databases will store all the information entered through the Dynamics GP 2010 application, and we can use SQL Server Management Studio to access the underlying tables that store this data.

The DYNAMICS database includes such things as records for each company that you create, the organizations registration information, the maximum account framework, and so on. ...