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

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

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.

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.

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.

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