How to merge and center for certain row in SQL/SSRS report?

Question

I have a table as below:

I'd like to merge and center S/No 1.0 and 2.0 only, and display to SSRS report, the rest of the row remained unchanged, is there a way to do it?

The result will be like the 2nd image below.

Answer 1

Method 1: No real changes to dataset.

Add a parent row group with a header to your current tablix. Set the grouping to an expression such as (assuming S/No is from a field called SerialNumber

=CINT(Fields.SerialNumber.Value)

So here we just convert S/No to an integer so 1.0 and 1.1 both return 1

Now you will have a header row for each group of rows.

You can merge the cells in the header row and set the expression to

=FIRST(Fields!Description.Value)

You may have to force the order of the data in your dataset to ensure that 1.0 is always before 1.1 etc.

Method 2: Add the group names to your dataset

Note: This is written for SQL Server not MySQL but should be easy enough to translate if required

If this does not work then you could add the group headers into a new column in your dataset query, then just group on that. The dataset query would look something like this...

(I've replicated your data to show it working)

DECLARE @myTable TABLE (SerialNumber decimal (5,1), Description varchar(50), UOM varchar(50), rate decimal (10,2))
INSERT INTO @myTable VALUES
(1.0, 'Warehouse Charges', NULL, NULL),
(1.1, 'Storage in pallet', 'perpallet per month or part thereof', 15.84),
(2.0, 'Handling', NULL, NULL),
(2.1, 'Unstuffing - Palletised', 'per pallet', 5.00),
(2.2, 'De-palletised', 'per palett', 5.00)

SELECT * FROM @myTable

    SELECT 
        b.Description as GroupName, a.* 
        FROM @myTable a 
          JOIN (SELECT SerialNumber, Description FROM @myTable WHERE CAST(SerialNumber AS INT) = SerialNumber) b -- headers only
            ON CAST(a.SerialNumber AS INT) = b.SerialNumber 
        WHERE a.SerialNumber != b.SerialNumber

This produces the following output

So now you can just group on the groupname field and then merge as described in the earlier method.