The SSRS Min Function is an aggregate and mathematical function that returns the Minimum value of all not-null numeric values from the given field. This article explains how to use the SSRS Min Function to find the Minimum value in a particular group or complete table with an example.
The syntax of the SSRS Min Function to find the Minimum value in a Default scope is as shown below.
Min(Fields!SalesAmount.Value)
To Find the Minimum value for each Group, Table, or Data Set, replace the name with the Group, Tablix, or Data Set name.
Min(Fields!SalesAmount.Value, "Name")
Min(Fields!SalesAmount.Value, "Name", Recursive)
To demonstrate the SSRS Min Function, we use the Employee table below, which has 15 records grouped by Occupation. The Min image below shows the records in the Table report.
SSRS Min 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 Minimum 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 Min function. The first approach is to click the cell and choose the YearlyIncome field. It will assign the default aggregate function, i.e., Sum. So, select the Text (Sum(YearlyIncome)), choose Summarize by, and click the Min function. It internally writes the below expression:
=Min(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 Min function and the scope. The code below returns the Minimum sales value.
=Min(Fields!Sales.Value)
Please click the preview tab for the Employee’s Minimum yearly income and Min Sales. If you observe the report, by default, it displays the Minimum value in each Occupation group because it is the default scope. For more functions >> Click Here!
SSRS Min Function Scope Demonstration
First, add a new column to the right side of the Sales and name the header as the Min Sale. Next, right-click the textbox and choose the Expression.
The SSRS Min function expression below returns the same result as shown by the Minimum Value row: the Min value in each group. However, you have more control because we manually changed the Min function scope.
=Min(Fields!Sales.Value, "Occupation")
The expression below subtracts the Minimum sales of each occupation group from the individual employee sales. Remember, “Occupation” is the group name.
=Fields!Sales.Value - Min(Fields!Sales.Value, "Occupation")
As mentioned earlier, to find the Minimum value 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. Next, add the two SSRS Min function expressions below for the Yearly Income and Sales.
=Min(Fields!YearlyIncome.Value, "Tablix1")
=Min(Fields!Sales.Value, "Tablix1")
Similarly, add a new column to subtract the employee sales value from the Minimum table sales value.
=Fields!Sales.Value - Min(Fields!Sales.Value, "Tablix1")
The report below shows the Minimum value of each group and the whole table. Please replace Tablix1 in SSRS Min function with the Dataset name to get the Minimum Sales value irrespective of the filters applied to the report.
If you check the report preview, Min Sale of Rob Johnson: 3399.99 (Sales) – 2234.99 (Management Minimum) = 1165. Meanwhile, the sales header column is 3399.99 (Sales) – 23.5 (Table Minimum) = 3376.49.
