Microsoft Dynamics GP, or former ERP name is Great Plains Software Dynamics / eEnterprise offers programmers or software developers a spectrum of report design tools: Crystal Reports, Microsoft SQL Server Reporting Services, GP ReportWriter, MS Access, MS Excel to name a few the popular. In this little technical paper we’d like to introduce you to the design of Crystal Reports for Dynamics GP, assuming you’re new to GP and need some help getting started. We also assume here you are on MS SQL Server Database: GP 10.0, 9.0 and 8.0, if you are on GP 7.5 and below: 7.0, 6.0, 5.5, 5.0 or 4.0 where Great Plains was available on Ctree or Pervasive SQL/Btrieve, review database connection options, described in our previous articles: you will need to install the Ctree/Faircom or Pervasive ODBC drivers. Back to Crystal for GP on MS SQL Server:
1. Specifications of tables. You can install the Dynamics GP SDK from CD no. 2, however for a quick and reasonably detailed reference log into the GP workstation, Tools->Resource Description->Tables. To provide you with a popular report design scenario: historical sales order processing invoices. SOP30200 (SOP Header) and SOP30300 (SOP line) must be aligned with the SOPTYPE, SOPNUMBE fields. When these two tables are linked, it is possible to group invoices by customer and link the main customer tables: RM00101 for field CUSTNMBR
2. Report Wizard versus SQL Stored Proc or view. If you’re a beginner and if your report isn’t overly complex, you can hand your project over to the report creation wizard. If you need complex data joins, aggregations and potentially internal temp tables, we recommend you change your approach and build your SQL queries on MS SQL Server stored procedure or SQL view layers. SQL stored procedure is the most powerful tool and you are absolutely unlimited here. If anyone complains that Crystal Report is not working properly (something like record duplications, omissions) – the first question would be – is it a report based on SQL stored procedure and if not, recommend to bring the DB link logic to the stored procedure
3. Subreports. The classic report should be top to bottom row by row with grouping and summary. This classic concept does not require subreports. If someone suggests a subreport, he first checks if it can be replaced by the group.
4. Call the Crystal report from the GP. If you want to use the standard GP functionality, consider calling Crystal from the VBA interface, you will need sample VBA code and the window itself should be placed in windows modified by Modifier. Alternatively you can call Crystal from custom Dexterity items or via popular add-ons, like Rockton. However, often users open Crystal Reports independently and narrow down the result set by selecting parameters