This SSRS article explains how to calculate the running variance and running population variance using RunningValue in a particular group or complete table with an example.
To demonstrate the SSRS Running Variance calculation, we use the Employee table below, which has 15 records grouped by Occupation. Next, we used the Var function to display the Variance of Income and Sales columns per group and table. The image below shows the records in the Table report.
The Var expressions that we used in the below report are:
Variance Per Group
=Var(Fields!YearlyIncome.Value)
=Var(Fields!Sales.Value)
Variance Per Table
=Var(Fields!YearlyIncome.Value, "Tablix1")
=Var(Fields!Sales.Value, "Tablix1")
Calculate Running Variance in SSRS
For this, let me add a new column to the right side of the Income and Sales and name the headers Var Inc and Var Sl. Next, right-click the textbox under the Var Sl and choose the Expression. To understand the report, I suggest you refer to the Var, Charts, Table, Grouping, and Format Table articles in SSRS.
It opens the following expression window to calculate the running variance. To get the same, we have to use the aggregate Var function. The SSRS RunningValue code below calculates the running variance of the employee sales for each occupation group.
=RunningValue(Fields!Sales.Value, Var, "Occupation")
Similarly, right-click the textbox under Var Inc and write the below expression to find the running variance of the YearlyIncome for each Occupation group. For more functions >> Click Here!
=RunningValue(Fields!YearlyIncome.Value, Var, "Occupation")
Please click the preview tab to see the Employee YearlyIncome and Sales running variance for each Occupation group.
Let me create two more new SSRS columns and name them as the Var FInc and Var FSl to calculate the running variance for the whole table. To do so, you must replace the group name (“Occupation”) with the table name (“Tablix1”) or DataSet name (“DSet”). First, add the below RunningValue expression for the textbox under the Var FInc header.
=RunningValue(Fields!YearlyIncome.Value, Var, "DSet")
Similarly, write the following expression for the textbox under the Var FSl header to find the running variance of the table sales.
=RunningValue(Fields!Sales.Value, Var, "DSet")
The report below calculates the yearly income and sales running variance for each occupation group and includes the complete employee table.
Calculate Running Population Variance in SSRS
To do so, create four more new columns and name them VarP Inc, VarP Sl, VarP FInc, and VarP FSl. Next, we must use the VarP aggregate function inside the RunningValue function for the Population Variance.
The expression to find the Running Population Variance of the Income and Sales for each Occupation group.
=RunningValue(Fields!YearlyIncome.Value, VarP, "Occupation")
=RunningValue(Fields!Sales.Value, VarP, "Occupation")
Calculate the running population variance of the income and sales for the whole table using the two SSRS RunningValue expressions below.
=RunningValue(Fields!YearlyIncome.Value, VarP, "DSet")
=RunningValue(Fields!Sales.Value, VarP, "DSet")
