If you deploy Microsoft Dynamics GP Corporate ERP system in your organization (this accounting, ERP, MRP, Logistics package was formerly known as Great Plains Dynamics and its predecessor Great Plains Accounting for DOS, Windows and Mac), and you are now facing the challenge to design non-financial report (if you need to build Financial Reports, such as Balance Sheet, P&L, Statement of Cash flow - then consider FRx Reporting package versus Crystal Reports), something like Sales Commission, Monthly or Quarterly Sales and Profitability
In our Dynamics GP and SAP Business One consulting practice we observed numerous pitfalls in Crystal Reports design for mid-size Corporate ERP systems and we would like to help you to avoid these known problems to start up CR development smoothly and in reasonably short time (with minimal learning curve):
1. How to make first steps. Install CR,
then launch Crystal Report wizard to see if you are comfortable with the application. Base your new report on Dynamics GP ODBC DSN (in this case it is easy to distribute report, as all GP user workstations have it preset), then try something very simple, for example create Customer List report via Wizard (here Customer Master table in Microsoft Dynamics GP is RM00101 in company database, if is often good idea to practice with Lesson Company Fabrikam, TWO database in your MS SQL Server). Try grouping your customers by customer class for example, or by State. If you are feeling comfortable and satisfied with results, let's move on to creation production Crystal Report
2. Dynamics GP Business Logic and Crystal Report design challenge. Great Plains is dedicated to mid-market or lower mid-market and you should expect business logic complexity. Let us give you few examples. First, if you are building Sales Report by Product, Subdivision or Region, likely on SOP30200 and SOP30300 tables (Sales Document Header and Lines), then you should be aware that all Sales Document types, such as Sales Quote, Order, Invoice, Return, Backorder and Fulfillment Order are stored in these tables. Obviously only Invoice and Return are posted to General Ledger and should be included into your Sales Result report (where for Sales Return you have to specially set negative multiplier, based in SOPTYP, as all numbers are positive in SOP30200 and SOP30300). Moreover, voided documents also are stored in SOP30200 and SOP30300 tables, you have to check SOP30200.VOIDSTTS field to be equal zero for non voided documents. Plus, if you are adding Credit Memos, Debit Memos, or void documents directly in Dynamics GP Receivable Management module (RM prefixed tables) - then these documents are not seen on SOP prefixed tables, but must be considered as altering your Sales results. These examples should give you typical complexity level, especially when you will be designing report for different set of Dynamics GP modules
3. SQL View or Stored Procedure as Microsoft Dynamics GP Crystal Report base. Crystal Reports is industrial reporting tool, but not the best SQL Query designer. Considering relatively complex business logic plus potentially challenging joining on SQL tables level, we recommend you to consider SQL View or even better SQL Stored Procedure as report base, where you can isolate business logic and even test report pulling results in SQL Query Analyzer. SQL Stored Procedure is more powerful, as it allows you to deploy such constructions as temporary tables in SQL joins, plus it has optional parameters, allowing you to map SQL Stored Procedure Parameters into Crystal Report parameters. Most of the failures in Crystal Report design for Dynamics GP or SAP B1 were related to the attempt to use CR wizard and use Crystal Report direct database table linking (most typical problem was resulting lines duplications)
4. Dynamics GP Tables Structure Diagram. You can choose from two options. First would be to install Dynamics GP SDK from CD #2. If you are using GP version 10 or 11 DVD or its image, please look for Tools folder, where you should be able to find Dynamics GP Dexterity and SDK folders (SDK is not the same as Dynamics GP eConnect). SDK allows you to print out complete Great Plains tables diagram. If you feel that your project is relatively simple and complete diagram is too complex to review, please login GP workstation, then Dynamics GP->Tools->Resource Description->Tables, here select product Dynamics GP, your series (Sales for example if you are working on Dynamics GP ecommerce Crystal Report), and follow your intuition if finding required table and its dependencies
5. Cross Platform and Consolidated Crystal Reports. If you are working for large multinational corporation, where you deploy several Corporate ERP applications: Oracle eBusiness Suite, Microsoft Dynamics GP, Axapta, SAP Business One, Accpac, Quickbooks, Peachtree, MYOB, and you are building Sales Report for your Corporate Headquarters, we recommend you to deploy MS SQL Server Linked Server approach and cross platform (often also referred as heterogeneous) SQL View or Stored Procedure to present data from various Corporate ERP systems in one consolidated Crystal Report
6. Crystal Report in Dynamics GP Barcode label printing. Here you may extend Dynamics GP and print Barcode Item Labels at the points of Purchase Receipt, Inventory Adjustment or Inventory Items Cycle Count. Elegant scenarios include Microsoft Dynamics GP integration with Microsoft RMS, Counterpoint, and other Point of Sale (POS) and warehouse management supporting systems
7. Crystal Reports for older versions of Dynamics GP, Great Plains and Great Plains Accounting. If your Dynamics application is on Microsoft SQL Server (2008, 2005, 2000, 7 or 6.5) - you can always deploy SQL connection and even advanced techniques as SQL View or Stored Procedure. Real challenge begins when you are on Pervasive SQL 2000/Btrieve or Ctree (Dynamics GP or Great Plains Dynamics versions 7.5, 7.0, 6.0, 5.5, 5.0, 4.0 and earlier). In the case of Great Plains Accounting you can build so-called DDF files to establish Btrieve or Pervasive SQL ODBC DSN to GPA tables
8. If your Crystal Reports case for Microsoft Dynamics GP is not described in this small publication, feel free to call us: 1-866-528-0577, or email us help@albaspectrum.com We help Microsoft Dynamics GP customers to recover non successful implementation, failed Great Plains data conversion, ecommerce integration, you may decide to call us if you are frustrated with your local Dynamics GP Partner support level
9. Crystal Reports for Dynamics GP in International Contents. If you need to combine Dynamics GP data with such language extensions as Chinese, Japanese, Russian, Korean, Brazilian Portuguese, you should consider to do it on the SQL View or Stored Procedure level, where foreign characters are entered through independent (from Dynamics GP) interface (as Microsoft Dexterity doesn't support Unicode). Crystal Report can perfectly combine ASCII English or European characters with Hieroglyphs (Chinese, Japanese, Korean) . We used this approach for Chinese and Oriental food suppliers in Chicago and Houston areas
10. Crystal Reports and Microsoft SQL Server Reporting Services or SSRS. If you separate business logic level in report design, then both tools are very similar or near identical. SSRS is completely web based, and often comes free with your Microsoft SQL Server license. On the other hand, there are probably more Crystal Reports developers on the job market, comparing to SSRS at this time, January 2010. In the future situation could change
