Great,Plains,Consultant,Crysta computer Great Plains Consultant: Crystal Reports Design
----------------------------------------------------------Permission is granted for the below article to forward,reprint, distribute, use for ezine, newsletter, website,offer as free bonus or part of a product for sale as longas no changes a Gone are those times when the companies and the organisations didn't need a hi-tech system to handle them. Owing to the considerable increase in the business sector and thus, an enormous increase in the complexity of the organisational struc
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. Lets 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 field2. 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 doesnt 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 procedure3. Subreports. Classical report should be from the top to the bottom line by line with grouping and summarization. Such classical concept doesnt 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
Great,Plains,Consultant,Crysta