SAP Business One Crystal Reports Integration and Design

This is traditional reporting tool and numerous programmers and consultants are comfortable in building reports in Crystal.  Let’s try to get into specifics of SAP B1 and probably recommend the ways to make bullet proof and reliable report, which doesn’t produce such annoying things as duplicate lines, wrong summaries, etc.  Let’s begin with the popular method to find the table and field name that you need to deploy in your report:

1. Table and Field name.  Popular method is the following.  Login user interface and open the form which has the record that you would like to place on your custom report.  Click on View menu on the top left corner and mark System Information.  Now if you place cursor over the field in question on the left bottom section you should see the table and field names.  This is good enough to give you the idea about the table to research.  Tables and fields are also documented in Software Development Kit if you prefer the research to be done without bothering system users

2. Report design learning curve.  It is probably not a good idea to assign the consultant who is making her or his first steps in discovery Crystal as a tool.  It is often a temptation to begin design via Wizard and begin linking tables directly in report designer.  What we saw people are doing in making their first steps is when they do not see real relations between the tables they are trying to fix inefficient report by placing several sub-reports.  These sub-reports make the hosting report more fancy and complex to analyze in the case of such popular problem as duplicate rows

3. Professional Report Building recommendations.  The first recommendation is to separate report from its base.  It is good idea to build the base in the form of SQL View or even better Stored Procedure in MS SQL Server.  Let’s look at SQL View method.  Here you can test the result set before even opening reporting tool.  And this is good point, because you should be sure that you are not leading your future report with the necessity to build links between tables and this eliminates the necessity in future duplicate lines debugging.  Stored procedure gives you even more power, comparing to the view.  It allows you to deploy cursors in its body and even build temporary tables to stretch linking power.  Stored procedure parameters are exposed in the report as its own parameters

4. Custom SQL objects and Watch Dog service.  SAP BO is three tiers application, meaning that application server is handling data adding, modification and deletion.  Reporting obviously just pulling data from the tables (we assume that you do not have the idea to update records in report based on stored procedure, this would be a bit strange).  It is not a good idea to push data into SB1 via SQL insert or update statements.  Watch Dog is specially created to trace such attempts and report to SAP.  When you are creating the view or stored procedure for solely reporting purpose – you may decide to create these objects in the company database.  This is probably fine if you stop Watch Dog service and do not turn it on in the future.  We also recommend you to consider the idea to create custom SQL database and place all the views and stored procedures there.  When you are calling report – pass company database as the parameter of the stored procedure.  In the case of the view – the way is to have separate one for each company, as view doesn’t accept parameters

5. Second Opinion.  Sometimes we hear sad stories about unhappy customer who contracted consulting firm and the report was designed with numerous problems.  One of the most popular is report duplication.  And the cause is typically the very complex table link produced in CR directly often with report design wizard.  Recommended remedy is to build SQL View or Stored Procedure, test it and when the result set is satisfying begin the design from scratch

6. Which reports are the most popular in being designed in Crystal?  We would dare to name sales commission, custom project profitability, customer statement.  Also SAP B1 allows you to add user defined fields to existing tables, such as Sales Invoice line for example.  These fields could be made available on the invoice form in the matter of minutes for data entry without any programming in SDK.  Plus you can create User defined tables.  These objects are exposed to reporting

7. How about SSRS or Microsoft SQL Server Reporting Services?  This tool seems to be free with SQL purchase and it is exposed through web interface.  Well, if you create SQL Stored Procedure or View – is it really matter if you deploy SSRS or Crystal for final report layout design?  Probably not…  SSRS however should be called from web browser, it is a bit more difficult to make it part of SAP Business One user interface

8. Some facts about Business One.  We believe that the direct competitor is Microsoft Dynamics GP and the number of B1 installations in USA is lower comparing to Great Plains.  However BO is in active expansion mode, while GP is serving existing customers with some expansion.  Business One is also localized in the majority of international regions.  Localization has two aspects: foreign language user interface and data entry and compliance to foreign country legislation.  GP is available in English, Arabic and Spanish speaking countries in Latin America.  Current version is 8.81 (we are writing this paper in July 2011) where former A and B blends are merged and you can now deploy one MS SQL Server to host B1 companies for all your foreign subsidiaries and open user interface via Citrix or Microsoft Terminal Server.  SB1 is not available in Arabic

9. Where CR might not be really the great choice.  We do not recommend it in designing Financial Statements from scratch, such as Balance Sheet, Profit and Loss and Statement of Cash Flow.  Consider instead giving more chances to the financial statements in the user interface, they are flexible and powerful

10. Please call us 1-866-304-3265, 1-269-605-4904 (for international customers, where our representatives pick up the phone in Naperville and 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