Saturday 2 February 2013

Dynamics CRM 2011 - LookupSet Function in SSRS reports

 

Syntax

LookupSet(source_expression, destination_expression, result_expression, dataset)

LookupSet is generally used 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.

The above is the description specified for use of LookupSet function. Yesterday, while creating a report I used the LookupSet function and overlooked the highlighted text. I used the LookupSet in a column expression within the tablix. The Dataset assigned to tablix was “DataSet2”.

image


In the LookupSet also I specified the same DataSet i.e. DataSet2 . While executing the query on DataSet, rows were fetched as required.


image


AS viewed above 8 distinct names could be viewed. But when running the report one row was not displayed in tablix but the distinct count i.e. 8 was correctly calculated on very first row of that column.

image

After spending few hours I found that the culprit was LookupSet function which was used in an expression of a column on same tablix . Both tablix and Lookupset function were using the same Dataset i.e. DataSet2.

image

According to the function description, the dataset that is not bound to the data region should be used whereas we were using the same dataset that was used for binding in the data region.

So, the solution is to create another dataset with fields that you want to Lookup. In the LookupSet function specify the newly created DataSet.

image

For me this fixed the weird behavior in displaying rows and the final results are displayed in below screen shot.


image

3 comments:

  1. Ankit, I am developing a fetchXML based report in CRM, and the lookupset function was used in visual studio. However, when I was trying to deploy the report in CRM, it failed because CRM 2013 does not support the function based on search. Do you know if there is any similar function I can use in CRM? Thanks.

    ReplyDelete
  2. According to my knowledge, the lookupset function works well in CRM 2013. There should not be any problems. Pleas e review the following link in which the lookupset function is used for CRM 2013.
    https://blogs.msdn.microsoft.com/crminthefield/2014/03/10/dynamics-crm-custom-fetchxml-reporting-with-multiple-datasets-using-pre-filtering/

    Can you please send me the link where you read that the CRM 2013 doesn't support lookupset function?

    Thanks,
    Ankit

    ReplyDelete
  3. My problem has been solved. After the sql server has been upgraded from 2008 to 2012, CRM custom report can support lookupset function well. Thanks, Ankit.

    ReplyDelete