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

By | September 16, 2015

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.ssrs

ssrs1

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:

ssrs4

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.

4 thoughts on “Using LookupSet in SSRS reports to show activity parties related to an activity in Dynamics CRM

  1. Pratik Telaviya

    Thank you very much..reallly help full.
    Do not copy expressions directly from here because inverted commas are special characters here and you will face error, so replace ”” with “” and then put it in your report.

  2. Archer Mehr

    Woow , Thanks for sharing this as it save me a lot of time for building a report on D365 CE

  3. Umer Javed

    Hi,
    Thanks for the post.
    I am trying above formulae on a Dynamics 365 online SSRS FetchXML report. I need to be able to show “Required” parties for an appointments however i seem to be getting error on “Fields!participationtypemaskValue.Value” when using this in the table which is being bulit from Activity dataset.
    “ParticipationtypemaskValue” is in activityparty dataset. i wonder if it is to do with the field not being recognised in Activity dataset based table?

    =IIF(IsNothing(Join(LookupSet(Fields!activityid.Value,
    Fields!activityidValue.Value,
    IIF(Fields!participationtypemaskValue.Value = 5,
    Fields!partyid.Value, “”),”ActivityParty”),”,”)),
    “”,Join(LookupSet(Fields!activityid.Value, Fields!activityidValue.Value,
    IIF(Fields!participationtypemaskValue.Value = 5, Fields!partyid.Value, “”),
    “ActivityParty”),”,”).Trim(“,”).Trim())

    1. Inogic

      Hi,
      Are you considering error for the red line under “Fields!participationtypemaskValue.Value” in expression, as shown in below screenshot? If so, it is not an error.

      Did you preview the report? Report should render if dataset name is correct.

      Make sure the dataset name in expression is correctly match with the activityParty dataset name in your report (in our blog dataset name is “dsActivityParties”).

      You can also avoid the condition for participationtypemask (RequiredAttendee) in expression, by using filter in your fetchxml for activity parties dataset.

      Sample fetchxml:

      Please feel free to reach out if this does not solve your issue.

      Hope this helps!
      Thanks

Comments are closed.