Search code examples
reporting-servicesreportbuilder3.0

LookUpSet - Count Items Returned

I have a report with 2 datasets on different servers. I have used the following expression to get data from one of the datasets:

=Join(LookupSet(Fields!field1.Value, Fields!field_1.Value, Fields!submitted.Value, "DataSet1"),",")

This gives me all the values expected, but how do I Count these values?

I have tried 

=Join(LookupSet(Fields!field1.Value, Fields!field_1.Value, Fields!submitted.Value, "DataSet1"),",").Length

and this just counts every character in the returned values rather than those separated by a comma.

and I have tried 

=Count(=Join(LookupSet(Fields!field1.Value, Fields!field_1.Value, Fields!submitted.Value, "DataSet1"),","))

but this just gives 1.

Solution

  • To get the number of items in the array, use:

    =LookupSet(Fields!field1.Value
  , Fields!field_1.Value
  , Fields!submitted.Value
  , "DataSet1").Length

    It looks like you were trying to apply your count to the Join result, but this will be a string, and you want to look at the array, i.e. just apply Length to the LookupSet result.