The SSRS Var function is an aggregate function that returns the Variance of all the not-null values from the given field. This article explains how to use the SSRS Var function to calculate the Variance of all items in a particular group or complete table with an example.
The syntax of the SSRS Var function to calculate the Variance of total items in a Default scope is as shown below.
Var(Fields!SalesAmount.Value)
To Find the Variance value for each Group, Table, or DataSet replace the name with the Group, Tablix, or DataSet name.
Var(Fields!SalesAmount.Value, "Name")
Var(Fields!SalesAmount.Value, "Name", Recursive)
To demonstrate the SSRS Var function, we use the Employee table below, which has 15 records grouped by Occupation. The image shows the records in the Table report.
SSRS Var function Example
Right-click on any field of the Details row, choose Insert a Row, and select the Outside Group—Below option to add a new row. Next, Merge the first three columns and add Variance Value as the text. To understand the report, I suggest you refer to the Charts, Table, Grouping, and Format Table articles in SSRS.
There are multiple ways to write the SSRS Var function. The first approach is to click the cell and choose the YearlyIncome field. It will assign the Sum function to it because it is the default aggregate function. So, select the Text (Sum(YearlyIncome)), choose Summarize by, and click the Var function. It internally writes the below expression:
=Var(Fields!YearlyIncome.Value)
The second approach is to right-click the textbox below Sales and choose the Expression option. This opens the following expression window, which allows you to write the more complex SSRS Var function along with the scope. The code below returns the Variance of the total sales items.
=Var(Fields!Sales.Value)
Please click the preview tab for the employees’ yearly income and sales variance. If you observe the report, by default, it displays the Variance in each Occupation group because it is the default scope. For more functions >> Click Here!
SSRS Var function Scope Example
Please add a new column to the right side of the Sales and name the header as Var Sale. Next, right-click the textbox and choose the Expression.
The below expression returns the same result as shown by the Variance Value Per Group row. However, you have more control because we manually changed the Var function scope.
= Var(Fields!Sales.Value, "Occupation")
The SSRS Var function expression subtracts four times the individual sales from the Variance of total items in each group. Remember, “Occupation” is the group name. Next, we formatted the number using 0.00;-0.00;’-‘.
=Var(Fields!Sales.Value, "Occupation") - 4 * Fields!Sales.Value
If you check the report preview, Var Sale of Christy Mehta: 307.39 (Clerical Variance) – 4 * 24.99 (Sale) = 207.43.
As mentioned earlier, to find the Variance of the whole table or all the employees, you must replace the group name with the table (“Tablix1”). Let me add an extra row outside the Group and name it Variance Value Per Table. Next, add the two SSRS Var function expressions below for the Yearly Income and Sales.
=Var(Fields!YearlyIncome.Value, "Tablix1")
=Var(Fields!Sales.Value, "Tablix1")
The report below shows the Variance of total items or records in each group and the whole table. Please replace Tablix1 with the Dataset name to get the Sales Variance irrespective of the report filters.
