Article

Creating complex SSRS reports for Microsoft Dynamics GP without 're-inventing the wheel'

by W. Mike
Dynamics GP Specialist, Computeration,

Creating datasets for Dynamics GP reports can be a daunting task that requires extensive knowledge of the GP database structure.   But what if you could use the same stored procedures and views that GP utilizes when it retrieves this data?   With the use of the often overlooked SQL Server Profiler tool within Microsoft SQL Server Management Studio, you can do just that.

Read on for more details and a demonstration of the technique.

Let's assume that you have been tasked with creating a report that would assist with reconciling the general ledger cash account to the check book.  The accounting team would like to enter a date and have the report display the Checkbook balance, Cash Account Balance, and any difference between the two as of that date. While this feature has been added to Dynamics GP 2013, we'll use the concept for demo purposes. In addition, maybe you haven't upgraded to GP 2013 yet, so this report might help you.

To begin the report design, you need a thorough understanding of the tables in which the data resides because this report needs to show historical information. To manually attempt to write the T-SQL script that would retrieve the information needed would be a challenge for even a seasoned GP veteran.

However, with SQL Server Profiler you can easily obtain the field and table information from within Dynamics GP by running two separate processes:

  1. The GL cash account balance as of a specific date can be retrieved by running a Trial Balance report that is restricted to the cash account and has an ending date specified by the user.
  2. The Checkbook balance can be retrieved by filtering the Checkbook Balance inquiry window ...

    FREE Membership Required to View Full Content:

    Joining MSDynamicsWorld.com gives you free, unlimited access to news, analysis, white papers, case studies, product brochures, and more. You can also receive periodic email newsletters with the latest relevant articles and content updates.
    Learn more about us here

    Get full access
Accounting BI and Reporting Dynamics GP GP 2013
About W. Mike

Mike brings strong data integration and conversion skills to Computeration.  His "can do" attitude gets data integrated into new systems quickly and cleanly.  He has delved into multiple legacy accounting products to retrieve data, utilizing Microsoft Access, Excel, Visual Basic, and SQL tools to convert and integrate data.

Mike utilizes his technical skills to develop efficient and economical customizations for clients.  He works with SharePoint, Business Portal, Web Services, SQL Reporting Services, and other legacy databases and reporting technologies. 

Mike's goal is to aid clients in their day-to-day processes.  His proficiency with the tables and fields in Dynamics, along with the process flows within all the modules, facilitates his ability to quickly and efficiently complete the migration of data.

More about W. Mike