Crystal Reports for Microsoft Dynamics GP Great Plains – Overview & FAQ
Microsoft Dynamics GP is successor of Great Plains Software Dynamics and eEnterprise ERP application, and Crystal Reports was always the reporting tool of the choice for Dynamics. Historically GP Dynamics was available on Pervasive SQL and Ctree platform, where Crystal Reports were limited, due to the limitation to Pervasive and Faircom ODBC drivers.
The last version, available on Pervasive/Ctree was Microsoft Great Plains 7.5 and since version 8.0 GP Dynamics is available on Microsoft SQL Server only, and as you can expect, SQL Server opens all the possible horizons for reports development. Current Version of GP as of November 2007 is 10.0. In this small article we will open “philosophical” principles of Crystal Reports design for Microsoft Dynamics GP:
Crystal Reports is “just” a reporting tool, not and magic “all-the-problems-solver”. This means that you should not overestimate the power of report wizards, but instead design either SQL view or SQL stored procedure to accept parameters and return records set to your Crystal Report. Then all you need to do in CR is to group records, sort them, and of course apply styles and other “cosmetics”. We are placing this message in the first bullet, because we saw a lot of report design failures, where designer tried to do very complex GP tables links within CR and then apply further complex restrictions, and ended up with lines duplication, and other annoying problems, which in fact pile up if you try to fix them with similar patches and at the end of the way, report was abandoned
SQL View vs. Stored Procedure. Of course SP looks more elegant as it accepts parameters, which are translated to CR parameters, however SP is also more powerful than the view due to the ability to include temporarily tables and intermediate calculations (based on temporary tables). Temporary tables allow you to go beyond the standard techniques of grouping in SQL Select statement – this should be the real solution, which will eliminate records duplications, described in #1. If you plan to invest your time in CR practicing for GP, you should make several practical lessons where you base GP Crystal Reports on SQL view and stored procedure
Other reporting tools for GP. If you are CR developer, it is natural, that you try to apply Crystal Report in all the situations, however you should be aware that GP has very powerful pre-designed tools. If you need to create financial statements: Balance Sheet, P&L, Cash Flow – you should consider FRx report designer first, FRx links to GP General Ledger and so, if more efficient than CR in Financial Reporting. GP ReportWriter. This reporting tool gets the advantages of GP report launching screens, such as SOP Invoice form, from where you can print specific invoice – so called modified reports allow you to place your company logo on Invoice form. Other features of FRx and RW are outside of the scope, please check our publications on these products
GP Dexterity Reporting. Dexterity is IDE and programming language of GP. Dex allows you to customize GP forms and reports plus create new ones. In order to explore Dex option, we recommend you to contact GP Dexterity developer, who should help you in the selection
Calling CR from GP interface. You can use several techniques. First one is traditional – use VBA/Modifier (you will need customization site enabler license for GP), from VBA scripts you can call CR engine. Second technique is Dexterity, where you call Crystal Reports engine from your custom Dexterity sanscript code. You can also consider SDK for Visual Studio developer, allowing you to extend GP forms and of course call CR from your .Net extension application