Report from multiple tables

[Faustin_Ngassa]

Hi,

I am building a sales report.

I have a SalesOrders Table with a SalesOrderDetails child table that lists the products sold in the Parents SalesOrder

I have a GoodsReceivedNotes Table with a GoodsReceivedNoteDetails child table that lists the products delivered

I would like to make a report showing, for a user provided date range and customer name, the following results:

• the list of products from the SalesOrderDetails
• the quantity ordered for each product
• the quantity delivered for each product

I am not sure what the best way to go about is.
I have created a SalesReport table in Google Sheets and a form in my app with

• Customer Id
• StartDate
• EndDate

Then a VC called _SalesOrderDetailsInTimeFrame with the formula
SELECT(
SalesOrderDetails[SalesOrderDetail Id],
AND( [SalesOrder Id].[Customer Id] = [Customer Id],
[_SalesOrderDetail_Date] >= [StartDate],
[_SalesOrderDetail_Date] <= [EndDate] )
)

The VC returns a table that lists the products ordered in the correct time frame and customer.

I could similarly create a VC that returns the list of products delivered in the same time frame and customer.

My question is: How do I display in the same “table” , that list of products with the SUM of quantities ordered, SUM of quantities delivered, Grouped by Products?

Is there a better way to do what I am trying to accomplish in a totally different (maybe easier) fashion using maybe slices and views, etc?

Thank you!