Reporting Services - report and group variables
Part three of a three-part series of blogs

If you're not sure when - or whether - you should be using report or group variables in SSRS, read this blog to find out how to speed up your reports and solve other problems.

1. SQL Server Reporting Services Variables
2. Report Variables in SSRS
3. Group Variables in SSRS (this blog)

Posted by Andy Brown on 21 June 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

# Group Variables in SSRS

The main reason to use group variables in Reporting Services is to speed up processing.  This is best illustrated by an example.

Translation note: a "film" is just a movie shown in the UK!

## Our Example Report: Oscar Nominations by Film Director

Suppose that you want to create the following report (which you can download here):

For each director you can see the films they've made, together with statistics on how many Oscar nominations each film they made received.

Consider the textbox shown in pink above.  The expression for this could look as follows:

=iif(

Sum(Fields!FilmOscarNominations.Value,"DirectorName")=0,

0,

Fields!FilmOscarNominations.Value /

Sum(Fields!FilmOscarNominations.Value,"DirectorName")

That is: if the total number of Oscar nominations for this director is zero, just display zero; otherwise, divide the number of nominations for this film by the total number of nominations for the director.

## The Need for a Group Variable

The above expression would work perfectly, but would mean that the group total number of nominations was calculated twice for every film.  There must be a quicker way!

The answer is to create (for this report) two variables:

Scope Variable What it will contain
Report repTotal The sum of nominations for all films in the report
Group GroupTotal  The sum of nominations for each director

The expression for the report variable will be as follows:

You can create this variable as shown in the previous part of this blog.  This will sum the number of nominations across the entire dataset (here called dsFilms).

For how to create the group variable - read on!

## Creating a Group Variable

To create a group variable, first display the properties of the DirectorName group:

There are many ways to do this: one way is to right-click on the group in the grouping pane and choose to show its properties.

Now add a group variable as you did for the report variable earlier:

The expression is: =Sum(Fields!FilmOscarNominations.Value)

Because this is a group variable, it will automatically calculate the total of Oscar nominations for that group (so the scope doesn't need specifying further).

## How our Group Variables should be Used

Let's consider first what expressions we want to create.  The report design contains 4 numbered textboxes below:

The expressons for each of the 4 textboxes shown here are given below.

Here is what these expressions should look like:

Box What the expression should do Expression
1 If there aren't any nominations for this director, display zero; otherwise, divide this filmâ€™s nominations by the total nominations for this director and display this as a percentage. =iif(Variables!GroupTotal.Value=0,0, Fields!FilmOscarNominations.Value / Variables!GroupTotal.Value)
2 Divide this film's nominations by the total nominations for the entire report. =Fields!FilmOscarNominations.Value/ Variables!repTotal.Value
3 If there aren't any nominations for this director, display 0; otherwise, the nomination proportions must sum to 100%. =iif(Variables!GroupTotal.Value=0,0,1)
4 Divide the total nominations for this director by the total nominations for the entire report. =Variables!GroupTotal.Value/ Variables!repTotal.Value

## Creating Expressions Referring to Group Variables

Referencing group variables takes a lot of nerve (particularly in SSRS 2012, which seems to have introduced a small bug).  The first thing to note is that the Variables category won't help you:

The Variables category only lists report variables, not group ones.

Consequently, we're going to have to type the expression in ourselves.  Let's start:

When you reference the collection of Variables, you still can't see the group ones.

You can now type in the name of your group variable:

We called our group variable GroupTotal.

When you type in a . to pick up on the Value property of this variable, SSRS sometimes changes its name back to the first report variable:

How annoying is this?

This irritating "feature" seems to exist only in SQL Server Data Tools in Reporting Services 2012 (2008 R2 doesn't seem to have the bug).

The thing to do if you get the autocorrection above is to complete the expression, then go back in and edit the variable name:

 Select the wrong name ... ... and correct it.

Finally, you have a reference to the group variable (although even now SSRS shows it as an error):

Ignore the red underlining - this is a valid expression!

You can now continue to create all of the expressions shown for boxes 1-4 above, and for your efforts get a much more efficient report.