SSRS MultiLookup error - SQL Server 2008 R2

Question

I have a report that contains 2 DataSets.

DataSet1 looks like this:

SRId SRName RelatedItemIdList
1        A            1,2,3
2        B            1
3        C            NULL

DataSet2 looks like this:

RelatedItemId  ItemName
1                      Item1
2                      Item2
3                      Item3

I am using MultiLookUp to display the ItemName in one of the report columns. I added this expression to the Textbox:

=Join(MultiLookup(Split(Fields!RelatedItemIdList.Value,","),Fields!RelatedItemIdKey.Value,Fields!ItemName.Value,"DataSet2"),", ")

This shows #Error in the textbox when i run the report.

What am i doing wrong here?

P.S.: My Target Server Version is "SQL Server 2008 R2"
My Reporting Server shows version as "Microsoft SQL Server Reporting Services Version 10.50.1600.1"

Answer 1

Hi There

Thanks for your posting. Can you please explain what is this in your expression (Fields!RelatedItemId  Key.Value). Is this a field in your dataset with a space?

Please have a look on this post that might help you

http://blog.datainspirations.com/2010/03/29/sql-server-2008-r2-reporting-services-look-up-part-ii-2/

Many Thanks

Syed Qazafi Anjum

Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Answer 2

Hi Syed,

Thanks for the reply.

I figured it out. There is no space in there. Its just bad formatting. I had to cast "RelatedItemId" to Varchar. Its working fine now.