Microsoft Dynamics GP Reports That Stand the Test of Time

We often are asked by customers to write reports from the data in Microsoft Dynamics GP.  While there are a number of reporting tools that can be used (Crystal, SQL Reporting Services, and Excel), regardless of the tool, we always develop the report logic in a SQL server stored procedure and simply return a “flat” results set.  The benefit is that we take the complexity of data assembly away from the reporting tool – the reporting tool is simply used to format the results, which look to the reporting tool like a single table, regardless of the complexity of the data assembly.  

This approach provides the “best of both worlds”.  In SQL logic, we can easily assemble data from the “Work-Open-History” movement of records in Microsoft Dynamics GP; we can easily join Receivables back to Sales Order Processing (a challenge because the “Type” value is not the same); and we can quickly develop, debug, and test the queries and logic.  Then the reporting exercise is simply formatting – grouping, sorting, and subtotaling – just organizing the results and making them look “pretty”. 

Another benefit of this approach is that the SQL stored procedures are generally unaffected by Microsoft Dynamics GP upgrades.  Because the queries are written off specific tables and columns – unless the column or table goes away – these stored procedures do not even require recompilation following an upgrade.  They really withstand the test of time. 

And as different reporting tools have come onto the scene, we still use the same stored procedures developed years ago to return results.  We have customers with reports that use the same stored procedure that were originally used with Crystal, but now also are called by Excel and SRS front-ends. Even the logic is simple – using a SQL stored procedure with your favorite reporting tool can make reporting on Microsoft Dynamics GP data a breeze and we can help with our custom development services! 

If your organization is located in the Arizona or New Mexico area, contact Sherwood Systems for assistance in determining if a technology solution can help your organization better serve your business needs. (877) 943-9696

By Sherwood Systems – Arizona Microsoft Dynamics GP Partner

Related Posts

1. 3 Ways To Import Data Into Microsoft Dynamics GP – Integration Manager, eConnect & Web Services
2. Crystal Reports vs SQL Server Reporting Services: Clash of the Microsoft Dynamics GP Reporting Titans
3. Extending Reports in Microsoft Dynamics GP