Crystal Reports for SQL Based ERP Systems: SAP Business One, Microsoft Dynamics

Talking about specific Corporate or Small Business ERP platform – here you need to know table structure in order to produce SQL View.  In this small publication we would like to give you two examples: SAP Business One and Microsoft Dynamics.  Let’s begin with advantages of SQL View and Stored Procedure as report data pulling:

1. SQL View.  Let’s assume that you do not want to do select statement test directly in Crystal and would like to avoid such annoying issues as duplicate rows and incorrect summaries.  Plus you would like to use such constructions as SQL Union.  And in addition to that you do not want to create the whole report and then potentially realize that it doesn’t work as expected and you would have to redo tables linking and redesign pretty much the whole report.  Then what you may decide to do is create SQL View and test its results in SQL Server Management Studio

2. Stored Procedure.  CR is treating final Select statement or Union as its base.  Stored Procedure parameters are translated into Crystal parameters.  What are the advantages of Stored Procedure?  Well in SP you are not restricted to just sophisticated joins in Select statement or Union.  Here you can use parameters, temporary tables, data update (be cautious do not compromise data integrity!).  It is also possible to use Exec statement and build Select statement on the fly analyzing the set of parameters.  You can also use SQL cursors especially in building temporary tables.  Stored Procedure could give you the advantage of better performance comparing to the view as here Crystal doesn’t have to apply restrictions to the total View selection and all the job are done in high performance SQL Server.  Let’s now move on to SAP B1 tables and fields

3. SAP Business One.  If you are not doing long term programming project in Software Development Kit then there is no need to install it and review tables diagram from there.  We recommend you just login SB1 user workstation and in menu mark View -> System Information.  Let’s now assume that your custom report will need to pull the fields from Sales Invoice.  Open Sales invoice form and place the cursor over the field in question.  Table and field name information will be displayed in the bottom left corner of the user interface.  Some comments before you begin your SQL View or Stored Procedure creation.  SAP BO is three tiers Small Business ERP application.  This means that direct data update via custom SQL statements will be detected by Watch Dog service and transmitted to SAP.  Consider just data selection and try to avoid updates on the SQL Stored Procedure level

4. Microsoft Dynamics ERP and CRM Family.  There are several products where direct select from SQL Server database is OK.  Among them GP (formerly known as Great Plains), NAV (former Navision), SL (originally Solomon) and CRM.  In the case of Dynamics AX Axapta direct access to SQL Server is not recommended and instead you should consider X++ code and such development tool as MorphX.  Microsoft actually is trying deemphasize Crystal Reports and rather recommend SQL Server Reporting Services (SSRS)

5. Financial Reporting where Crystal is not recommended.  General Ledger is tightly connected with Accounting theory and double entry.  If you need to produce Balance Sheet, Profit and Loss Statement or Statement of Cash flow including consolidated versions please consider such tools as Microsoft Management Reporter or its predecessor FRx.  If you decide to recreate P&L directly in Crystal you should expect to reproduce most of FRx business logic and this is in our opinion not a feasible idea

6. Please call us 1-866-304-3265, 1-269-605-4904 (for international customers, where our representative pick up the phone in St. Joseph, MI call center) help@efaru.com.  We have local presence in Chicagoland, Southern California, South West Michigan, Houston and Dallas areas of Texas. We serve customers USA, Canada, Mexico, Brazil nationwide and internationally via web sessions and phone conferences (Skype is welcomed). Our consultants speak English, Spanish, Portuguese, Russian, Chinese. Our core expertise is in International Business