Create and manage business reports with Dynamics GP
Knowing where to begin is a critical first step in the development process. The aim of this article is to provide helpful tips for finding and locating data in the Dynamics GP 2010 ERP system and company databases. Although we’ll discuss some reporting tools that do not require us to know the SQL database structure for Dynamics GP companies, it is still helpful to understand how GP stores its data.
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.
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.
When Microsoft Dynamics GP is first installed and some initial settings are provided, a system database will be created. This database is the DYNAMICS database. It includes such things as records for each company that you create, the organizations registration information, the maximum account framework, and so on.
Regardless of how many company databases we have, they will all share a single DYNAMICS database.
From a reporting standpoint, there is certain information located in the system database that we may need to report on at one time or another. We have provided a quick reference for this information below:
Each company that we create in Dynamics GP has its own company database. As information such as transactions, accounts, and customer or vendor data is entered through GP, this information is recorded in individual fields. These fields comprise the smallest unit of data stored. All of this data makes up a record and a record is grouped with similar records and stored in a table.
For obvious reasons, this data is segmented by company database so that each company can maintain unique records. In addition to this transactional data, numerous additional company setups exist in the company database. As with the DYNAMICS database, we may need to report on some of these company system setups. Here is a quick reference to the more common company setup tables:
Dynamics GP has a rather interesting and sometimes frustrating table naming structure. When developers or consultants first see this, they are overwhelmed, to say the least. However, once you learn that there is a rhyme and reason to the madness, it actually makes a lot of sense and it is quite easy to follow and locate the tables that we need.
When data is entered into windows via the Microsoft Dynamics GP application, that data is stored in tables in the underlying SQL database. In most cases, data entered via a single process can be stored in two or more tables. In such cases, it is common for these tables to be grouped together by a certain naming convention. For example, entering journal entry information may update the Transactions Work table (contains General Ledger transaction header information), the Transaction Amounts Work table (contains the General Ledger transaction distributions), and the Transaction Clearing Amounts Work table (contains the General Ledger clearing transactions distributions). These make up what are called Table Groups. These table groups are also referred to as logical tables.
Each Microsoft Dynamics GP table has three names: a technical name, a display name, and a physical name. The technical name is used solely by the software and will usually be seen in some alert messages instead of the display name. The display name is the name that will appear in most of the alert messages generated by the system and is typically the name used for a given table when referring to it in speech, for example, Vendor Master. The physical name is the name that will be found in the SQL database when looking in Microsoft SQL Server Management Studio.
When working within the context of a SQL database to generate reports, developers and consultants will make use of the table physical names. A quick scan through the various tables in a standard GP install reveals a bewildering array of table numbers. How can there possibly be any rhyme or reason to these table names? Surprisingly, it does actually follow a certain pattern. For the most part, the table numbering follows a special convention. This schema packs a lot of information into a small number, and it can help developers and consultants know where to begin looking for their data.
As Dynamics GP has grown into a more comprehensive accounting solution, it has expanded, in part, by incorporating third-party applications into the solution. While many of the third-party programmers tried to stick within the relative bounds of the GP physical table naming conventions, as we will soon see, this is not always the case. So, while many of the tables that belong to the core GP modules maintain a fairly standard numbering convention, we will find that this does not hold true for all GP modules.
Generally speaking, GP table physical names contain a two or three digit alpha prefix followed by a five digit number. The three digit prefix represents the module for which the table holds data. The numbers that follow identify what type of data is held in the table. For example, is it posted transaction data? Or, is it information related to the module setup?
As we see in the next image and following sections of this chapter, the numbering schema for a Dynamics GP physical table can be broken down to reveal information about the kind of data that is found in that table.
Let’s begin by taking a look at the alpha-prefix for these tables. We have put together a handy reference of some of the most common prefixes and the modules that they represent:
PrefixModulePrefixModuleAAAnalytical AccountingMRPMaterial Requirements PlanningAFAdvanced FinancialsMXLSAudit Trails/Electronic SignaturesASISmartListNLBNavigation List BuilderBMBill of Materials (Mfg)OCSales Configurator (Mfg)CFMCash Flow ManagementOSRCOutsourcing (Mfg)CLMCertification ManagerPAProject AccountingCMCheckbook MasterPDKPersonal Data Keeper (Proj. Acct.)CNCollections ManagementPMPayables ManagementCPCapacity Requirements Planning (Mfg)POPPurchase Order ProcessingDDDirect DepositPPRevenue Expense DeferralsECEngineering Change Management (Mfg)QAQuality Assurance (Mfg)ERBExcel Report BuilderRMReceivables ManagementEXTExtenderRTRoutings (Mfg)FAFixed AssetsSCSales Forecasting (Mfg)GLGeneral LedgerSLBSmartList BuilderHRHuman ResourcesSOPSales Order ProcessingICJCJob Costing (Mfg)SVCField ServiceIVInventorySYSystem/Company SetupIVCInvoicing (Sales)UPRPayrollMCMulticurrencyVATIntrastatMEElectronic Reconcile (EFT)WCWork Centers (Mfg)MOPManufacturing Order ProcessingWOManufacturing Orders
In some modules, such as Manufacturing, tables are broken down even further with Routing tables represented by one set of digits while Material Requirements Planning data is found in tables represented by another set of digits. More commonly, however, entire modules are generally represented by a single alpha code, such as is the case with Project Accounting where all project transactions, billing, and revenue recognition tables are represented with the same alpha code.
Once we’ve identified the module in which our data might be stored, our next thought should be towards the type of data that we are trying to find. Before we return to the numbering convention, let’s take a look at the various types of data that exist in GP tables:
In terms of the physical naming convention for GP tables, the data type is represented by the first digit following the module code. The following table contains the various table types and their associated number in the numbering convention:
Table TypeNumberMaster0Work1Open2History3Setup4Temporary5Cross Reference6Report Options7
The next two digits in the numbering convention make up the sequence number. This number indicates the logical table to which the table belongs. As we discussed earlier in the article, logical tables are related tables, or table groups, that share similar data. Not only do these table groups share similar data, but they also share the same data type and sequence number when it comes to the physical table numbering convention.
For example, let’s consider the following set of logical tables:
These tables comprise the Payables Vendor Master Logical File table group. We can easily see this by the numbers that follow the module code. First, we see that these tables share the same data type—remember, 0 means these are Master tables—and second, we see that these tables share the same sequence number. Although we need other tools to help us determine the name of the table group, we are able to easily scan through a list of table physical names in SQL Management Studio and see that these tables are in the same table group.
The final two digits of the physical numbering convention represent the logical group variant. Within a logical group, numbers are incremented sequentially. This is evident in our example using the Payables Vendor Master Logical File above; as we see the final two digits of each table increment by one.
In table groups related to transactions, the variant often distinguishes between header tables, detail tables, distribution tables, and other related tables.
Keep in mind, what we have discussed is only a general naming and numbering convention for GP tables in their SQL databases. Unfortunately, to the frustration of developers and consultants everywhere, these conventions do not hold true in all cases! At the very least, knowing this convention can point us in the right direction. We can rely on other tools and resources to help us pinpoint the right table when these conventions fall short.
I remember deciding to pursue my first IT certification, the CompTIA A+. I had signed…
Key takeaways The transformer architecture has proved to be revolutionary in outperforming the classical RNN…
Once we learn how to deploy an Ubuntu server, how to manage users, and how…
Key-takeaways: Clean code isn’t just a nice thing to have or a luxury in software projects; it's a necessity. If we…
While developing a web application, or setting dynamic pages and meta tags we need to deal with…
Software architecture is one of the most discussed topics in the software industry today, and…