Introduction
In this blog, we have explained how we can get any specific row value in the report, as we know SSRS have inbuilt functions like First, Last. Using these functions, we can get first row details or last row details but what if we need second, third, fourth… rows value.
Recently, we had a business requirement to show specific row value in the report. Like the need to show the first 3 Order Products from the dataset. Here, I can’t go through the grouping approach as the table contains other information and if we use grouping then the same information will repeat multiple times.
Here, the remaining products are also used in the report, so we can’t exclude them from Dataset.
To handle this scenario, we have gone through the below steps:
Step 1: We have created a new field as “Sr. No.” on Order Product and using a plugin we are maintaining its unique sequence (i.e. 1, 2, 3, etc.) number on ‘order product records’. Here, we can’t populate these unique row numbers at the data set level, because it does not allow to use Row Number(), Aggregate or and other functions. Yes, for SQL-based reports we can handle this sequencing at the reported side. But our report is designed based on the Fetch XML.
Step 2: Now, we need to add the “Sr. No.” field in the report dataset. And using the Lookup function, we can show specific rows values. Please refer below screenshots of the report:
Step 3: As per the row sequence, we need to map value in Look up function for each textbox:
1- Display ‘Product name’ in Row1- textbox1:
Lookup(1,Fields!new_srnoValue.Value,Fields!productdescription.Value, “OpportunityProduct”)
2- Display ‘Product name’ in Row2 – textbox2:
Lookup(2,Fields!new_srnoValue.Value,Fields!productdescription.Value, ,”OpportunityProduct”)
3- Display ‘Product name’ in Row3 – textbox3:
Lookup(3,Fields!new_srnoValue.Value,Fields!productdescription.Value, ,”OpportunityProduct”)
Using the same way, we have mapped Quantity and Price textboxes values. On the link given below, you can read more on how to use the ‘look up’ function.
https://docs.microsoft.com/en-us/sql/reporting-services/report-design/report-builder-functions-lookup-function?view=sql-server-ver15
Step 4: Run the Report and then you can see that the top three rows are showing in the same box:
Conclusion
In this way, using the above steps, we can show nth row values in Report without using grouping.
