By: Scott Murray | 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.
Next, we select the Variables window and click the Add option.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Finally, we run the report once again, and now we have a total sales value per year as demonstrated below.
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
- Check out these related resources:
About the author
Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2014-05-16
