SQL Server Reporting Services Report and Group Variable References

By:   |   Updated: 2014-05-16   |   Comments (11)   |   Related: > Reporting Services Formatting

Problem

How can report group variables be referenced in SQL Server Reporting Services (SSRS) and in what way are they used?  Are there any limitations with Report and Group variables in SSRS?  Can you show me an example of using these SSRS variables?  When would you use SQL Server Reporting Services Report vs. Group variables?  Check out this tip to learn more.

Solution 

Continuing my series of tips which center on creating custom code for SSRS reports, this third tip covers the implementation of group and report variables. In the first tip, SSRS Embedding DotNet Code, I covered how to add code within a single report. In the second tip, I covered building a Custom Assembly, a DLL, which can be referenced by a group of reports. In this third tip, we will explore how to define a variable, at the group or report level, which can be referenced within expressions that are placed on a report. 

SQL Server Reporting Services Report Variables

Report variables can be helpful when making complex calculations and are evaluated, often just once, at run time. Often, a variable is implemented when a value or calculation is used many times, but we want use a consistent value for each calculation. The calculations in the variable are made just once and then applied throughout the report, as they are used during that report session. Of course as with any item, a few restrictions do exist in the use of variables. First, the variable must begin with a letter and cannot contain any spaces; furthermore, the name used is case sensitive. Additionally, report variables are by default read only; however, this behavior can be changed to read-write. Thinking through the different scenarios, you can easily see that setting the value once could be helpful on a long running report where maybe the exact time the report is run (and used in a calculation) is very important. For a variable that is defined as read-write, the exact time when a variable calculation is evaluated is not defined, so care must be taken in utilizing the write driven variables. 

Group Variables in SQL Server Reporting Services

Group variables differ from report variables in that their scope remains only within the context of the group in which it is defined. Additionally, the variable is available for reference within any child of the group in which it is defined. A group variable can often be used in situations where you need to adjust a variable value per each group. Again, similarly to report variables, the variable name must begin with a letter, cannot contain spaces, and the name is case sensitive.  Likewise, a group variable is handy when you want to make just one call to get a set of values, such as currency or tax rates, which change per different product categories or countries respectively. Group variables cannot be read-write.

Using Report Variables for SSRS

To display data for our reports, we will again use AdventureWorks 2012 SSAS database; the database is available on Codeplex. Once you download and install the SQL Server database, we will subsequently use the SSRS 2012 sample reports. These reports are a good basis to demonstrate the use of both report and group variables. After downloading the SSRS sample reports, go ahead and open up the AdventureWorks Sample Reports 2012 solution. We will use the Company Sales.rdl for our examples; this report is fairly straightforward, so it will be easy to see these variables in action. In order to create a report variable, we first need to go to Report Properties from the menu bar, as shown below. 

Report Properties

 Next, we select the Variables window and click the Add option.

Report Properties Variables

Once you click Add, you will need to specify a variable name and select or deselect Read-Only. Finally we can define the value for the variable. 

Define Variables

As shown below, clicking on the fX button allows us to define the actual value. In this example, we are using the Now() function, which returns the current date and time. Finally, clicking the OK button twice will create the variable, TimeNOW, which allows us to reference it in an expression.

Report Variable Definition

Illustrated next, we add two columns to the current tablix. In the first text box we will use the variable expression where as in the last column we will use the NOW() function directly.

tablix

The syntax to reference a variable is: "=Variables!VariableName.Value". For our example, the expression is "Variables!TimeNOW.Value" as illustrated next. Notice in the below screen print how IntelliSense is invoked as soon as you type "Variables".  The second screen print shows the final expression reference to the newly created variable. This expression is placed in the second text box on the tablix within the Company Sales report.

variable expression

Completed Expression

Notice how the exact same time is displayed on the report for each of the values in the Variable Use column. To the contrary, the Direct Expression column shows variations in the time. You can see if consistency is important (which it is especially when evaluating time items like seconds and minutes), use of a variable can maintain a consistent time across the span of report processing.

