The SSRS VarP function is an aggregate function that returns the Population Variance of all the not-null values from the given expression. This article explains how to use the SSRS VarP function to calculate the Population Variance of all items in a particular group or complete table with an example.
The syntax of the SSRS VarP function to find the Population Variance of total items in a Default scope is as shown below.
VarP(Fields!SalesAmount.Value)
To Find the Population Variance value for each Group, Table, or DataSet replace the name with Group, Tablix, or DataSet name.
VarP(Fields!SalesAmount.Value, "Name")
VarP(Fields!SalesAmount.Value, "Name", Recursive)
To demonstrate the SSRS VarP function, we use the Employee table below, which has 15 records grouped by Occupation. The image shows the records in the Table report.
SSRS VarP 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 Population Variance Per Group 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 VarP 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 VarP function. It internally writes the below expression:
=VarP(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 to write the more complex SSRS VarP function along with the scope. The code below returns the Population Variance of the total sales items.
=VarP(Fields!Sales.Value)
Please click the preview tab for the Population Variance of employees’ yearly income and Sales. If you look at the report, it displays the population variance in each occupation group by default because it is the default scope. For more functions >> Click Here!
SSRS VarP function Scope Example
Add a new column to the right side of the Sales and name the header as the VarP Sale. Next, right-click the textbox and choose the Expression.
The below expression returns the same result as shown by the Population Variance Per Group row. However, you have more control because we manually changed the VarP function scope.
= VarP(Fields!Sales.Value, "Occupation")
The SSRS VarP function expression subtracts five times the individual employee sales from the Population Variance of all items in each occupation group. Remember, “Occupation” is the group name. Next, we formatted the number using 0.00;-0.00;’-‘.
=VarP(Fields!Sales.Value, "Occupation") - 5 * Fields!Sales.Value
From the report preview, Var Sale of Christy Mehta: 230.54 (Clerical Population Variance) – 5 * 24.99 (Sale) = 105.59.
As mentioned earlier, to find the Population Variance of the table or all employees, you must replace the group with the table name (“Tablix1”). Let me add an extra row outside the Group and name it Population Variance Per Table. Next, add the two SSRS VarP function expressions below for the Yearly Income and Sales.
=VarP(Fields!YearlyIncome.Value, "Tablix1")
=VarP(Fields!Sales.Value, "Tablix1")
The report below shows the Population Variance of total items or records in each group and the whole table. Please replace Tablix1 with the Dataset name to get the Sales Population Variance irrespective of the report filters.
