{"id":27200,"date":"2021-03-08T11:16:51","date_gmt":"2021-03-08T11:16:51","guid":{"rendered":"https:\/\/www.inogic.com\/blog\/?p=27200"},"modified":"2021-03-08T11:25:23","modified_gmt":"2021-03-08T11:25:23","slug":"ssrs-how-to-get-specific-row-values-from-dataset-of-fetch-xml-based-report-in-dynamics-365-crm","status":"publish","type":"post","link":"https:\/\/www.inogic.com\/blog\/2021\/03\/ssrs-how-to-get-specific-row-values-from-dataset-of-fetch-xml-based-report-in-dynamics-365-crm\/","title":{"rendered":"SSRS: How to get specific Row values from dataset of Fetch XML based Report in Dynamics 365 CRM"},"content":{"rendered":"

Introduction<\/h2>\n

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.<\/p>\n

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\u2019t go through the grouping approach as the table contains other information and if we use grouping then the same information will repeat multiple times.<\/p>\n

Here, the remaining products are also used in the report, so we can\u2019t exclude them from Dataset.<\/p>\n

To handle this scenario, we have gone through the below steps:<\/p>\n

Step 1: We have created a new field as \u201cSr. No.\u201d on Order Product and using a plugin we are maintaining its unique sequence (i.e. 1, 2, 3, etc.) number on \u2018order product records\u2019. Here, we can\u2019t 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.<\/p>\n

\"get<\/a><\/p>\n

Step 2: Now, we need to add the \u201cSr. No.\u201d field in the report dataset. And using the Lookup function, we can show specific rows values. Please refer below screenshots of the report:<\/p>\n

\"get<\/a><\/p>\n

Step 3: As per the row sequence, we need to map value in Look up function<\/strong> for each textbox:<\/p>\n

1- Display \u2018Product name\u2019 in Row1- textbox1:<\/strong><\/p>\n

Lookup(1,Fields!new_srnoValue.Value,Fields!productdescription.Value, “OpportunityProduct”)<\/p>\n

2- Display \u2018Product name\u2019 in Row2 \u2013 textbox2:<\/strong><\/p>\n

Lookup(2,Fields!new_srnoValue.Value,Fields!productdescription.Value, ,”OpportunityProduct”)<\/p>\n

3- Display \u2018Product name\u2019 in Row3 \u2013 textbox3:<\/strong><\/p>\n

Lookup(3,Fields!new_srnoValue.Value,Fields!productdescription.Value, ,”OpportunityProduct”)<\/p>\n

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 \u2018look up\u2019 function.
\nhttps:\/\/docs.microsoft.com\/en-us\/sql\/reporting-services\/report-design\/report-builder-functions-lookup-function?view=sql-server-ver15<\/a><\/p>\n

Step 4: Run the Report and then you can see that the top three rows are showing in the same box:<\/p>\n

\"get<\/a><\/h2>\n

Conclusion<\/p>\n

In this way, using the above steps, we can show nth row values in Report without using grouping.<\/p>\n

\"\"<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

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\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":13,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[16,19],"tags":[545,592,2198,2197],"class_list":["post-27200","post","type-post","status-publish","format-standard","hentry","category-dynamics-365","category-dynamics-crm","tag-dynamics-365-crm","tag-dynamics-crm","tag-fetch-xml-based-report","tag-get-specific-row-values"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts\/27200","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/users\/13"}],"replies":[{"embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/comments?post=27200"}],"version-history":[{"count":0,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts\/27200\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/media?parent=27200"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/categories?post=27200"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/tags?post=27200"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}