This article is the first in a 3-part of a series of Tips and Tricks for Using Crystal Reports with Sage 100, Sage 500, and Sage X3.
Problem: Whenever you use an existing Sage report and modify it to bring in data from other tables, that requires joining in the extra tables. The more joins, the slower the report performance and the more possible complications with join types (inner, outer, full).
Solution: Cheat! Many of the tables that you would typically want to join into your report come with the required underlying business objects needed to, instead, create a UDF (on the work table) pointing to the field in another table. Once in place, it is as though the field(s) you needed were in the work table all along and you lose nothing on performance. You also save on aspirin!
Here's how to do it.
Suppose your client wants the customer’s state code in the AR Trial Balance report (for sorting and sub-totaling). The customer state code is not a standard part of AR_TrialBalanceWrk. Here's how to add in the AR_Customer.State field without joining AR_Customer into the report:
Steps:
a. Custom Office, Main, User Defined Field and Table Maintenance
b. Open Accounts Receivable, AR_TrialBalanceWrk
c. Add field, name it and use the Business Object setup as shown below, click OK.
d. This takes you into setup – click “OK” (three times) to complete and update the Sage data dictionary. Close User Defined Field and Table Maintenance when done.
2. Open the AR Trial Balance Report in Crystal Designer and update the data dictionary in the report.
a. Click on Database (menu) and Verify Database.
You’re ready to go!! Use the field just as you would use any of the fields in the table list.
The follow up blog in this series Conditional Joins in Crystal Reports, describes how to join two or more tables when the data relationship is dependent upon variable values.
If you have questions or want guidance on how to be more effective with Crystal Reports in Sage ERP, click below to get in touch with a Crystal Reports expert at RKL eSolutions.