We often are asked by customers to write reports from the data in . 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
If your organization is located in the or 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 –
 |
More Posts By Columbus Global
- Microsoft Dynamics 365 for Finance and Operations: Viewing Purchase Requisitions
- Dynamics GP 2018: Feature of the Day – Sorting
- Grow your business on your terms with the right ERP solution
- Dynamics GP 2018 – New Workflow Functionality
- Five Ways to Make Your Supply Chain More Dynamic
Related Posts
- 3 Ways To Import Data Into Microsoft Dynamics GP – Integration Manager, eConnect & Web Services
- New Budgeting Features in Microsoft Dynamics GP 2010
- Update: New Rules Require Rental Property Owners to Issue 1099s Starting With the Tax Year 2011
- Management Reporter Replacing FRx in Microsoft Dynamics GP
- Frustrated When Closing the 2011 Year with Your Current Accounting Software? Microsoft Dynamics GP Can Help!
For those who need a distribution solution for either Crystal or SQL Sever Reporting Services (SSRS) reports, you may want to consider . It allows you to publish Crystal and/or SSRS reports (any versions) through a secure web portal and also includes a powerful scheduling engine. It is a server license so there are no user or report limits and best of all you do not need any distribution software like Report Server or Report Manager. You can publish your SSRS and Crystal reports out of the box.