Dynamics GP Crystal Reports Design Notes

Before we do our details, please note that there are reporting tool selection guidelines, where you categorize reports as Financials (Balance Sheet, Profit and Loss Statement, Cash Flow Statement, GL Trial Balance), Business Documents or Forms (Sales Invoice, Purchase Order, Packing Slip, Packing Ticket, Bill of Lading, Stock Count WorkSheet) and Managerial Reports (Sales Commission, Project Profitability, Sales By Product Lines), then you think about which Reporting tool is most suitable for each category (likely FRx is best fit for Financial Statements, Crystal Reports and SSRS are equally good for Managerial Reports and Advanced Business Forms, plus GP Report Writer does wonderful job in modifying Business Forms - placing your company graphical logo, comments, etc, please note that RW has natural advantage to be considered first, as it is naturally integrated and called from Dynamics GP User Workstation screens).  All three tools should be considered in concurrence in printing Barcode Labels: CR, RW and MS SQL Server Reporting Services (above we used abbreviation SSRS for this tool).  Plus, you can use Reporting features and functionality from such products as Microsoft Access, Excel (including Pivot Tables in Data Warehousing), Cognos, Dynamics GP Analysis Cubes.  All these tools mentioned should give you reasonable ground for initial research, in this small article we will try to concentrate on Crystal Reports design for Great Plains:
1. Crystal Reports for GP technical aspects.  In our opinion for mid-size Corporate ERP product, such as Microsoft Dynamics GP, tables structure and data flow including work transactions, batch posting logic, moving open transactions to history, is too complex for such intuitive and self apparent tools as CR Wizard.  We would rather recommend you to separate report presentation level from its DB engine (which should be probably SQL View or Stored Procedure).  You can certainly try to experiment with Crystal Report wizard, especially when your desired goal is simple Customers export with some selection criteria (by Zip code or State for instance, in this case you place restriction on customer address record in RM00102 table).  Just to give you an idea where CR wizard is not the best option - imagine Sales Commission report, which should only accrue commission for paid invoices, with Credit Card Fees to be subtracted from commission principal, plus only for the projects delivered (drop ship purchase orders completed).  That type of report is not possible (or if you can do it in CR wizard, let me know and I will ship you small pocket aquarium).  And what is recommended for such advanced reports is MS SQL Server View or (even more flexible) SQL Stored Procedure
2. Dynamics GP ODBC openness.  Since GP version 8.0 Microsoft Dynamics GP is only available on MS SQL Server DB platform.  However you may already know that Crystal Reports is a way more flexible, it is very comfortable with ODBC compliant queries.  For you it may mean that earlier Great Plains Dynamics versions on non Microsoft SQL Server platforms, such as Ctree or Pervasive SQL 2000/Btrieve could be easily automated reporting wise via CR
3. Where Crystal does the best job.  CR supports Unicode (Chinese, Japanese, Korean hieroglyphs based alphabet).  CR is absolutely perfect in Graphical Report design (especially when you are in high expectation about report company logo printing, including PDF documents).  As you may already learned that Microsoft Dynamics GP doesn't support Unicode and it is not translated (in its Dexterity string resources) to such languages as Russian, Czech, Romanian, Ukrainian, Moldovan, Portuguese (including Brazilian Portuguese).  CR supports most of the languages and collates in MS SQL Server, in the case when you would like to supplement your Microsoft Dynamics GP Corporate ERP with the link to MS SQL Server Custom DB, which supports targeted language
4. Crystal Reports versus SSRS.  There might be opinions that Crystal Reports is more matured reporting tool comparing to SSRS.  However the pluses about Microsoft Reporting Services might be related to the fact that SSRS is initially web based tool, and its reports could be accessible worldwide by your company sales people (or via internet connection locally if they are working from homes)
5. Crystal versus Report Writer. One of the most popular routines, that is in question is Barcode Inventory Items Labels printing.  Both tools RW and CR are good.  In our opinion Crystal Reports has more flexibility and more precise in WYSIWYG.  Crystal is also good competition for RW in traditional ReportWriter reports modification areas, such as Sales Order Processing Invoice form.  Report Writer SOP Invoice form could be reasonably modified - if you would like to place data from non associated table, you will have to add this table in Reports.dic table relation (to become associated with SOP10100 or SOP10200, for example). As RW is Dynamics GP Dexterity application, it is limited in table linking.  In Crystal you have no limits (assuming that two tables have the field, which could be uniquely referenced)
6. Crystal Reports for Great Plains on Pervasive SQL 2000/Btrieve, Ctree.  If you are on legacy Great Plains Software Dynamics version: 7.5, 7.0, 6.0, 5.5, 5.0, 4.0, 3.2 on Pervasive SQL 2000 (formerly known as Btrieve) or Ctree (Faircom, as we could remember Ctree version was also available on Macintosh platform as Great Plains Dynamics on Mac 4.0 and earlier versions), you can install either Pervasive SQL ODBC driver (or Ctree driver) from GP legacy CD#2.  If you do so, you can connect in CR to your old Great Plains Database
7. Crystal Reporting for Great Plains Accounting for DOS or Windows.  ODBC connection was introduced in GPA version 9.2 (and also available for Great Plains Accounting 9.5).  If you are on these latest versions of GPA, please read ODBC documentation (available in PDF format from GPA CD) on how to create DDF (Data Definition File) files - you will need them for Btrieve or Pervasive SQL ODBC driver.  Please, note that GPA could be moved from Novel or WidowsNT server to Windows 2003 or WindowXP workstation - in this case you will need to redeploy GPA on Pervasive SQL Server 2000 SP2 or SP3
8. OK, at that point we feel that this article is complete.  Again we are not writing official user manual, we are rather in Corporate ERP popularizing business, meaning that if you are current or perspective Dynamics GP, Great Plains Dynamics or Great Plains Accounting customer, and if your question is not answered, please review our articles on our website, or contact us via email or call our free 866 number
9. How to get help?  Please, feel free to call us: 1-866-528-0577, outside of USA: 1-630-961-5918 or email us help@albaspectrum.com  We are very technical and real Dynamics GP Dexterity, SAP Business One SDK and Microsoft CRM programming gurus.  We have Great Plains Software Development Factory and could support unlimited Dynamics GP Customization and Programming needs.  Plus we speak English, Chinese, Spanish, Portuguese, Russian, and not only as native speaking sales folks, but as real technical consultants.  If you prefer skype: albaspectrum