Calculated Hierarchy member sets for row edge nesting

[timness]

So..

I've been using Report Studio for a few years, but have mostly done relational reporting - lists, charts, fairly basic stuff.

I now have a need to use a cube to create dynamic crosstab reports, and am coming unstuck on this one point.

I need to build a crosstab with the middle elements of a hierarchy exposed as nested values on a row edge. This report will not drill up and down (users don't want to), but needs to have the row edges update as the hierarchy changes.

For background, I work in an environment where there is NO access to Framework Manager - everything has to be done in RS. I can make requests for changes to the cubes I'm using, but they cost my company each time (don't ask), so I have to provide an ROI analysis with every request. Incidentally the cubes are built using Microsoft Analysis Services on a MS SQL platform, not Cognos tools.

In the cube there is a 14 level hierarchy used to represent the company's general ledger structure, and while the levels have been included and are dynamic, the cube does not contain specific 'levels' as unique member sets for use in a report the way I want to.

I'm trying to build a crosstab:
- on the column edges I have a handful of measures as peers (value mtd, budget mtd, variance, value ytd and so on) to give the financial details.
- on the row edges I have members of levels 8, 9, and 10 of the hierarchy nested as crosstab edges. I've done this the brute force way already by adding sets of children as the nested edge details - I need the row edges to grow dynamically, as the company I work for is in an acquisition phase, and the hierarchy structure will change.

The issue with what I've done is that if a new element is added above level 6 in the hierarchy, my nested sets of children will miss the change, as the parent levels didn't exist at the time when I generated the sets of children.

To get aeound this, I'm looking for a way to calculate "members of level 8 of hierarchy" and "members of level 9 of hierarchy" etc to use for nesting on row edges to achieve in 3 to 6 components what I have currently used 50+ sets of children to achieve.

I have tried several different calculations - sets of members using 'level' and 'descendants' and a couple of others, but always get errors because I'm shooting arrows into the abyss.

I'm hoping someone here will understand enough of my rambling description to point me in the right direction.

Any help will be much appreciated.

Cheers,

timness

Sent from my SM-N910G using Tapatalk

[TheFrenchGuy]

Hi,

Can we have a printscreen on how your hierarchy cube is build ?
And a simple example on the result you want ?

For me you have to use the "descendants" function, allowing you to have children of an upper level.
Ex : children([Level8]) = child Level 9
       descendants([Level8],1) = child Level 9
       descendants([Level7],2) = child Level 9

[MFGF]

So..

I've been using Report Studio for a few years, but have mostly done relational reporting - lists, charts, fairly basic stuff.

I now have a need to use a cube to create dynamic crosstab reports, and am coming unstuck on this one point.

I need to build a crosstab with the middle elements of a hierarchy exposed as nested values on a row edge. This report will not drill up and down (users don't want to), but needs to have the row edges update as the hierarchy changes.

For background, I work in an environment where there is NO access to Framework Manager - everything has to be done in RS. I can make requests for changes to the cubes I'm using, but they cost my company each time (don't ask), so I have to provide an ROI analysis with every request. Incidentally the cubes are built using Microsoft Analysis Services on a MS SQL platform, not Cognos tools.

In the cube there is a 14 level hierarchy used to represent the company's general ledger structure, and while the levels have been included and are dynamic, the cube does not contain specific 'levels' as unique member sets for use in a report the way I want to.

I'm trying to build a crosstab:
- on the column edges I have a handful of measures as peers (value mtd, budget mtd, variance, value ytd and so on) to give the financial details.
- on the row edges I have members of levels 8, 9, and 10 of the hierarchy nested as crosstab edges. I've done this the brute force way already by adding sets of children as the nested edge details - I need the row edges to grow dynamically, as the company I work for is in an acquisition phase, and the hierarchy structure will change.

The issue with what I've done is that if a new element is added above level 6 in the hierarchy, my nested sets of children will miss the change, as the parent levels didn't exist at the time when I generated the sets of children.

To get aeound this, I'm looking for a way to calculate "members of level 8 of hierarchy" and "members of level 9 of hierarchy" etc to use for nesting on row edges to achieve in 3 to 6 components what I have currently used 50+ sets of children to achieve.

