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