Microsoft Dynamics GP reporting is an integral part of GP implementation and post-production ERP support. As for a typical medium-sized MRP application, you should expect several reporting tools, especially when you think of the report genre: financial reporting, business process specific reports, etc. Although Microsoft has integrated MS SQL Server Reporting Services or SRS and is somehow recommended versus the previous tool of choice – Crystal Reports – you should do your homework in selecting the reporting tool that will do the proposed and expected job. In this article we would like to review the most important reporting tools for Microsoft Great Plains and give you an orientation session:
1. Great Plains ReportWriter or RW. This tool is integrated into the GP Dexterity environment, meaning it works from the GP UI and within the Great Plains security perimeter. RW is the tool you should look at first when customizing something you call from the GP interface: for example the invoice form for order processing. Don’t expect too much from the report writer as it has numerous limitations: it can only work from existing GP screens if parameter sets are predefined by existing standard GP logic. If you want to create custom reports in RW and call it from GP interface with custom parameter selection form, you need to create GP skill customization and call your report from there
2. Crystal Reports and SRS. These two report design tools compete in the market and are similar in some features. If you base your GP report logic on SQL Stored Procedures, the final report printing could be done with the same success of CR and SRS, so the question probably arises which tool is most convenient for you based on your skills and experience. The benefit of SRS and CR is this – you can really create cross-module and nightly reports. You should also consider comparing stored procedures to SQL views. Stored procedures allow you to pass parameters and create temporary tables in your SQL script code. This is what you cannot do in the SQL Views. However, if you are into preparing pure financial reports: income statement, balance sheet and cash flow – you need to read on
3.FRx. The advantage of FRX Report Designer over Crystal and SRS is the connection to the General Ledger GP interface. In FRx, you can also cross company boundaries and create consolidated reports. FRx allows you to create what is called reporting trees, where you can report by specific BP segments across multiple companies. Also, FRx gives you the link to the Excel spreadsheet where you have something like small branch office accounting data that you want to see consolidated with the company’s income statement
4. Microsoft Excel Report Options. Imagine realizing the fact that most of your employees in the accounting department only rely on MS Excel and do not follow company procedures to use complex tools like MS Outlook or even Microsoft CRM. In GP you can easily export data from Smart List or a more advanced tool – Smart List Builder to Excel where you can create views based on your custom SQL expressions
5. SQL select statement. If your reporting, data mining and discovery needs are changing daily and creating persistent Crystal Reports is not feasible, and if you have decent SQL programmers and developers, you should use Ad Hoc SQL Scripts with Export To Excel consider required reports. You should be aware that if your managers require you to build data warehouses on a daily basis, you should implement SQL Analytical Services with Excel pivot tables or Cognos
6. Microsoft Access Report Option. If your IT expertise covers MS Access, you should consider using an ODBC connection to the GP database and use Access Reports
7. MS Visual Studio.Net custom programming reports. If you have strong C# and VB developers, consider training them on eConnect and then they should be able to build web reporting applications. In case you want to extend the eConnect logic with direct access to GP tables, please read the GP table structure – Microsoft Dynamics GP->Tools->Resource Description->Tables
8. Archaic DB platforms. If you’re still using GP 7.5 and earlier, you may be using Pervasive SQL Server 2000 or Ctree/Faircom. If this is the case, you will need to use one of the following tools: MS Access, Crystal Reports (where you are limited to ODBC driver limitations to exceed GP module limitations), or GP Dexterity Adaptation. Even if you are using an old version, you should consider upgrading if your company is in active mode – the current version of GP is 10.0 and you need to use GP migration tool to MS SQL Server version
9. Cross-Platform Reporting. Here we suggest you to check MS SQL Server linked server concept first, you can also consider similar tools of Oracle EDI.
10. Generic ERP Reporting. If you use PeopleSoft, Oracle EBusiness Suite, Peachtree, MYOB. Then the reporting tool requires homework.