How to Concatenate Field Values Across Multiple Rows in Crystal Reports

sap-crystal-reports-software

How to Concatenate Field Values Across Multiple Rows in Crystal Reports

When working with Crystal Reports, you may encounter situations where you need to concatenate the values of a particular field across multiple rows. For instance, you might want to gather a list of customer names into a single string, separated by commas, and display it in the page header or any other section of the report.

In this tutorial, we’ll walk you through the steps to achieve this with detailed instructions, sample data, and screenshots (optional) to make the process clear.

Objective

Concatenate the values from a specific field across multiple rows and display the combined result in a designated section of the Crystal Report.

Table of Contents

  • Overview
  • Sample Data
  • Step-by-Step Implementation
  • Final Output
  • Conclusion

1. Overview

In Crystal Reports, there is no out-of-the-box function to concatenate values across rows directly. However, using shared variables and formulas, you can manually achieve this. We’ll create a series of formulas that concatenate the values as the report processes each row, and then display the final concatenated string in the report’s page header (or any other section).

2. Sample Data

Let’s assume we are working with the following dataset. Our goal is to concatenate the CustomerName values into a single string.

Row CustomerName
1 John
2 Alice
3 Bob
4 Mary
5 David
6 Lisa
7 Tom
8 Sarah
9 James
10 Karen

We want to display the following output in the Page Header:

John, Alice, Bob, Mary, David, Lisa, Tom, Sarah, James, Karen

3. Step-by-Step Implementation

Step 1: Creating a Formula to Concatenate Field Values

To accumulate the field values across rows, we need to create a formula that will concatenate each CustomerName with a comma.

  1. Open Crystal Reports and navigate to Field Explorer.
  2. Right-click Formula Fields and select New.
  3. Name your formula (e.g., ConcatStringValues).
  4. In the formula editor, write the following code:
WhilePrintingRecords;
Shared StringVar concatString;

// Concatenate the field with a comma separator
If concatString = "" Then
    concatString := {Table.CustomerName}
Else
    concatString := concatString + ", " + {Table.CustomerName};

Replace {Table.CustomerName} with the field from your actual data source.

  1. Click Save and Close.

Step 2: Resetting the Variable

You need to reset the shared variable at the start of the report (or wherever appropriate). Otherwise, the string would carry over to subsequent pages, leading to unintended results.

  1. Again, right-click Formula Fields and select New.
  2. Name this formula ResetConcatString.
  3. In the formula editor, write the following:
WhilePrintingRecords;
Shared StringVar concatString := "";
  1. Place this formula in the Report Header section to ensure that the string is cleared at the beginning of the report.

Step 3: Displaying the Concatenated String

Now that we’ve created a formula to build the concatenated string, let’s display it in the Page Header.

  1. Create another formula field (name it DisplayConcatString).
  2. In the formula editor, write the following code:
WhilePrintingRecords;
Shared StringVar concatString;
concatString;
  1. Drag this formula into the Page Header (or another section where you want the final result to appear).

Step 4: Suppressing Unnecessary Output

Since we are only interested in displaying the concatenated result in the page header, we can suppress the ConcatStringValues formula in the Details section.

  1. Right-click the ConcatStringValues formula field in the Details section.
  2. Select Format Field.
  3. In the dialog box, check the Suppress option so the intermediate values aren’t shown for each row.

4. Final Output

When you run the report, after processing all rows, the concatenated string will appear in the Page Header (or the section where you placed the DisplayConcatString formula).

Sample Output

In the Page Header:

John, Alice, Bob, Mary, David, Lisa, Tom, Sarah, James, Karen

The individual row values in the Details section will be hidden due to the suppression.

5. Conclusion

By using shared variables in Crystal Reports, you can easily concatenate field values across multiple rows and display the result in a specific section of the report. This method is especially useful when creating reports that need to summarize or display cumulative data like a list of customer names, product names, or other similar information.

The steps outlined in this guide can be adapted to work with other types of fields and sections in your report, offering flexibility in how data is presented. Experiment with different use cases, and you’ll find many practical applications for this technique!

Additional Notes:

  • You can apply similar logic for other operations (e.g., concatenating numeric values, adding new delimiters, etc.).
  • Make sure that the field you are concatenating doesn’t contain null values, or handle them appropriately in the formula (If IsNull({Table.Field1})).
Tags: , , , , , , , , , , , , , ,