Crystal report is a business intelligence application used to design and generate reports from a wide range of data source. It supports connectivity to various data sources such as Microsoft excel, SQL server, Oracle and more. It includes major features such as formulas, cross-tab reports, sub-reports, and conditional formatting etc. In crystal report sub-reports are used to embed one report within another. Sub-reports can be helpful when you need to display related or supplementary information alongside the main report.
In Sage X3, we recently received a requirement from one of our client. They requested that the sales order numbers be displayed in comma-separated format within the sales invoice report. Sage X3 allows user to create single invoice against one or multiple sales order or deliveries. The client desires same to have the same sales order numbers included in the report.
To achieve the above requirement follow the below steps:
Step 1: Open your main report (Sales invoice report) in crystal report and insert one new sub-report where you want to display the comma-separated value. Refer the below screenshot for your reference.
New Stuff: Price List Import template does not update products
Step 2: Open the sub-report and create the three new formulas.
Step 3: Create formula No.01 as shown in below figure, and place it in the report header section of the sub-report. It initialize a shared string variable named “t1” with an empty string. This variable will be used to accumulate or store the values that you want to display comma separated.
Step 4: Create formula No.02 as shown in below figure, and place it in the report footer section of the sub-report. It checks if shared string variable “t1” is empty, if empty it returns an empty string. If not empty, it trims extra spaces, calculate the length, and removes the trailing comma from the accumulated string.
Step 5: Create formula No.03 as shown in below figure, and place it in the detail section of the sub-report. Replace the fields name as per your requirement.
Step 6: Place all these formulas in sub-report as shown in below figure.
Step 7: Save both the sub-report and main report then deploy them to the desired folder.
Step 8: In the below screenshot you will get desired output.
Additionally, if you wants to display two or multiple fields comma separated value, create 3 new formulas in same sub-report and follow the above mentioned steps.
In this way, we can include comma separated value for single or multiple fields using crystal report by following simple steps in crystal report as mentioned in this blog.