Microsoft Dynamics GP Reporting: Overview For Consultant

Microsoft Great Plains Dynamics GP provides a range of reporting tools thinktribe. These include Dexterity ReportWriter (MS SQL Server Reporting Services), FRx (which is actually an old legacy dex tool), Crystal Reports and SQL Queries (exported into MS Excel), Smart List Builder and Smart List Builder. Sometimes, it is difficult to understand the capabilities of each reporting tool. Let’s consider this philosophically: Just because you have a tool that creates screws for car assembly, it doesn’t necessarily mean that the newer version will allow your car to run faster or allow you produce a new model.

o Limitations in Reporting Tools. Crystal Reports (or MS SQL Server Reporting Services) (SRS) typically allow you to link GP Tables in a visual linking program and then create your report body. You can then group the data and show summary values by groups. However high your hopes that a newer reporting tool will work miracles for you, we recommend that you look into the report engine. It is usually designed outside of the reporting tool.

SQL Views. SQL Server allows you to go beyond the limitations of a reporting tool. SQL Server lets you create views where you can combine select statements and cross databases. SQL views are great tools. However, you sometimes need to go beyond that to SQL scripting.

o SQL Stored Method. A SQL Stored Procedure can be used to base your Crystal Reports or SRS (sometimes known as stored proc). Procedure is flexible because you can add variables, parameters and create temporary tables to join data from different sources into one union. You will switch to stored procedure when you require temp tables, parameter and exec commands (when you create your select statement from text).

o GP Report Wizard. These are our recommendations. You should first link tables using the reporting tool. If this method fails, you can try SQL view concept. SQL Stored Procedure may be an option if View is not possible. However, if you are not confident enough with your knowledge of SQL Stored Procedure, then you might consider temporary tables. These tables should enable you to create final result sets.

Leave a Reply