LookUpSet - Count Items Returned

Question

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.

Answer 1

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.