Nest Vertical Dynarange

regis.cabaret · Jan 8th 2016, 6:45am

Hi there,

I fail to create a report that returns all non-empty intersections of 4 dimensions.

I use overlapping dynaranges and refer to the outer dynarange as variable in the data filter as shown below:

[IMG:http://www.lightstream.asia/temp/Screen Shot 2016-01-08 at 12.32.15 PM.png]

Note: Each
- F7:J7 cells contain PALO.DATA formulas
- All 4 dynarange expands until the last column (Column J: Billable Hours)

[IMG:http://www.lightstream.asia/temp/Screen Shot 2016-01-08 at 12.40.10 PM.png]

[IMG:http://www.lightstream.asia/temp/Screen Shot 2016-01-08 at 12.39.51 PM.png]

[IMG:http://www.lightstream.asia/temp/Screen Shot 2016-01-08 at 12.40.22 PM.png]

[IMG:http://www.lightstream.asia/temp/Screen Shot 2016-01-08 at 12.40.30 PM.png]

Outermost dynarange (Activity):

[IMG:http://www.lightstream.asia/temp/Screen Shot 2016-01-08 at 12.22.17 PM.png]

Second dynarange (Date):

[IMG:http://www.lightstream.asia/temp/Screen Shot 2016-01-08 at 12.35.04 PM.png]

Last dynarange (Phase):

[IMG:http://www.lightstream.asia/temp/Screen Shot 2016-01-08 at 12.37.30 PM.png]

Yet, what results is no row although there are data at those intersections.

Any suggestion what I am doing wrong?

Thanks

Regis

p.hildebrandt · Jan 8th 2016, 8:31am

Hi Regis,

I guess there is no result due to the constraint described in Jedox knowledge base:
knowledgebase.jedox.com/knowledgebase/menu-tools-dynaranges/

Here is the interesting part of the article:

Also allowed are two Dynaranges that are dependent on each other, i.e. Subset of inner DynaRange refers to outer DynaRange. Note that when referring from the formula of the dependent DynaRange to its “parent”, you have to use the parents name (for example, “DynaRange1”), not a cell reference. Cell references in DynaRange source formulas are static, meaning they are not rewritten as the DynaRange expands.
In the typical use-case the two DynaRanges have the same direction. It should be avoided to have either the right-side or the bottom edges of DynaRanges that move in the same direction “touch” each other.
When DynaRange subsets are interdependent (i.e. one DynaRange refers to another), nesting more than 2 Dynaranges is currently not supported.

Regards,
Peer

regis.cabaret · Jan 8th 2016, 11:14am

Thanks Peer,

As a matter of fact, I had seen this but reading this thread Dynarange: how to build a table using over 2 overlapping vertical dynaranges (mostly works). suggested that it was possible.

Is there another way of returning all non-empty intersections? I was told to consider the standard view with suppress missing option in Jedox 6 but it would have another issue: the dimension selections are static while I need them to be dynamic ("All base elements of this member" for example, rather than selecting all individual base members... if I had a new base member, it won't be taken into account in the view).

Thanks,

Regis

p.hildebrandt · Jan 11th 2016, 11:36am

Hi Regis,

whenever I tried to set up nested dynaranges I came out with something like that:
[IMG:http://fs5.directupload.net/images/160111/xxdnuexp.png]

Problem if working: The result is some kind of treeview with hierarchy.
If expanding or collapsing of dynaranges is not really necessary you can hide all rows except for the last one and write the content of dynarange 1..n-1 to the row of the n-th dynarange:
[IMG:http://fs5.directupload.net/images/160111/wzfsfne7.png]

Regards, Peer

regis.cabaret · Jan 20th 2016, 3:21pm

Thanks Peer,

I still can't get it to work.
In your data filter, what do you use for the element selection of the dimensions that are on the left hand side?
Say, my dimension sequence is: Date (Dynarange1), Project (Dynarange2), Phase (Dynarange3), Activity (Dynarange4).
When I set the data filter for Dynarange3, do you leave Date and Project blank or do you refer to their Dynarange as variable?

Thanks in advance,

Regis

p.hildebrandt · Jan 21st 2016, 6:31am

I do set the datafilter to the corresponding dynaranges as variable like you mentioned.
Of course the only dynarange without any reference to another is the most left (first dynarange).
But I have never tried to combine 4 dnyaranges.

If this is still not working, I do not have another idea right now.

regis.cabaret · Feb 5th 2016, 3:16am

Thanks Peer,

I couldn't make it work. I tried all kind of different ways to no avail. I ended up using one dynarange at the top left and subset to its right. It works as long as each element in the dynarange intersects with only one member from the other dimensions to the left.

Thanks

Regis

Nest Vertical Dynarange

Nest Vertical Dynarange

Share

Tags

Similar Threads