I have a complex report (lots of tables/joins) based on a sales order. 

Here is the schema tables/relations defined in the report:

I need to redirect to this at the end of a process.  My process loop is working, just need to add this redirection.

So far, I have a PXReportResultset defined and I have a SelectFrom that matches the schema of the report (had to create derived DAC classes to join to same tables more than once):

            PXReportResultset salesOrders = new PXReportResultset(typeof(SOOrder),
                typeof(Branch),
                typeof(BAccount),
                typeof(CustomerAcc),
                typeof(BillingAddress),
                typeof(CompanyAddress),
                typeof(SOLine),
                typeof(ShippingAddress),
                typeof(SOOrderType),
                typeof(Note),
                typeof(LineNote),
                typeof(Terms),
                typeof(CompanyContact),
                typeof(Carrier),
                typeof(FOBPoint),
                typeof(ShippingContact),
                typeof(BillingContact),
                typeof(ShipTerms),
                typeof(CommonSetup),
                typeof(PX.SM.PreferencesGeneral),
                typeof(Customer),
                typeof(EmContact),
                typeof(SOOrderTypeOperation));

SelectFrom<SOOrder>
                    .InnerJoin<Branch>.On<SOOrder.branchID.IsEqual<Branch.branchID>>
                    .InnerJoin<BAccount>.On<Branch.bAccountID.IsEqual<BAccount.bAccountID>>
                    .InnerJoin<CustomerAcc>.On<SOOrder.customerID.IsEqual<CustomerAcc.bAccountID>>
                    .LeftJoin<BillingAddress>.On<SOOrder.billAddressID.IsEqual<BillingAddress.addressID>>
                    .LeftJoin<CompanyAddress>.On<BAccount.defAddressID.IsEqual<CompanyAddress.addressID>.And<BAccount.bAccountID.IsEqual<CompanyAddress.bAccountID>>>
                    .LeftJoin<SOLine>.On<SOOrder.orderType.IsEqual<SOLine.orderType>.And<SOOrder.orderNbr.IsEqual<SOLine.orderNbr>>>
                    .LeftJoin<ShippingAddress>.On<SOOrder.shipAddressID.IsEqual<ShippingAddress.addressID>>
                    .InnerJoin<SOOrderType>.On<SOOrder.orderType.IsEqual<SOOrderType.orderType>>
                    .LeftJoin<Note>.On<SOOrder.noteID.IsEqual<Note.noteID>>
                    .LeftJoin<LineNote>.On<SOLine.noteID.IsEqual<LineNote.noteID>>
                    .LeftJoin<Terms>.On<SOOrder.termsID.IsEqual<Terms.termsID>>
                    .LeftJoin<CompanyContact>.On<BAccount.defContactID.IsEqual<CompanyContact.contactID>>
                    .LeftJoin<Carrier>.On<SOOrder.shipVia.IsEqual<Carrier.carrierID>>
                    .LeftJoin<FOBPoint>.On<SOOrder.fOBPoint.IsEqual<FOBPoint.fOBPointID>>
                    .LeftJoin<ShippingContact>.On<SOOrder.shipContactID.IsEqual<ShippingContact.contactID>>
                    .LeftJoin<BillingContact>.On<SOOrder.billContactID.IsEqual<BillingContact.contactID>>
                    .LeftJoin<ShipTerms>.On<SOOrder.shipTermsID.IsEqual<ShipTerms.shipTermsID>>
                    .CrossJoin<CommonSetup>
                    .CrossJoin<PX.SM.PreferencesGeneral>
                    .LeftJoin<Customer>.On<SOOrder.customerID.IsEqual<Customer.bAccountID>>
                    .LeftJoin<EmContact>.On<SOOrder.ownerID.IsEqual<EmContact.contactID>>
                    .LeftJoin<SOOrderTypeOperation>.On<SOOrderType.orderType.IsEqual<SOOrderTypeOperation.orderType>.And<SOOrderType.defaultOperation.IsEqual<SOOrderTypeOperation.operation>>>
                    .View.Select(graph).Where(sp => sp.Record.OrderNbr == order.OrderNbr && sp.Record.OrderType == order.OrderType)

What I’m struggling with is how to cast the SelectFrom to a PXResultSet (due to many SOLine, LineNotes, etc. I cannot use FirstOrDefault otherwise I only get 1 row for these)

Then, how to get this into PXReportResultset?  If I add FirstOrDefault() I can get a PXResult, and add to my PXReportResultset and the report runs, but I only get 1 SOLine per order.

Or, is there a better way to do this vs. using PXReportRequiredException?  Can I just pass the OrderNbr to a parameter, run/execute each report but have it all combine into one report/preview window?  This way, the report can just do the query itself, right? 

Thanks for any suggestions.