column grouping in ssrs

 .

Step 3: Verify the Dataset

Ensure that your dataset (YourDatasetName) contains the required fields (BILLNO and AMT). If the dataset is grouped or filtered, make sure the grouping does not exclude any rows.

Step 4: Preview the Report

Run the report, and the concatenated result will appear in the textbox. For example:

123: 1000.00    1223: 2000.00

To display the data in the desired format (BILLNO: AMT concatenated into a single row), you can use SSRS expressions to concatenate the values and display them in a single cell. 

Step 1: Understand the Desired Output

You want to display the BILLNO and AMT fields concatenated into a single string, separated by spaces, like this:

123: 1000.00    1223: 2000.00

This requires concatenating all rows of the dataset into a single string.

Step 2: Use SSRS Expressions for Concatenation

SSRS does not natively support concatenating multiple rows into a single cell using grouping or tablix controls. However, you can achieve this using a custom expression with the Join function.

Here’s how:

1. Add a Textbox to Your Report:

   • Add a textbox to your report where you want the concatenated result to appear.

2. Use the Join Function:

   • Use the following expression in the textbox to concatenate the BILLNO and AMT fields for all rows in the dataset:

=Join(
    LookupSet(1, 1, Fields!BILLNO.Value & ": " & Format(Fields!AMT.Value, "N2"), "YourDatasetName"),
    "    "
)

Explanation:

• LookupSet: This function retrieves all values from the dataset that match a condition. In this case, we're retrieving all BILLNO and AMT values.
• Fields!BILLNO.Value & ": " & Format(Fields!AMT.Value, "N2"): Concatenates the BILLNO and AMT fields, formatting the AMT value as a number with two decimal places.
• "YourDatasetName": Replace this with the name of your dataset.
• " ": The delimiter used to separate concatenated values (in this case, four spaces).

You’ll need to dynamically pivot rows into columns, which SSRS does not support natively in a tabular report unless you pre-shape the data using SQL or custom code.

If the number of BILLNO values is small and known in advance, you can pivot the data in SQL:
  

   SELECT 
       MAX(CASE WHEN BILLNO = 123 THEN CAST(BILLNO AS VARCHAR) + ': ' + CAST(AMT AS VARCHAR) END) AS Bill1,
       MAX(CASE WHEN BILLNO = 1223 THEN CAST(BILLNO AS VARCHAR) + ': ' + CAST(AMT AS VARCHAR) END) AS Bill2
   FROM your_table

   Then use the result in a Table or Matrix in SSRS.

Or, if you don’t know how many BILLNO there are, but you're okay showing them all in one row or column, use SQL aggregation to create a single text field:

SQL Server 2017+ (STRING_AGG):

SELECT STRING_AGG(CAST(BILLNO AS VARCHAR) + ': ' + CAST(AMT AS VARCHAR), CHAR(9)) AS BillSummary
FROM your_table;

Older SQL Server versions:

DECLARE @Result NVARCHAR(MAX) = ''

SELECT @Result = @Result + CAST(BILLNO AS VARCHAR) + ': ' + CAST(AMT AS VARCHAR) + CHAR(9)
FROM your_table

SELECT @Result AS BillSummary

Then display BillSummary in a Textbox in SSRS.