562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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.
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!
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 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:
What it will contain
The sum of nominations for all films in the report
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!
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).
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:
What the expression should do
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)
Divide this film's nominations by the total nominations for the entire report.
If there aren't any nominations for this director, display 0; otherwise, the nomination proportions must sum to 100%.
Divide the total nominations for this director by the total nominations for the entire report.
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 a two-day 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.
|Parts of this blog|
25 Aytoun Street