- Dedicated trainers
- Maximum 6 per class
- Superb courseware
Phone (01457) 858877 or email
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.
- SQL Server Reporting Services Variables (this article)
- Report Variables in SSRS
- Group Variables in SSRS
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?
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:
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.
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.
- SQL Server Reporting Services Variables (this article)
- Report Variables in SSRS
- Group Variables in SSRS
Comments on this blog
This blog has 6 comments:
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.
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?
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?
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?