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