SQL Report Builder 3.0 LookupSet Function on SharePoint Lists

Question

I have two SharePoint list named city and town. I have created two datasets for each list in SQL Report Builder 3.0. The city dataset has ID and Title columns. Town dataset has ID, City_Id and Title.

My purpose is showing cityname and it's towns in same row. I use LookupSet function like this:

=Join(LookupSet(Fields!ID.Value, Fields!City_ID.Value, Fields!Title.Value,"Town"), ",")

But, it gives just blank field. How I can use LookupSet function. Thanks

Answer 1

Fields!ID.Value and Fields!City_ID.Value are not same type. Fields!ID is integer, Fields!City_ID.Value is string. So I re-write my expression like this

Join(LookupSet(Fields!ID.Value, CInt(Fields!City_ID.Value), Fields!Title.Value,"Town"), ",")