Combining indirectly linked dictionary groups

[SueHarris]

Could some please help me with what the correct structure for my report should be. I'm trying to product a report which has rows with summed totals for 2 datasets summed across 2 other datasets which are indirectly linked to each other (but directly linked to both of the datasets I'm trying to sum), but haven't been able to get the report structure correct (it either sums the wrong thing, or records are missing).
There are 4 data dictionaries (simplified for this example): RetailSales (RS), TradeSales (TS), Customers and Products. RS & TS are essentially the same structure linked to Customers and Products.
I would like to product a report which essentially looks like:

CustomerA Product1 sum(RS of P1) sum(TS of P1)
Product3 sum(RS of P3) sum(TS of P3)
CustomerB ....

But haven't been able to quite get the report right since Products and Customers aren't directly linked and I'm trying to sum two different datasets in a single row. Can anyone please help?

[Lech Kulikowski]

Hello,

Please send us a sample report with test data that reproduces the issue for analysis.

Thank you.

[SueHarris]

Please send us a sample report with test data that reproduces the issue for analysis.

This isn't precisely an issue, rather a 'I have no idea how to achieve the report output I want'. Attached is a report with sample data which shows it not working (all the customer records are showing in the product rows instead of limiting to the current customer datarow), and also attached is the report that I'm trying to achieve (the first 2 columns in the RetailSales and TradeSales dataset components are only used to prove the output is correct and would be removed).

Thank you.

[Lech Kulikowski]

Hello,

With this data structure, it is difficult to come up with an immediate way to achieve such a report.
Is it possible to modify the data - for example, merge the Retail and Trade tables into one data source?

Thank you.

[SueHarris]

Hello,

With this data structure, it is difficult to come up with an immediate way to achieve such a report.
Is it possible to modify the data - for example, merge the Retail and Trade tables into one data source?

Thank you.

Unfortunately not, the tables in question have those common fields for this particular report but are otherwise different and link to different tables. Unless there is some way to create a virtual data source inside the report itself?

[Lech Kulikowski]

Hello,

In this case, only the following result.

Thank you.