BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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:
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.
As mentioned earlier in this blog, we run classroom-based advanced SSRS course covering variables (and much more besides), as well as a three-day fast-track SSRS course. We will consider running these as online training if you have a group of people from the same company. You can see all of our SSRS courses here.