Crystal Reports for SAP Business One Combining Standard and Custom Fields and Tables

We would like to give you popular Sales Commission report design case study, where we are deploying such functionality as User defined tables and fields.  These objects are open to designer as well as end user for data entry change or deletion.  There is no need to do programming in SDK or in other tools.  Let’s begin with requirement analysis and mapping it to B1 settings:

1. Let’s assume that your customers are served by the whole team of sales engineers and project managers and you have complex commission structure.  First of all commissions are categorized and each category has different percentage.  Second challenge is that some categories are only applicable during the first year of service, while others might be permanent.  Third feature is the fact that you are reselling time units to your clients and each month your hourly cost varies based on negotiated amount with your franchisor

2. User Defined Commission Table.  Programming is not required here.  In user interface follow the path: Tools -> Customization Tools -> User Defined Tables Setup.  Here create the table with the fields according to your specification.  What is pretty amazing in our opinion – there is no need to create user interface to this new table – it is already created by standard functionality.  In order to fill commission table please follow the path: Tools -> User Defined Windows and select Commission and you should be able to open table grid with editable fields

3. Assigning commission to Business Partner/Customer.  Now we will be adding User Defined fields to the Business Partner table.  Again Customization Tools -> User Defined Fields Management and here expand Master Data and twice Business Partner.  Highlight Business Partner and you will see Add button on the bottom of the form.  What is also amazing is that fields are really appended to the table existing standard fields and table structure changes.  Here you should add several commission categories and sales representatives for each one

4. Enabling User Defined fields on Business Partner form.  Open Business Partner Master Data screen and in menu View mark User Defined Fields.  Again we didn’t do or suggest any software development in SDK – it is all in Settings.  At this point assuming that you already entered all the commission categories and specified them for each business partner we are ready to go ahead with CR design

5. Best Crystal Report design practices.  We strongly recommend you to create SQL View or Stored procedure as the base for your report and test result set directly in SQL Server Management Studio Query.  Reporting tools are architected to be just for reports.  They have reasonable data linking functionality especially for novice designer via wizards, but this is unlikely to be the best option in producing professional report.  SQL Stored procedure is even more powerful as it allows you to deploy cursor in its body or create temporary table.  Please, note that temporary tables are created in TempDB in SQL Server.  However we discourage you from updating existing SAP B1 tables, as this is subject for being monitored by so-called SAP BO Watch Dog service

6. Conclusion.  It is possible to do advanced business logic Reporting without programming by creating custom tables, adding custom fields for existing table and interface will be created automatically to expose these objects to the user.  It is recommended to base report on SQL Stored Procedure or View.  This method allows you to test result set prior to beginning the design itself.  If you will separate design and data selection parts such possible annoying problems as duplicate rows should be eliminated by methodology

7. 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)