Using LookupSet in SSRS reports to show activity parties related to an activity in Dynamics CRM

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:

 As per SSRS documentation a LookupSet may be defined as:

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.

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.

Explanation:

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.

So we use LookupSet to fetch the records from another dataset based on a matching value between the current dataset and the target dataset.

For example, if we need to show the activity parties associated with an activity then we can use the LookupSet to achieve this.

Working:

Since it is not possible to fetch the related activity parties associated with an activity from OOB approach as shown in the below figure.

So we cannot fetch the To (Recipients) and From (Sender) of an activity using Advanced Find.

Workaround:

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

Formula:

To (Recipients):

=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())

From (Sender)

=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”)))

Note:

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 (,).

Sample report:

After developing the report the report looks something like below:

Hope it helps if you are willing to show records from different datasets in a table.

Before you move to the next post, have you seen our new Click2Export Solution? A 1 click solution to export reports to Word/Excel and Pdf. Email us on crm@inogic.com for a trial or if you would like to see a live demo.