Search code examples
reporting-servicesssrs-2008-r2ssrs-2012microsoft-reporting

How to group by a column based on a value on the start of each page in SSRS


I am using VS2010 with Microsoft Report Viewer v11.0.0.0. I would like to group my output data based on my Facility value. I managed it by adding a row group and set groupby value to [Facility]. It is working fine. And I want all the facility groups in a different page. I also managed it set "page break" as between each instance of group in the properties of added row group.

My problem is that I would like to hide the Facility column and instead of it use Facility: <<*Expression>> at the top of each page. Note that Facility: <<*Expr>> is repeating at the top of each page already. However, I couldnt manage to update the expression on each page. The expression is: Fields!Facility.Value. My current output is like below:

Facility:   <<Expr>>> (repeating for each page, <<Expr>> is always X in my case! :( )
-------------Page 1 starts----------
Facility    Name      Age
    X       Jack      28
            John      25
            Jillian   18
-------------Page 1 ends & Page 2 starts-----------
    Y       Brian     19
-------------Page 2 ends & Page 3 starts-----------
    Z       Hossein   20
    Z       Brandon   30
-------------Page 3 ends----------- 

What I want is:

----------Page 1 starts--------
Facility   X
Name      Age
Jack      28
John      25
Jillian   18
----------Page 1 ends & Page 2 starts-------
Facility    Y
Name       Age
Brian      19 
----------Page 2 ends & Page 3 starts-------
Facility   Z
Name
Hossein    20
Brandon    30

Any help would be so appreciated!


Solution

  • Based on the way you typed up your current output, you need to change it like this:

    -------------Page 1 starts----------
                Facility:   <<Expr>>> 
    Facility    Name      Age
        X       Jack      28
                John      25
                Jillian   18
    -------------Page 1 ends & Page 2 starts-----------
                Facility:   <<Expr>>> 
        Y       Brian     19
    -------------Page 2 ends & Page 3 starts-----------
                Facility:   <<Expr>>> 
        Z       Hossein   20
        Z       Brandon   30
    -------------Page 3 ends----------- 
    

    And then go into the Advanced view of the groups and hide the Column Group that contains the first visible column "Facility X / Facility Y / etc".

    EDIT:

    When you have your tablix selected, you should see something like this on the left side of it:

    enter image description here

    You need to insert your Facility: <<Expr>> row just above the row with the 3 little horizontal lines in it.