Multiple 1-N Relationships in a Report

[GummowN]

Our universe consists of 1 header table and 4 joined tables (with 1-N relationships to header).

When I try to validate the universe - in terms of being sufficiently user friendly for non-technical staff - I find there are problems in adding columns from 2 of the N tables.

It is possible if I create a query and use freehand query to access the other table - however this is not user friendly - and freehand SQL might as well be written in Zulu for all the help it is for our business users.

Any suggestions?

Structure:
Header - 1 row per deal
Referrer - N rows per deal
Executor - N rows per deal
Assets - N rows per deal
Revenues - N rows per deal

Report Requirement:
Header information
1st Referrer information
1st Executor information
Current Assets (sum of asset value)
Year End Assets (sum of assets for all periods to end of year)
Revenues (as per Assets)

If at first you don't succeed, try

http://www.tek-tips.com

for the answer.

 

[skrandel]

This will work fine if you use contexts. You need a context for each N. Then BO will generate multiple SQL statements and your fan trap will be gone.

Steve Krandel
Intuit

 

[GummowN]

Tried contexts and it doesnt work either

2 of the N tables are just Dimensions - and 2 are a mix of dimensions and measures.

When I have returned 2 of the N tables - when I select one field from a table - the others from the other N table appear in italics and are kind of greyed out.

Are there any settings that would allow this sort of join to occur and leave me reprot side to deal with the results

If at first you don't succeed, try

http://www.tek-tips.com

for the answer.

 

[blom0344]

The cardinality is the relevant issue here. Whether data is dimensional or measure, the use of contexts should be the proper solution.
If you start using contexts then EACH and EVERY object should be within a context and you need to set properties for the universe for working with them.

Check out the documentation on fan trap resolution..

Ties Blom