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

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.

Report Variables in SSRS

You can download the example report used for this page, which looks like this:

Report with header

What follows after the title doesn't matter, but we want to ensure that the title gives the same date and time on every single page.

 

An Expression for the Report Title

To get a report title as above, you could use the following expression:

="Report printed at " & format(now,"HH:mm:ss")

However, this would give a different title on each page, because (as previously stated) SSRS recalculates all of the textbox expressions on a page whenever you view it.  To get round this we need to find some way to freeze the title.

Creating a Report Variable

To get round the problem above, we'll create a report variable to hold the title.  Since this will be calculated once and once only (when the report first loads), we'll get the same time on every page.  First go to the report's properties:

Short-cut report properties menu Top report properties menu
Either right-click on the report edge … … or use the top menu

Next, choose to add a variable:

Report variables tab

Click on the Variables category, then choose to add one.

 

Give your new variable a name, then click to create an expression for it:

Naming a variable

Here we've called the variable repTitle.

 

It's a good idea to prefix all report variable names with rep, so as not to confuse them with group variables (all variable names must be unique).

Finally, type in an expression in the usual way:

Report title expression

Create the expression you want in the usual SSRS manner.

Referring to a Report Variable

Once you've created a report variable, you can include its value in any expression.  For our example, first choose to create an expression for a textbox in the report header:

Creating report title expression

Right-click on the textbox and choose to create an expression.

 

To put in a reference to a report variable, just choose the Variables category and double-click on (here) the repTitle variable:

Choosing variable to refer to

Double-click on the repTitle variable to create the expression shown here.

When you preview this report, SSRS will calculate the report title variable repTitle once only, and display this on the top of every page.

This blog has 0 threads Add post