Variable results

We could easily define some embedded code, as shown below, and use it as part of the variable definition. Again, such a setup provides a layer of abstraction for defining specific calculations or for use in other assignments such as formatting.

'*************************************************************************
' Set Background color based on input value and threshold
'*************************************************************************
Function BGColor(ByVal InputValue AS double, _
ByVal ThresholdValue As double) As String

If InputValue > ThresholdValue Then
Return "Yellow"
Else
Return Nothing
End If

End Function

'*************************************************************************
' Handle divide by zero without an error
' Author: Scott Murray
'*************************************************************************
Public Function CalcDivision(ByVal Numerator As Object, _
ByVal Denominator As object, ByVal DivideByZeroDefault As Object) As Object
If Denominator <> 0 Then
Return Numerator/Denominator
Else
Return DivideByZeroDefault
End If
End Function

We could define a variable using the BGColor function after embedding the above code in the report properties code section. So in the below example, we use the BGColor function from the embedded code to check the Sales values. You need to note though, that this code evaluates the sales value in totality. Thus the code actually evaluates total sales values when the report starts to run and it is compared against a threshold value of 100,000,000,000. Both the sales and threshold values are passed to the function, once, at the beginning of the report run. A second variable is also created which is just the total sales value, as denoted in the second screen print; thus we can see the actual value being passed to the function. Also notice in the second variable we have to reference the "Sales" dataset, so the variable knows what dataset to use to get the Sales field. 

BGColor

Total Sales Variable

Now we can use both these variables on the report.  First, we change the BackGroundColor property to use the ColorCode variable as illustrated in the first screen print below. Then, as shown in the second illustration, we add a new column for the "Total" Sales value and set it to the TotalSales variable.

Background Expression

Total Expression

Remember, both variables will evaluate the total sales for the entire dataset at run time, and the reported value will not be specific to the row or column scope.  Thus, the background color and resulting "total" sales value, as displayed below, reflect the variable value which were processed just once.  Since 110,373,889.31 is less than 100,000,000,000, a green background is displayed. Although it may not seem overly helpful in these simple examples, using just the variable instead of the actual code expression in each of the fields allow for a layer of abstraction that may help in very complex report expressions.

BG Color and Total Report

Using Group Variables in SQL Server Reporting Services

Group Variables work in a similar fashion to report variables; however they are aggregated or evaluated within the scope context of the group. To continue with our report example, we will add a group variable to the OrderYear column group. To get the variable definition screen, click on the down arrow to the right of the column group name, and then select Group Properties.

Group Properties

Click on the Variables option and then select the Add button. Next, we define a second sales total variable, although this one will be within the scope of the OrderYear column group. Thus, the variable will evaluate the total sales for each year; remember it will only make this calculation once per group or year in our example.

Year Group Variable

Then, we add this variable to a new cell and column on the tablix as illustrated next. We now have a Group Total Sales and a Total Sales column. Of course we could use these for some sort of ratio calculation if needed.

Group Variable Expression

Finally, we run the report once again, and now we have a total sales value per year as demonstrated below. 

Group Variable Result

Although we will not demonstrate it here, you could certainly use references to embedded code or external assemblies within a group variable.

Conclusion

Report and group variables within SSRS report provide report designers with another tool to reuse code and ease the design process when dealing with complex calculations. Report variables can be used throughout a report whereas a group variable only is allowed within the scope of the group where it is implemented. A group variable is defined in the group properties window; similarly, report variables are defined in the report properties window. Like other objects within a SSRS report, a variable name must begin with a letter and cannot contain spaces, references to the variable are case sensitive, and report variables can be read-write or read-only (default). Finally, a variable can interact with SSRS embedded code or external code references.

Next Steps 


Last Updated: 2014-05-16


get scripts

