As in the majority of 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 chances are high that your vision and formulas are based on these objects. It is natural to decide design work to be done in CR as SAP owns this tool and recommends for managerial reporting. We will try to come through customization and design pitfalls and recommendations to avoid them. This paper is not intended to be a technical guide for programmer as it is rather FAQ style collection of recommendations:
1. Commission formulas. Real life is rich in case studies and we have seen customers where formulas have several tiers and they have effective date from and to just to open the list of possible scenarios. If you recognize your case then good idea is to create few user defined tables and use them for calculations
2. Statement. In various industries you might have parallel units that should be present on the printed form. For example it might be billed and actual minutes in advertising
3. User defined fields and tables. There is no need to do software development in order to create these objects. They could be created in user interface: Tools -> Customization Tools -> User defined tables and fields management and from here use your intuition or read user manual. The nice thing here is the fact that these objects are created in SQL tables in metadata and they are neutral to version upgrade. So feel free to deploy this technique. Often you are sending customer invoices or sales orders from external database or application. In this case it is good idea to send them first to user defined tables and then deploy Software Development Kit C# or VB programming in Microsoft Visual Studio to transform the rows into real Invoices and Orders
4. Design. Now let’s talk about the design itself. It is paradoxical but our recommendation is begin this process in SQL Server Management Studio queries. Crystal is perfect tool but if formulas change in the future it might be difficult to follow these changes in Designer tool. Much easier is to create SQL View or even stored procedure where all the mathematics is done and base report on these SQL units. If you are designing something complex with formulas then you might be disappointed with design results getting rows duplications and other annoying bugs. SQL is better positioned to do math. General idea is the following – try your query in SQL until you are satisfied with result and only after open CR Designer tool to finish layout and such cosmetics as company logo and other graphical elements
5. Now fields and tables structures. There are two ways to discover these. First is to print it out from SDK in the form of complete and very complex diagram. And the second option is to switch in menu View System Information. Then open the form with the table and field in question and place cursor over. Take a look at bottom left corner where you should be able to read names. Try it on your own and see if you got the same results. Sales Order header is hosted in ORDR and lines in RDR1 tables respectively.
