Mixing multiple contexts in one table

[SpikeTheLobster]

Hi all,

I have a difficult situation that I can't resolve. The DB has four tables (that matter): I'll call them Main, Headers, Values and Revenues. Main is linked to each of the others in a separate context for each.

What I need is to be able to build a single table with all key values ("Assignment Numbers") from Main, plus their related values (Header No., Value No. and Revenue No. for simplicity) in ONE table, with blanks in columns from incompatible contexts.

That is, lines should read:

Assignment No. | Header No. | Value No. | Revenue No.
1234 123
4321 321

And so on. I've tried using three separate queries (Assignment No. and Header No., Assignment No. and Value No., Assignment No. and Revenue No.) and unioning them, but I only get back two columns: the first two. I read on another post that using User Variables might solve this, but I can't figure out how.

Any pointers would be much appreciated!

 

[blom0344]

Use 3 seperate dataproviders with Assignment no. as the common dimension (fetch it in all 3 DP's) and the three other objects as detail-objects. Link the 3 DP's over the common dimension and you can show all data in one table.
Should work with all tables having a one-to-one relationship..


T. Blom
Information analyst
tbl@shimano-eu.com

 

[SpikeTheLobster]

Thanks...

Unfortunately, they are all one-to-many and using three separate queries returns similar results. Despite their automatic linking on the common field (which is nice), BO won't let me place the three (separate context) details in the same table.

It wants to do a header/block thing - although this gives fairly nice layout (with the Assignment No. as a title per section and the three separate detail numbers below), it's not one table.

My three DPs all have Assignment No. (common) and one other item in them, in case I'm doing something stupid.

 

[blom0344]

Hmmm, just tested it and to my surprise it does not allow the third detail into the table. What does work at my end is to create a additional variable (in the report) on the third detail like:

=Max(<third detail>)

and use this in the table. BO accepts that.

If I just create a measure without the 'Max' it does allow the variable in the table , but it throws a '#ERR' error.

After all these years, there are still surprises around the corner

T. Blom
Information analyst
tbl@shimano-eu.com