How to group on the values returned by Lookup/MultiLookup/LookupSet

Question

I have 2 datasets that come from two different data sources. DataSet1 and DataSet2.

This is how DataSet1 looks like:

CustomerName      City      State            Zip
Cust1           Cust1City  Cust1State     Cust1Zip
Cust2           Cust2City  Cust2State     Cust2Zip
Cust3           Cust3City  Cust3State     Cust3Zip

This is how DataSet2 looks like:

CustomerName     OrderDate       OrderType       Amount
Cust1           01/01/2014       OrderType1       $100
Cust1           01/02/2014       OrderType2       $150
Cust1           01/03/2014       OrderType3       $75
Cust2           01/01/2014       OrderType1       $250
Cust2           01/02/2014       OrderType3       $450
Cust3           01/01/2014       OrderType1       $145
Cust3           01/02/2014       OrderType2       $400
Cust3           01/03/2014       OrderType3       $10

I am trying to display this information in SSRS report that will have Group on City, State, Zip from DataSet1 and then Group on OrderDate.

Since OrderDate is coming from the different dataset, i am not able to Group on it.

I tried using Lookup and MultiLookup but that throws me this error:

The Group expression for grouping 'table1_Details_Group' uses a lookup function 
with an expression that returned a datatype that is not valid for the lookup 
function. The datatype must be an RDL Variant Type.

Lookup, MultiLookup, LookupSet returns array but is there a way to use them to achieve the grouping on the values returned by them?

Is there an alternative to this?

This is how my Grouping expression looks like:

=LookupSet(Fields!CustomerName.Value,Fields!CustomerName.Value,Fields!OrderDate.Value,"DataSet2")

Mike Honey Mike Honey · Accepted Answer · 2014-02-06T02:30:35

Your design doesnt make any sense to me - how can you expect to group on an expression that returns multiple values?

I would try basing the table on Dataset2, then you can use a Lookup to get anything you need from Dataset1 (assuming CustomerName is unique across Dataset1).

How to group on the values returned by Lookup/MultiLookup/LookupSet

1 Answers