The SSRS Max Function is an aggregate and mathematical function that returns the maximum value of all numeric not-null values from the given field. This article explains how to use the SSRS Max Function to find the maximum value in a particular group or complete table with an example.
The syntax of the SSRS Max Function to find the maximum value in a Default scope is as shown below.
Max(Fields!SalesAmount.Value)
To Find the Maximum value for each Group, Table, or Data Set, replace the name with the Group, Tablix, or Data Set name.
Max(Fields!SalesAmount.Value, "Name")
Max(Fields!SalesAmount.Value, "Name", Recursive)
To demonstrate the SSRS Max Function, we use the Employee table below, which has 15 records grouped by Occupation. The image shows the records in the Table report.
SSRS Max 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 Maximum 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 Max 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 Max function. It internally writes the below expression:
=Max(Fields!YearlyIncome.Value)
The second approach is to right-click the textbox and choose the Expression option.
It opens the following expression window, where we write the more complex SSRS Max function along with the scope. The code below returns the Maximum sales value.
=Max(Fields!Sales.Value)
Please click the preview tab for the Employee’s Maximum yearly income and max Sales. If you observe the report, by default, it displays the maximum value in each Occupation group because it is the default scope. For more functions >> Click Here!
SSRS Max Function Scope Demonstration
Let me add a new column to Sales’s right side, named the header as the Max Sale. Next, right-click the textbox and choose Expression.
The SSRS Max function expression below returns the same result as shown by the Maximum Value row: the max value in each group. However, you have more control because we manually changed the Max function scope.
=Max(Fields!Sales.Value, "Occupation")
The expression below subtracts the individual employee sales from the maximum sales of each occupation group. Remember, “Occupation” is the group name.
=Max(Fields!Sales.Value, "Occupation") - Fields!Sales.Value
As mentioned earlier, to find the maximum value of the whole table or all the employees, you must replace the group name (“Occupation”) with the table name (“Tablix1”). For the Yearly Income and Sales, let me add an extra row outside the Group and the two SSRS Max function expressions below.
=Max(Fields!YearlyIncome.Value,"Tablix1")
=Max(Fields!Sales.Value,"Tablix1")
Similarly, add a new column to subtract the employee sales value from the maximum table sales value.
=Max(Fields!Sales.Value, "Tablix1") - Fields!Sales.Value
The report below shows the Maximum value of each group and the whole table. Please replace Tablix1 with the Dataset name to get the Maximum Sales value irrespective of the filters applied to the report.
If you check the report preview, Christy Mehta’s max sale is 59.553 (Clerical Maximum) – 24.99 (Sales) = 34.54. The empty header column is 4968.559 (Table Maximum) – 24.99 (Sales) = 4943.6.
