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