Released on: 27, August 2008
, Author: Alba Spectrum
, Audience: Software related
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, firstname.lastname@example.org, 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.
.rpt Inspector Enterprise Suite for Crystal Enterprise 10 / Crystal Reports 10
19 tools for Crystal Enterprise 10 in the most powerful, integrated, & easiest suite of tools to Monitor, Manage, Edit, Analyze, Document, Modify, Migrate & Convert + more :: one or many Crystal Reports, Schedules, Instances, Objects at the same time
.rpt Inspector Enterprise Suite for Crystal Enterprise 9 / Crystal Reports 9
19 tools for Crystal Enterprise 9 in the most powerful, integrated, & easiest suite of tools to Monitor, Manage, Edit, Analyze, Document, Modify, Migrate & Convert + more :: one or many Crystal Reports, Schedules, Instances, Objects at the same time!
Crystal Clear Reports for ACT!
Download a free trial copy of 40 Crystal Reports designed to help you get the most out of your ACT! database. The reports run right off the ACT! menu and the Crystal Reports runtime is included for free with the reports.
Crystal Reports Barcode Font UFL
Three (3) clicks to change a field to a barcode in Crystal Reports with this enhanced UFL, which supports all popular linear and 2D barcode types. Royalty-free use with a purchase of any IDAutomation.com font license.
Report Viewer for Crystal Reports
A professional Crystal Reports viewer. Free your budget. Manage and view multiple reports at once with tabbed reports, refresh report with current data, load saved data, save to disk, export, emal, show sql, edit selection formulas and more.
Barcode Generator for Crystal Reports
Create barcodes in Crystal Reports without installing additional fonts or other components with the IDAutomation Native Barcode Generator for Crystal Reports.
.rpt Inspector Professional Suite for Crystal Reports 9
16 tools for Crystal Reports in the most powerful, integrated, and easiest suite of tools to Edit, Analyze, Document, Modify, Migrate and Convert + more :: one or many Crystal Reports at the same time! For Crystal Reports 9