Search code examples
reporting-servicesssrs-tablix

SSRS: Showing only columns associated with matrix row group


I am attempting to create a report, utilizing a matrix, that only displays columns for associated row groups.

For example, I have a piece of software that allows users of a system to periodically "take samples" which are stored in a database. This data is stored in such a way that it so grouped by person name.

The format of the report would be like this.

Person  Item    TimeStamp1 TimeStamp2 
John    Item1   Value1-1   Value1-2   
John    Item1   Value2-1   Value2-2   
John    Item1   Value3-1   Value3-2   
--PageBreak--
Person  Item    TimeStamp1 TimeStamp2 
Steve   Item1   Value1-1   Value1-2   
Steve   Item1   Value2-1   Value2-2   
Steve   Item1   Value3-1   Value3-2   

What happens is that I get the line break but the columns for Steve show up in John and John in Steve, like this.

Person  Item    TimeStamp1 TimeStamp2 SteveTime1 SteveTime2
John    Item1   Value1-1   Value1-2   
John    Item1   Value2-1   Value2-2   
John    Item1   Value3-1   Value3-2   
--PageBreak--
Person  Item    JohnTime1  JohnTime1 TimeStamp1 TimeStamp2 
Steve   Item1                        Value1-1   Value1-2   
Steve   Item1                        Value2-1   Value2-2   
Steve   Item1                        Value3-1   Value3-2   

I thought adding a page break based on the user group would work out but extra columns remain.

Is there a way for only columns to display ONLY for an associated group on a page?


Solution

  • I'm assuming that in your question you mean the first "TimeStamp1" is in fact the same as "JohnTime1", right?

    Don't think you can do this easily in a matrix, if at all. It would probably involve grouping on the ranking of timestamps, and displaying the timestamp for that rank within the row group.

    There's a simpler solution though. My suggestion would be to do the following:

    • nest the matrix inside a list
    • the list will iterate the distinct person/item combinations (or just person, depending on your needs)
    • set the PageBreak property for the matrix to Start
    • the matrix has Row Groups on person/item
    • the matrix has have Column Groups on the timestamps
    • the values (Value1-1 etc) as data