SAP Business One Table Structure in Crystal Reports Design

This tool pulls data directly from SQL and this is why tables and fields names should be in the disposition of designer.  Let’s review the options to learn table structure as well as recommendations to create reports:

1. Client Interface and Software Development Kit as the sources.  Try the following approach.  Login application and open the form which is based on the table which you are about to research.  In menu mark View -> System Information.  Then place cursor over the field and read it and table name on the lower left corner.  If you need to know Customer ID on AR Invoice form then you should see it as CardCode in OINV table.  Next step is detailed research in SDK where you need to open Help Center and in Search tab review whatever is known about OINV.  Probably pick A/R Invoice - Assembly – Rows.  You can do the whole job directly in SDK by opening Contents -> Customization Tools -> Database Tables Reference.  In our opinion this way is more appropriate for consultant as she or he knows that AR Invoice Header and Lines are in the category of Marketing Documents

2. Let’s now move on to Crystal Reports design recommendations and FAQ.  If all you need to do is to print Business Partners in Customer category then there is no need to do SQL programming and you can follow Wizard guidelines (we assume that at this point you are able to find table name OCRD).  However if you are building complex paper such as project profitability or customer statement then we would recommend you to hold on and first do you job in Microsoft SQL Server Management Studio.  Crystal is great tool but it is rather oriented to give you’re the instrument for dataset presentation including grouping, summaries and graphical bells and whistles.  Consider SQL View or maybe even stored procedure creation as the base for your report.  Test it in MS SQL Server Management Studio Query until you are satisfied with results.  And only then open Designer and finish the job

Let’s now answer popular questions:

Q.  Do we have to purchase CR designer license to do the job for SAP B1?A.  No – is included assuming that you are customer with annual support contract

Q.  We heard that it is special add-on in order to call CR from client applicationA.  In versions 8.8X and newer there is no need in third party add-on.  Integration is realized in core business logic

Q.  We got exactly what you mentioned above - row duplications and incorrect summaries.  Our consultants have hard time trying to trace the problem and fix it and our budget is limitedA.  The recommendation is to appeal to second opinion.  It is not necessary that your future consulting team is located next door.  Try to find organization specializing in technology consulting with strong SQL programming expertise

Please call us 1-866-304-3265, 1-269-605-4904, help@efaru.com.  We have local presence in Chicagoland, Southern California, Atlanta Georgia, South West Michigan, Houston and Dallas areas of Texas. We serve customers USA and Canada nationwide and internationally via web sessions and phone conferences (Skype is welcomed).  We are working on Brazilian ERP consulting market in Sao Paulo since 2004