Master Report with 2 sub reports - best strategy

• aaron.reese

  SSChampion

  Points: 13498

  More actions

  January 28, 2015 at 7:48 am

  #397936

  Guys, I am pretty good with SQL but the SSRS side sometimes defeats me.

  I have a master data set which links orders to refunds (Customer ID, Order ID, Refund ID): One order COULD have more than one refund, but a refund can only ever be for one order

  I want to produce a report of these master records (list control, linked to master dataset, break on Refund ID)

  But then I want to show the Order and the refund side-by side. The problem is that each order is made up of an order header, order lines and order payments Hdr-Lines = 1:many, Hdr-Paymt = 1:many

  E.g.

  Hdr:

  OrderID:123456, OrderDate:01/01/2015, OrderValue:555

  Lines:

  OrderID: 123456, LineID:1, ItemCode:ABCD, Qty:1, Price:50

  OrderID: 123456, LineID:2, ItemCode:DEFG, Qty:2, Price:150

  OrderID: 123456, LineID:3, ItemCode:WXYZ, Qty:1, Price:205

  Payment:

  OrderID: 123456, PaymentID:1, PaymentMethod:CC, Amt:200

  OrderID: 123456, PaymentID:2, PaymentMethod:AX, Amt:355

  The refunds follow the same structure but from different tables.

  I 'COULD' put all the header, line and payment details into the master data set with cartesian joins and then put a whole bunch of tablix into the report filtered and grouped by the relevant part of the dataset, show the record details in the lowest group that does not duplicate and hide the detail records but that doesn't feel like good design.

  I could build sub-reports for the order and refund and pass the orderID and refundID down to them as parameters to filter the report to the correct order/refund but since I don't have a reliable cutoff for the orders, the sub-report dataset(s) contain all the records for all orders even though the master dataset is a very small subset of these orders (54 orders out of approx 4 million!) and the sub-reports still need separate queries as there are two 1:many relationships to be reported. and so the queries for the sub reports are really inefficient.

  Is there another option that I have not considered? I can't believe this scenario is unique.

  Aaron