The SSRS Last Function is an aggregate function that returns the last value from the given expression. You can also use the Last function to test an expression that returns a boolean True or False. This article explains how to use the SSRS Last Function to find the last (ending row) value or text in a particular group or complete table with an example.
The syntax of the SSRS Last Function to find the start value in a Default scope is as shown below.
Last(Fields!SalesAmount.Value)
Please use the SSRS last function syntax below to check if the last value is Tue or False. You can replace the “G” with a parameter value, Group Name, or the DataSet.
Last(Fields!MiddleName.Value = "T")
To demonstrate the SSRS Last Function, we use the Employee table below, which has 15 records grouped by Occupation. The image below shows the records in the Table report.
SSRS Last 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 the Last 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 Last function. The first approach is to click the cell and choose the YearlyIncome field, and it will assign the default aggregate function, i.e., Sum. So, select the Text (Sum(YearlyIncome)), choose Summarize by, and click the Last function. It internally writes the below expression:
=Last(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 Last function along with the scope. The code below returns the last sales value for each group.
=Last(Fields!Sales.Value)
Please click the preview tab for the Employee’s Last yearly income and Last Sales value. If you observe the report, it displays the last values in each Occupation group by default because it is the default scope.
SSRS Last Function Scope Example
For this, let me add a new column to the right side of the Sales and name the header as the Last Sale. Next, right-click the textbox and choose the Expression. For more functions >> Click Here!
The expression below returns the same result as shown by the Last Value row, that is, the Last value in each group. However, you have more control because we manually changed the Last function scope.
=Last(Fields!Sales.Value, "Occupation")
The SSRS Last function expression below subtracts the individual employee sales from the Last sale value of each occupation group. Remember, “Occupation” is the group name.
=Last(Fields!Sales.Value, "Occupation") - Fields!Sales.Value
If you check the report preview, the Last Sale of Rob Johnson is 4968.59 (Last Management Sale) – 3399.99 (Sales) = 1568.6.
As mentioned earlier, to find the Last 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 and add the two SSRS Last function expressions below for the Yearly Income and Sales.
=Last(Fields!YearlyIncome.Value, "Tablix1")
=Last(Fields!Sales.Value, "Tablix1")
Apart from the above, add a new column beside the First Name (Last Bool) to showcase the boolean result. The below expression returns True if the First Name is “Christy”; Otherwise, it returns False.
=Last(Fields!FirstName.Value = "Christy")
The SSRS Last Function report below shows the Last value of Employee Yearly Income and Sales. You might wonder about the result as it does not display the Rob Huang result. In the original dataset, the last value for the EmpID 15 is Tutorial Gateway, which has 90000 sales of 3578.27.
Please replace Tablix1 with the Dataset name to get the Last Sales irrespective of the filters applied to the report.
