Great Plains Consultant: Crystal Reports Design

Microsoft Dynamics GP, or former ERP name is Great Plains Software Dynamics / eEnterprise gives you, programmer or software developer vide spectrum of report design tools: Crystal Reports, Microsoft SQL Server Reporting Services, GP ReportWriter, MS Access, MS Excel to name some of the popular. In this small technical paper we would like to introduce you into Crystal Reports design for Dynamics GP, assuming that you are new to GP and need some starting help. We also assume here, that you are on MS SQL Server Database: GP 10.0, 9.0 and 8.0, if you are on GP 7.5 and earlier: 7.0, 6.0, 5.5, 5.0 or 4.0 where Great Plains was available on Ctree or Pervasive SQL/Btrieve, please review database connection options, described in our earlier articles – you will need to install Ctree/Faircom or Pervasive ODBC drivers. Let’s come back to Crystal for GP on MS SQL Server:

1. Tables specifications. You can install Dynamics GP SDK from CD #2, however for quick and reasonably detail reference, please login GP workstation, the Tools->Resource Description->Tables. To give you a popular reports design scenario: Historical Sales Order Processing invoices. SOP30200 (SOP Header) and SOP30300 (SOP line) should be lined by SOPTYPE, SOPNUMBE fields. When you have these two tables linked, you can group invoices by customer and you can link customer master tables: RM00101 by CUSTNMBR field

2. Report Wizard versus SQL Stored Proc or view. If you are beginner and if your report is not overly too complex, then you can hand your project over to report creation wizard. If you need complex data joining, aggregation and potentially internal temporary tables, we recommend you to change the approach and build your SQL queries on the MS SQL Server stored procedure or SQL view levels. SQL stored procedure is the most powerful tool and here you are absolutely unlimited. If somebody is complaining that Crystal Report doesn’t work correct (something like record duplications, omissions) – first question would be – is report based on SQL stored procedure, and if not – recommend to port DB linking logic to stored procedure

3. Subreports. Classical report should be from the top to the bottom line by line with grouping and summarization. Such classical concept doesn’t require subreport. If somebody suggest subreport, first check if it could be replaced by group.

4. Calling Crystal report from GP. If you would like to use GP standard functionality, consider calling Crystal from VBA interface, you will need sample VBA code and the window itself should be placed into modified windows by Modifier. Alternatively you can call Crystal from Dexterity custom objects or via popular add-ons, such as Rockton. However often users open Crystal Reports independent and the narrow down the result set by selecting parameters

Andrew Karasev, Alba Spectrum, LLC, http://www.albaspectum.com, help@albaspectrum.com, 1-866-528-0577, serving Great Plains Dynamics GP community since 1994. Please visit our information portal Pegas Planet: http://www.pegasplanet.com. GP, SAP B1 local service in Chicago, Naperville, San Diego, Atlanta, Houston, Dallas. We specialize in ERP remote support and we cover the whole territory or the USA, Canada, Mexico, Central America, Caribbean, Pacific, UK, Australia, New Zealand. US States covered: IL, CA, FL, WA, DC, NY, MI, PA, AZ, TX, LA, GA, AL, HA, IN, WI, AL, KA, IO, NV, NM, CO, SC, NC, DE, MA.