Reporting Services - report and group variables
Part one 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 (this blog)
  2. Report Variables in SSRS
  3. Group Variables in SSRS

Posted by Andy Brown on 21 June 2012 | 7 comments

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.

SQL Server Reporting Services Variables

Have you ever wondered why SSRS (as I'll call SQL Server Reporting Services from now on) includes a tab for Variables?

Variables tab

You can create variables either for a report or for a group.

 

Me too!  So I thought I'd do an in-depth blog on how - and why - you would use variables in Reporting Services (and in the process unearth one of the odder bugs).

Scope of Variables

I thought I'd start with an explanation of what variables are, which is simply expressions with wider scope than would be the case for a textbox. 

To prove this, I've created an example report, which you can download:

Different ticks for report

The ticks are different at report, group and detail level.

The expression to display the ticks is as follows:

=mid(now.Ticks.ToString,9,6)

What this does is take the current state of a computer's clock cycle, and extract some digits from it to give an impression of how much time has elapsed.  If you look at the figures, you'll see the following:

Scope of expression Value of ticks Calculated
Report (appears in report title) 221,463 When the report first loads
Group (appears in group footer) 221,744 for visible groups When each group first loads
Detail (6 films are visible) 221,775 and 221, 791 When a page loads

Versions of Reporting Services from 2008 onwards calculate textbox expressions afresh for every page as it loads, but variables are evaluated once only for the report (and cached in memory).  This means that group and report variables can save you a lot of processing time.

Now that we know how variables work, let's look at how you might use them, using one example each for report and group variables. 

This blog has 7 comments

Comment added on 24 June 2012 at 06:42 GMT
Buenas noches, espero me ayuden a resolver un inconveniente que tengo en un reporte, resulta que del procedimiento traigo un campo de fecha que puede esta llenar o no por que es una fecha de egreso, y cuando genero el rpt sale #error... Mi pregunta es como puedo validar si la fecha esta NULL que no muestre nada?  gracias por su colaboraciĆ³n.
Reply from Andy Brown
In English please!
Comment added on 06 September 2012 at 16:40 GMT
Hey guys, I get a 404 when I try to download the sample report! Article's great, but it'd be easier to get with the sample.  Thanks!
Reply from Andy Brown

Thanks for letting me know.  I've now fixed this (all links point to ZIP files, which work OK).  Please let me know of any other broken links you find.

Comment added on 09 October 2012 at 11:19 GMT
Thanks for this Andy.  I'm struggling with a problem that feels like it should be solved (I think) by variables.  I'll try to express the problem in terms of the example you've used here.
 
If we had information held per director - say their annual salary - and I want to show the value of this for each director and for the whole report (i.e. the total earnings of the directors listed).  The data rows would hold a repeating value of Salary for each film by a given director.
 
We can show salary per director by simply including "MAX(Salary)" or similar (e.g. "FIRST()"), since the salary value will be the same for each detail (film) row for that director.
 
Alternatively we could create a group variable (say "DirectorSalary") to equal Salary and use that.
However, if I try then to show the overall report total, I run into problems.
 
If I use SUM(Salary) I get a value multiplied by the number of films since the salary value appears in each row (i.e. my total is 8 x Kurosawa's + 1 x Proyas' + 2 x Cuaron's salaries rather than simply Kurosawa's + Proyas' + Cuaron's ).
 
If I try to use SUM(Variables!DirectorSalary.Value) I get an error, because we can't use variables in aggregate expressions.
 
It feels like I'm missing something very obvious, but I'm struggling to see it.  
 
Can you help please?
Reply from Andy Brown

Why can you not just create a report grouping films (to continue with your kind attempt to put things in terms I understand) by director?  There would then be no need for variables (which I suspect you don't need).  You could just include the salary in the director group header or footer, and the SUM(Fields!DirectorSalary.Value) in the overall report header/footer.  Or am I missing something obvious?

Comment added on 10 October 2012 at 10:01 GMT
Yes, I was missing the obvious really.
 
I also found that whilst it wouldn't let me do a SUM(MAX(Salary, "Director")) it does allow SUM(FIRST(Salary, "Director")), which does what I wanted - the total of each group's repeated value.
 
Sorry that this isn't at all related to variables, but maybe it will help someone somewhere.  Thanks for your patience and attention... and the helpful articles.
Comment added on 10 February 2013 at 02:49 GMT
Hi:

I'm trying to do suscriptions for a report.
I configured the e-mail  (I don't have a local server, the server is hosted in Godaddy ) using the smtp parameters of the email account and I think the parameters are ok.
however,  How  to configure the password for the email account?  I'm obtaining a message from Reporting Services sending the email for the suscription (Error sending email : the server denied the sender address.  error 530 authentication required )
 I reviewed at this web page " http://technet.microsoft.com/es-es/library/ms157273.aspx "  the configuration for RSreportserver.config file, in the mail server configuration 's section and I don't see a key for asing the password.
Could someone help me?

I'll apreciate your help.

Best regards
Reply from Andy Brown

As regular readers of Wise Owl blogs will know, we claim to be gurus on every aspect of the software in which we train - apart from things to do with security!  Can any other readers help?

Comment added on 11 February 2013 at 17:16 GMT
Andy, Great post. Here's my question, though, what is the advantage to using report variables rather than simply using a manually created parameter? When I first saw the variables, I thought, wonderful, I can create a variable on the main page (initial .rdl) and then on every one of the related report pages (meaning individual .rdl files in the same solution) I can call that variable. Hurray!

Not so much, sadly. I already use parameters in much the same way variables seem to be used so I don't see the point, and I'm sure I'm missing something. I establish a hidden parameter set to some expression (example, calculate some overall company metric at run-time, set it up in a parameter) and then just pass the parameter through the actions when the user navigates.

Thanks.
Reply from Andy Brown

A fair question.  You are correct that variables are just expressions contained within a single report.   Right now I can't think of any difference: if you set a hidden parameter and give it a default expression, I can't see any difference between this and a variable doing the same job.  Nor can I find anyone else who's commented on this.

So maybe it's me missing the point?

Comment added on 04 April 2014 at 18:11 GMT
Parameters add to the processing time. Variables don't add that much time as minimalistic information is stored for them. Also not to forget, parameters (hidden or not) are meant to be used with datasets. Variables are provided as a means to do the rough work for us. Don't see much difference other than that.

A full-blown discussion forum is being built for this site, which will allow you once more to add comments and discussion threads.