Search code examples
reporting-servicesssrs-2008ssrs-2012ssrs-2008-r2reportingservices-2005

Microsoft SSRS Reporting Services Matrix Report Indentation

I have a Matrix Report built in SSRS, I have it grouped on the ID for the rows and grouped on the FirstName & LastName for the columns to create the needed report, however the only thing that I need to get to work is the to indent the second and third rows to start at the first column instead of after the last column of the first row as showing, how can I get that to work please?

Thanks in advance!

SSRS Matrix Report

enter image description here

Solution

  • Your problem is that you have created a column group based on FirstName + LastName. This will create a new column for each instance of this (so 1 column for each record as they are all unique).

    What you need to do is assign a column number for each name with each ID.

    Here I have reproduced your sample data and then assigned a value to ColN for each unique name within each ID.

    DECLARE @t TABLE (ID INT, FirstName varchar(20), LastName varchar(20))

INSERT INTO @t VALUES 
(25, 'Abby', 'Mathews'),
(25, 'Jennifer', 'Edwards'),
(26, 'Peter', 'Williams'),
(27, 'Johns', 'Jacobs'),
(27, 'Mark', 'Scott')

SELECT 
    ID, FirstName, LastName
    , ColN = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY FirstName, LastName)
 FROM @t

    This gives us the following output.

    enter image description here

    You can now use ColN in you column group instead of the expression you now have.

    This will give you the desired output.