SAP Business One – Sales Commission and Customer Statement Reports in Crystal Recommendations
Posted On June 30, 2021
As with most mid-market ERP and accounting applications, SAP B1 has customizable commission and invoice forms. However, being flexible in the sense of creating user-defined fields and tables, your vision and formulas will most likely be based on these objects. It is natural to decide that the design work should be done in CR, since SAP has this tool and recommends it for managerial reports. We will try to overcome the customization and design problems and recommendations to avoid them. This document is not intended to be a technical guide for the programmer, but rather a collection of frequently asked questions-style recommendations:
1. Commission formulas. Real life is rich with case studies and we have seen clients where the formulas are multi-tiered and have an effective date from and to just to open the list of possible scenarios. If you recognize your case, then a good idea is to create some user-defined tables and use them for calculations.
2. Declaration. In various industries, you may have parallel units that should be present on the printed form. For example, they could be billed and actual minutes in advertising.
3. User-defined fields and tables. You do not need to develop software to create these objects. They could be created in the user interface: Tools -> Customization tools -> Management of tables and fields defined by the user and from here use your intuition or read the user manual. The good thing here is the fact that these objects are created in SQL tables in metadata and are version update neutral. So feel free to implement this technique. Often you send customer invoices or sales orders from a database or external application. In this case, it’s a good idea to send them to user-defined tables first, and then implement C # software development kit or VB programming in Microsoft Visual Studio to transform the rows into actual invoices and orders.
4. Design. Now let’s talk about the design itself. It is paradoxical but our recommendation is to start this process in SQL Server Management Studio queries. Crystal is the perfect tool, but if formulas change in the future, it can be difficult to track these changes in the Designer tool. It is much easier to create an SQL view or even a stored procedure where all the math is done and the report is based on these SQL units. If you are designing something complex with formulas, you might be disappointed with the design results that you get row duplications and other annoying errors. SQL is better positioned to do math. The general idea is as follows: test your query in SQL until you are satisfied with the result and only after opening the CR Designer tool to finish the design and cosmetic elements such as the company logo and other graphic elements
5. Now table and field structures. There are two ways to discover them. First is to print it from the SDK in the form of a complete and very complex diagram. And the second option is to switch to the View System Information menu. Then open the form with the table and field in question and hover over. Take a look at the bottom left corner where you should be able to read the names. Try it on your own and see if you get the same results. The sales order header is housed in ORDR and the lines in RDR1 tables, respectively.