SAP Business One: Customer statement and sales commission reporting in Crystal Recommendations
Like most mid-market ERP and accounting applications, SAP B1 has customizable commission and billing forms. However, being flexible in the sense of creating user-defined fields and tables, it is very likely that your vision and formulas will be based on these objects. It is natural to decide that the design work is done in CR, since SAP owns this tool and recommends it for management reports. We’ll try to overcome customization and design pitfalls and recommendations for avoiding them. This document is not intended to be a technical guide to programming, but rather a collection of FAQ-style recommendations:
1. Commission forms. Real life is rich in case studies and we have seen clients where formulas have multiple levels and are dated effective from and to just to open up the list of possible scenarios. If you recognize your case, a good idea is to create some user defined tables and use them for the calculations.
2. Statement. In various industries, you may have parallel units that should be present on the printed form. For example, you could be billed and actual minutes in advertising
3. User-defined fields and tables. There is no need to do software development to create these objects. They can be created in the user interface: Tools -> Customization Tools -> User Defined Tables and Fields Management and from here use your intuition or read the user manual. The nice thing here is the fact that these objects are created in SQL tables in metadata and are version upgrade neutral. So feel free to implement this technique. Often, you send customer invoices or sales orders from an external database or application. In this case, it’s a good idea to first output them to user-defined tables, and then implement C# or VB Software Development Kit 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 the SQL Server Management Studio queries. Crystal is the perfect tool, but if the formulas change in the future, it might be difficult to track these changes in the Designer tool. Much easier is to create SQL View or even a stored procedure where all the math is done and base the report on these SQL units. If you’re designing something complex with formulas, you might be disappointed with the results of the design getting duplicate rows and other annoying errors. SQL is better positioned to do math. The general idea is this: test your query in SQL until you are satisfied with the result, and only after opening the CR Designer tool to finalize the design and cosmetic elements such as the company logo and other graphical elements.
5. Now fields and table structures. There are two ways to discover them. First is to print it from SDK in the form of a complete and very complex diagram. And the second option is to change in the menu View system information. Then open the form with the table and field in question and hover over it. Take a look at the bottom left corner where you should be able to read the names. Give it a try for yourself and see if you got the same results. The sales order header is housed in the ORDR and the lines in the RDR1 tables, respectively.
No Comment