I recently came across a scenario where a requirement was needed to show specific row values from the dataset of a Fetch XML Based query in the report, specifically showing the first 3 products from the dataset. In the SSRS, we have the “first, last” functions that are built-in, which allows you to obtain the details of the first or last row. However, this case requires retrieving the details of every other row except first and last.
The grouping approach, which groups the first 3 rows from the table together, will not fulfill the requirement because the table contains other information, and if we use grouping, then the same information will repeat multiple times. Other products are also used in the XML Report aside from the first 3 rows of products, so we can’t exclude them from the dataset.
Follow the steps below to meet the requirement:
Step 1: I have created a new field as “Sr.No.” on “order product.” Using the plugin, I have maintained the unique number sequence (i.e. 1, 2, 3, etc.) on “order product records.” You will not populate the unique row number at the data set level because it does not allow the use of row number, aggregate, or and other functions. But we can use it for the SQL report.
Step 2: Add the “Sr. No.” field in the report dataset. Using the lookup function, we can show specific rows values. Refer to the screenshots of the report below:
Step 3: As per the row sequence, we will map the value in the “lookup” function for each textbox:
- Display ‘Product name’ in Row1- textbox1:
Lookup(1,Fields!new_srnoValue.Value,Fields!productdescription.Value, “OpportunityProduct“)
- Display ‘Product name’ in Row2 – textbox2:
Lookup(2,Fields!new_srnoValue.Value,Fields!productdescription.Value, ,”OpportunityProduct“)
- Display ‘Product name’ in Row3 – textbox3:
Lookup(3,Fields!new_srnoValue.Value,Fields!productdescription.Value, ,”OpportunityProduct“)
I mapped the other textboxes values (quantity and price) with the “lookup” function as well.
Step 4: Run the report. You can now see the top three rows are showing in the same box:
Setup in Four Easy Steps
With this simple solution, you can easily retrieve the specific row value and populate it on your report. For more information on these types of processes, contact our experts today.
