This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. SQL Server Reporting Services Variables Report Variables in SSRS Group Variables in SSRS (this blog)

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.

Posted by Andy Brown on 21 June 2012

In this blog

Our Example Report: Oscar Nominations by Film Director
The Need for a Group Variable
Creating a Group Variable
How our Group Variables should be Used
Creating Expressions Referring to Group Variables

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,

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.

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
SQL Server Reporting Services Variables Report Variables in SSRS Group Variables in SSRS (this blog)

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Group Variables in SSRS

Our Example Report: Oscar Nominations by Film Director

The Need for a Group Variable

Creating a Group Variable

How our Group Variables should be Used

Creating Expressions Referring to Group Variables

Head office

London

Manchester