There are many ways to extract data from Microsoft Dynamics GP. However, finding the most efficient and productive option to use for each situation can be tricky. I’m going to provide a quick overview of each option along with my recommendation of which methods are best for different reporting scenarios.
First up is the inquiry windows within GP. These are typically best for looking up specific information about single transactions. If you know the customer and invoice number, for example, you can quickly find the invoice and related transactions using the hyperlinks within each window. However, you are limited in your ability to sort or customize the way the data is displayed, so I don’t typically recommend this option if you’re looking for more data than just a single transaction, such as reviewing all the invoices you’ve sent to the customer in a month.
When inquiry windows won’t do the trick, SmartLists are the next best thing. They allow you to sort, customize column data and position, add restrictions, and they easily export to Excel or Word. SmartList is a great tool for finding things such as what invoices are currently outstanding for a vendor or viewing all the customer invoices posted yesterday. Information is presented in an easy-to-read table and they are relatively easy to customize. Users also have the ability to drill into the detail window by double-clicking records in the list. Using the Export Solutions feature, you can create macro-enabled excel sheets that run during export to perform formatting and editing tasks automatically so you don’t have to repeat these tasks every time you export your list to Excel. Since information is displayed in a table, this is not a valid option for creating financial statements or other reports that need to be displayed in a different manner.
GP also provides some out-of-the-box reports that are useful in certain situations. The stock invoices, PO’s, packing lists, and other related documents will usually suffice assuming you don’t have different logos for different customer invoices or things of that nature. The AR and AP aging reports or financial trial balance are great for quick viewing when you don’t need to perform additional analysis on the data. Report parameters can be saved to use each time you run the report. However, the Report Writer tool can be difficult to work with, and there are little options available to customize the reports for presentation purposes.
Replacing the FRx reporting tool brings Management Reporter into the picture. The two are very similar, but the user interface in Management Reporter gives us a much-desired overhaul into the 21st century from FRx. Management Reporter is a great tool to use for creating financial statements that are attractive and presentable. It requires a bit of training to understand how to build new column and row definitions from scratch, but once that’s done it is easy to quickly create and update reports. MR also provides the ability to consolidate reports across multiple companies. With the use of reporting trees, you can define and view reports by department or account segment. There is a drill-back feature that allows you to open the account detail window in GP directly from MR as well.
One of the more advanced options is SSRS or SQL Server Reporting Services reports. These reports are accessible through a web browser, and they don’t require a Dynamics GP license to gain access, although they can be accessed through GP as well. SSRS allows you to build reports completely from scratch using Report Builder, meaning the customization options are endless. Microsoft gives you a fair amount of stock SSRS reports that can be deployed for GP at any time, including AR and AP agings and an inventory stock status report. All SSRS reports also easily export to Excel. The downside to SSRS is that more advanced reports require extensive SQL knowledge.
Since the reports are all based on tables and views in SQL, you need an intermediate knowledge of coding in SQL to write the queries.If you prefer to work in Excel and you end up exporting all your reports in order to manipulate them anyway, then Excel reports might be the answer. Connections can be established between SQL and Excel to pull data from tables or views directly into Excel and make the data refreshable. Excel reports, like SSRS, don’t require a GP license to access the reports. Any pivot tables or graphs you create based on the data will be updated as you refresh the data source in the worksheet. This is a great place to create custom dashboards for management to view critical information quickly and easily. A sales dashboard may be linked to multiple tables and views and contain information on your top 10 customers, top 10 salespersons based on dollars, top selling items based on units, or any other desired information. Again, because the reports are pulling data from views and tables, some SQL knowledge may be required to create your own custom views.
The future of GP seems to be leading toward Jet reports. Jet reports are Excel based, and they have the ability to integrate with multiple ERP applications, including Dynamics GP. Jet reports have very extensive reporting and analytical capabilities, as well as offering a high degree of security. You should expect to see more reliance on Jet reports in the coming years.