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

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.

Posted by Andy Brown on 21 June 2012

In this blog

An Expression for the Report Title
Creating a Report Variable
Referring to a Report Variable

Report Variables in SSRS

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

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:


Either right-click on the report edge ï¿½	ï¿½ or use the top menu

Next, choose to add a variable:

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

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

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:

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:

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:

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.

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.