I have tried several different calculations - sets of members using 'level' and 'descendants' and a couple of others, but always get errors because I'm shooting arrows into the abyss.

I'm hoping someone here will understand enough of my rambling description to point me in the right direction.

Any help will be much appreciated.

Cheers,

timness

Sent from my SM-N910G using Tapatalk

Hi,

What is at the very top of the hierarchy? Is there a single root member at the apex? If so, you can use this in a descendants() function and refer to 8 levels down for example.

Cheers!

MF.

[timness]

Hi,

Can we have a printscreen on how your hierarchy cube is build ?
And a simple example on the result you want ?

For me you have to use the "descendants" function, allowing you to have children of an upper level.
Ex : children([Level8]) = child Level 9
       descendants([Level8],1) = child Level 9
       descendants([Level7],2) = child Level 9

Hi TheFrenchGuy,

Screenshot of Hierarchy attached to this post.

Cheers,

Timness

[timness]

Hi,

Can we have a printscreen on how your hierarchy cube is build ?
And a simple example on the result you want ?

For me you have to use the "descendants" function, allowing you to have children of an upper level.
Ex : children([Level8]) = child Level 9
       descendants([Level8],1) = child Level 9
       descendants([Level7],2) = child Level 9

and a screenshot of what I have done using what feels like a brute force approach previously attached to this post...

[MFGF]

Hi TheFrenchGuy,

Screenshot of Hierarchy attached to this post.

Cheers,

Timness

Ah - good. You have an "All" apex member at the top of the hierarchy. This means you can use expressions such as:

descendants([All], 8) to get the level members 8 logical levels below the All member.

Cheers!

MF.

[timness]

Ah - good. You have an "All" apex member at the top of the hierarchy. This means you can use expressions such as:

descendants([All], 8) to get the level members 8 logical levels below the All member.

Cheers!

MF.

Thanks MFGF,

i'm sitting here working on it at the moment.. I wanted to try the suggestions so far before i made any more comment.

turns out, that due to my lack of knowledge, i was trying to use a query calculation to create member sets. I've since used a data item object, and dropped in your calculation and everthing is starting to work.

:) :) :)

[timness]

hah!

and so figuring out that row edges needed to be data items follows on to using query calculations to create measures, and i'm heading in the right direction.

do you think I would be able to create a calculation along the lines of:

case when

[level5] = 'x'

then [actual]*-1

else

[actual]

end

???

i'm trying to make revenue and costs out of a general ledger both appear as 'positive' for non-financial types.

[TheFrenchGuy]

Bello

It should works !
Have you try it ?

[timness]

Thanks again to MFGF and TheFrenchGuy, my crosstab is now working nicely.

I have a follow up question:

The hierarchy I'm working with is unbalanced - this is not under my control. Level 9 is the end of the hierarchy in some instances, and in others Level 10 is the end.

If I try to nest my calculated Level 10 descendant Query Item in my crosstab, the report doesn't run, which I can understand from a logic perspective.

Is there a way of 'conditionally nesting' (i don't have the correct terminology here) the level 10 set into the Row edge so that it will populate when present, but be ignored if not?

I've attached a screenshot of what i'm trying to achieve - where <#Level 10#> shows is what i want to make 'conditional'

If any more information is required to answer my question, please let me know.

Cheers,

Tim

[timness]

Bello

It should works !
Have you try it ?

thanks TheFrenchGuy.

Turns out that it's more complicated than that.

the comparison i need to make is to compare a string with the member caption  of 'level 5'

I've not figured out how to do this properly, so I'm getting an error because i'm trying to compare incompatible types..

Any suggestions?

thanks,

Tim

[TheFrenchGuy]

Hey

what's the key to show level 10 or not ?

To make your crosstab more "agile", you have, for me, 3 solutions :
_Create a variable, copy paste your crosstab and apply a render variable
_Create a variable, apply a style variable on your crosstab (near the same as before)
_Modify your data item lvl9 in "if ([lvl10] is null) then [lvl9] else [lvl10]

For comparison, you have to use the good functions, tell me more !