So you’ve noticed SQL Server Reporting Services has variables now? Besides thinking that’s pretty cool, my first reaction was to question about when to use a variable instead of an expression – and what about custom code? This blog entry attempts to clarify how they all fit together. This discussion applies to SQL Server 2008 and above.
The first thing to know is that SSRS variables really are just expressions. There’s no syntactical differences between variables and expressions. The big difference is in the scope, when they’re evaluated, and if caching is involved.
- Expression: scope is one textbox
- Group Variable: scope is the group (as well as other child groups)
- Report Variable: scope is the report (i.e., this is considered a global variable)
- Expression: per page (on demand report processing)
- Group Variable: once per unique group
- Report Variable: once per report
Robert Bruckner’s blog entry on this topic explains that, due to the new on demand report processing engine in 2008, an expression for an individual textbox is evaluated when the page is rendered by the user. You can see the difference if you test a Now() function. In the following example, the tablix was set to have a page break before it to purposely be able to delay rendering of page 2. In this situation, the Now() function within a report variable is only executed once (which makes it act like the Built-In field called ExecutionTime).
You can already guess that there are some calculations that need to be global throughout the entire report, rather than evaluated on a page-by-page basis. Besides consistency, there’s performance to think about which leads us to…
Caching of Results
- Expression: No caching
- Group Variable: Yes
- Report Variable: Yes
This of course presumes we are not running the report from a snapshot. With caching, if we have an expensive, time consuming calculation to perform, we can take advantage of it being processed once and being reused throughout the entire report (or group).
- Expression: Least reusable – applicable to one textbox
- Group Variable: Applicable to one group (and its child groups)
- Report Variable: Most reusable – applicable throughout entire report
Reducing redundant code is always a good thing!
Expressions and variables both allow for simple expressions (i.e., referencing a single dataset field, parameter, or built-in field), as well as complex expressions (i.e., referencing multiple dataset fields, using operators, constants, report functions, and so on). They both also allow for the utilization of code – both embedded custom code functions as well as functions from a custom assembly.
Where to Set Up
- Expression: As you’d expect, this is set within the Textbox Properties.
- Group Variable: Set within the Group Properties. Note the expression is the exact same as above – no syntactical differences. A good name is important since it’ll be referenced when you use the variable. Since names for Report Variables and Group Variables can’t be the same, that’s why I used the Grp prefix in its name.
- Report Variable: Set within Report Properties. A good name is important since it’ll be referenced when you use the variable.
Using the Variable
After it’s set up, you reference the Variables collection within a Textbox Expression to call it. Within the Variables collection, “Value” is the only property choice available.
To conclude, the above factors (scope, when evaluated, caching, and reusability) all may help decide whether to use a variable. Either an expression or a variable may be used when custom code is involved.