{"id":1737,"date":"2015-09-16T12:44:28","date_gmt":"2015-09-16T07:14:28","guid":{"rendered":"https:\/\/www.inogic.com\/blog\/?p=1737"},"modified":"2015-09-16T12:44:28","modified_gmt":"2015-09-16T07:14:28","slug":"using-lookupset-in-ssrs-reports-to-show-activity-parties-related-to-an-activity-in-dynamics-crm","status":"publish","type":"post","link":"https:\/\/www.inogic.com\/blog\/2015\/09\/using-lookupset-in-ssrs-reports-to-show-activity-parties-related-to-an-activity-in-dynamics-crm\/","title":{"rendered":"Using LookupSet in SSRS reports to show activity parties related to an activity in Dynamics CRM"},"content":{"rendered":"

There are so many features of SSRS reports which are very less used but they can play a vital role in our report performance and productivity.<\/p>\n

One such feature of SSRS reports is LookupSet<\/strong>.<\/p>\n

SSRS Definition:<\/strong><\/p>\n

\u00a0<\/strong>As per SSRS documentation a LookupSet may be defined as:<\/p>\n

Use LookupSet to retrieve a set of values from the specified dataset for a name-value pair where there is a 1-to-many relationship.<\/p>\n

For example, for a customer identifier in a table, you can use LookupSet to retrieve all the associated phone numbers for that customer from a dataset that is not bound to the data region.<\/p>\n

Explanation:<\/strong><\/p>\n

In SSRS reports we can bind a table to a single dataset and due to that it becomes tedious to show the records from another dataset.<\/p>\n

So we use LookupSet to fetch the records from another dataset based on a matching value between the current dataset and the target dataset.<\/p>\n

For example, if we need to show the activity parties associated with an activity then we can use the LookupSet to achieve this.<\/p>\n

Working:<\/strong><\/p>\n

Since it is not possible to fetch the related activity parties associated with an activity from OOB approach as shown in the below figure.<\/p>\n

So we cannot fetch the To (Recipients) and From (Sender) of an activity using Advanced Find.\"ssrs\"<\/a><\/p>\n

\"ssrs1\"<\/a><\/strong><\/p>\n

Workaround:<\/strong><\/p>\n

In reports we can achieve this using LookupSet, to achieve this we created two dataset i.e. dsActivities<\/strong> and dsActivityParties<\/strong>. Both the datasets have the ActivityId<\/strong> field in common so we used the following formula to fetch the To (Recipients) and From (Sender) of the activities shown in the report.<\/p>\n

Formula:<\/strong><\/p>\n

To (Recipients):<\/strong><\/p>\n

=IIF(IsNothing(Join(LookupSet(Fields!activityid.Value, Fields!activityidValue.Value, IIF(Fields!participationtypemaskValue.Value = 2, Fields!partyid.Value, “”), “dsActivityParties”), “,”)),””,Join(LookupSet(Fields!activityid.Value, Fields!activityidValue.Value, IIF(Fields!participationtypemaskValue.Value = 2, Fields!partyid.Value, “”), “dsActivityParties”),”, “).Trim(“, “).Trim())<\/p>\n

From (Sender)<\/strong><\/p>\n

=IIF(IsNothing(Join(Lookupset(Fields!activityid.Value,Fields!activityidValue.Value,IIF(Fields!participationtypemaskValue.Value =1,Fields!partyid.Value,””),”dsActivityParties”),”,”)),””,Join(Lookupset(Fields!activityid.Value,Fields!activityidValue.Value,IIF(Fields!participationtypemaskValue.Value =1,Fields!partyid.Value,””),”dsActivityParties”)))<\/p>\n

Note:<\/strong><\/p>\n

Since the records returned by the LookupSet function are in the form of an Array so we use the Join function to show the record collection separated by comma (,).<\/p>\n

Sample report:<\/strong><\/p>\n

After developing the report the report looks something like below:<\/p>\n

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

Hope it helps if you are willing to show records from different datasets in a table.<\/em><\/p>\n

Before you move to the next post, have you seen our new\u00a0Click2Export<\/a>\u00a0Solution? A 1 click solution to export reports to Word\/Excel and Pdf. Email us on\u00a0crm@inogic.com<\/a>\u00a0for a trial or if you would like to see a live demo.<\/p>\n","protected":false},"excerpt":{"rendered":"

There are so many features of SSRS reports which are very less used but they can play a vital role in our report performance and productivity. One such feature of SSRS reports is LookupSet. SSRS Definition: \u00a0As per SSRS documentation a LookupSet may be defined as: Use LookupSet to retrieve a set of values from\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":[15,19,58],"tags":[592,1022,1666],"class_list":["post-1737","post","type-post","status-publish","format-standard","hentry","category-development","category-dynamics-crm","category-ssrs-reports","tag-dynamics-crm","tag-lookupset-in-ssrs-reports","tag-ssrs-reports"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts\/1737","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=1737"}],"version-history":[{"count":0,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts\/1737\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/media?parent=1737"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/categories?post=1737"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/tags?post=1737"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}