next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Thursday, August 25, 2016 - 7:46:38 PM - Clint Back To Top

 I have added a group variable exactly as shown here and in other blogs but it never shows up in the expression dialog. I downloaded an example from another blog and viewing that report does indeed show the group variables in the expression dialog box. They appear to be defined exactly the same way. Any ideas?

 


Thursday, August 18, 2016 - 9:56:52 AM - Scott Murray Back To Top

 Darrell,

That does seem odd... it should be available.  Could you check the case is correct?

 


Wednesday, August 17, 2016 - 8:04:59 PM - Darrell Parker Back To Top

I created a group variable and when I try to refrence it using Variables!GroupVariable it doesn't show up in the list (I am using SSRS/BI in VS 2013). Specifically when I preview the report it tells me that I can't access a Report variable, which is not what I used in my text box. Weird stuff maynard! 

 


Wednesday, May 11, 2016 - 7:34:08 AM - Scott Murray Back To Top

Rafal.  I have not seen that behaviour.  My variables change with each report run.


Wednesday, May 11, 2016 - 7:17:30 AM - Rafal Back To Top

 Are there any limitations regarding using report variables and Lookup function? I have SSAS data source where I read some values and I was using Report variables to store them as those values are valid throughout whole report. Everything works first time, second time you run report and variables with lookups are empty. When I alter report and save it, values show up - again one time only. 

 I have posted question on SO: http://stackoverflow.com/questions/37158843/


Monday, August 24, 2015 - 7:28:50 PM - Alan Back To Top

Megan, I have encountered something similar the first time I implemented a complex dynamic group formatting system for a report.  I was using VB objects in the report custom code to handle formatting, and it was working great, but I noticed that when navigating to the second page, the objects would be recreated (uninitialized).  Similar to what you've reported, this only affected one report out of dozens I've used this technique with, and it simply ceased to be a problem after a subsequent update (simply reorganizing) on the report where it first was discovered.

Now, this is somewhat different in that it was my custom objects which got lost, rather than the value from a report object (which is actually quite a bit more concerning), but I thought I'd mention it since they may be related.


Thursday, July 30, 2015 - 11:50:04 AM - scott Back To Top

Roman,

You need to use a regular variable to complete what you are trying to do, if I understand correctly.


Thursday, July 30, 2015 - 10:23:15 AM - Roman Drexler Back To Top

Thank you for this article, it was very helpful already. I have a question about group variables though:

Is there any way to have a variable that gets calculated not only once per row, if it is a row group variable or once per column, if it is a column group variable, but instead one, that gets calculated for each row X column? Or do I just have to make an even longer and more redundant expression in the desired detail field than I already have?


Tuesday, August 26, 2014 - 3:13:33 PM - Megan Brooks Back To Top

I will post a follow-up if I come across anything new. I have not changed any of the other RDLs to not use a group variable and have not seen the problem with any of them, and I see nothing unusual at all about the one that does have the problem.

These reports won't be going into production on RS 2014 at least until SP1 comes out (if not even further out), and I will be watching to see if anything changes with a CU or the SP. I am on CU2 right now, because something or other (I forget what, now) flat out failed using RTM (I couldn't even deploy all the RDLs) and needed an immediate hotfix.


Tuesday, August 26, 2014 - 12:09:09 PM - scott Back To Top

Megan,

I have only worked with 2014 in pass, so that is good to know.  Thank you for sharing; I wonder what is happening.


Tuesday, August 26, 2014 - 10:52:24 AM - Megan Brooks Back To Top

I had been using a group variable defined within a tablix that represents the current page, so that the variable value updates once per page. The value was calculated by calling a method in a custom assembly, passing parameters that defined which page was being rendered. I ran into a weird problem, however, with just one RDL file so far (out of 60+), where in some cases the values passed as parameters to the custom assembly would arrive as null/zero instead of the their actual values as seen in the RDL.

It all worked fine using RS 2008, but this problem appeared when I began testing with RS 2014. The only solution I have found (and I have spent hours trying different things) is to manage the data a different way that doesn't involve using group-level variables.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools