Master-Detail Relationship. Summary of detail data required

[silversuit]

Hi all,

I have an issue in Report Studio where im required to present a report off two separate sources in the one Package via a Master-Detail relationship.

For the master query I have - 'INSTRUMENT',  'DEAL_NUMBER',  'PRINCIPAL_AMOUNT'
For my detail query I have - 'DEAL_NUMBER', 'AMOUNT X', 'AMOUNT Y', 'AMOUNT Z'

Dimensional data sources.

My link between the master and detail query is 'DEAL_NUMBER' (0-Many DEAL_NUMBERs for 1 INSTRUMENT). INSTRUMENT does not exist in my source for the detail.

I need to be able to group my data by INSTRUMENT and have a sum of 'AMOUNT X', 'AMOUNT Y' & 'AMOUNT Z' at the instrument level.

Can anyone please help me try and solve this? My apologies if something similar has been asked for before.

Silversuit.

[wyconian]

Hi

Instead of using a master detail relatiosnhip try adding a query for your master query and a a separate query for the detail query.  You can then join these queries together in another query with the join based on the deal number.  You should then be able to have all the data in 1 query and do any kind of grouping or aggregation that you want to.

Good luck

[silversuit]

Thanks for your reply wyconian. Unfortunately I can't join these two queries together as im working off 2 TM1 cubes. So Dimensional style.

I did use the UNION however, using some MAX, MINS, SUMs etc on the UNION set to get the report as I needed. Not the most elegant solution though im happy I got it out.

Cheers and all the best.

Silversuit