Microsoft SSRS 2016 Lookup (on dynamic dataset) and return multiple (n) columns

Question

I have a unique Microsoft SSRS (2016) situation:

I have this table below:

Table A (3 columns; key column: OrderID) the main table, main dataset - fixed dataset:

OrderID Region  Store
124     FL      ORL-23
125     CA      SFO-21
126     TX      DAL-19

I have another table, Table B (4 columns; key column: OrderID) in another dataset (say Lookup dataset):

OrderID Gratuity    DeliveryFee Surcharge
124     $2.50       $3.00       $0.75
125     $3.50       $5.50       $0.67
126     $4.75       $3.75       $0.45

Table B (Lookup dataset) is actually a dynamic table; it can have 3 columns (as shown above) apart from OrderID, the base column; in some other cases (via parameters), it can have 4 columns (apart from OrderID), and so on - - say, n columns apart from OrderID, where n depends on a parameter value I choose.

I need to get the following result set (Table C with 6 columns (in this particular case, when Table B has 3 dynamic columns)), lookedup on OrderID column):

OrderID Region  Store   Gratuity    DeliveryFee Surcharge
124     FL      ORL-23  $2.50       $3.00       $0.75
125     CA      SFO-21  $3.50       $5.50       $0.67
126     TX      DAL-19  $4.75       $3.75       $0.45

Table A: fixed 3 columns; Table B: n varying columns

I need to get table C in SSRS 2016; I have options such as Lookup, LookupSet, MultiLookup, etc.

Remember that Table C must have 3 (or n, where n is the number of columns in the dynamic dataset Table B) new columns (Gratuity, DeliveryFee, Surcharge), not 1 column concatenated in this way:

Fields!Gratuity.Value & ", " & Fields!DeliveryFee.Value & ", " &  Fields!Surcharge.Value

Hence Table C can have 6 columns, 7 columns, ...i.e. (3+n) columns, since Table B has a varying number of columns, depending on a parameter chosen.

How can I achieve this? Or should I try some options such as SubReport, or something?

Due to some business logic, I cannot use JOIN; table A- here I have simplified (actually Table A has many more columns with a lot of calculations such as SUM, DISTINCT COUNT, etc). It has some aggregated calculations.

Answer 1

This can be done in your query so it is one dataset in SSRS.

SELECT
    a.OrderID
    ,a.Region
    ,a.Store
    ,b.Gratuity
    ,b.DeliveryFee
    ,b.Surcharge
FROM TableA A
JOIN TableB B ON
    a.OrderID = b.OrderID