Planning with Hierarchies at a particular level

[Former Member]

Hi Experts,

We have the below requirement to store the data(in planning) on a particular level of hierarchy.

For instance:

We have a leveled hierarchy object ZHIER with 7 levels (level 7 being the leaf node)  and a key figure ZKF.

Each level in the hierarchy is associated with a correction node(used to correct the data) as a child node of the level.

We have activated the hierarchy in the Bex Query Designer.

If a user enters the data on level 3, I want to store the data (difference of data entered and the of data in infocube) in the correction node.

Ex: Infocube as data as:

A              B             C           Level 7X          10

A              B             D           Level 7Y          10

A              B             E           Level 7Z           10

Report shows data as : Taken one row for example

A         B           C             Level 1A                 10

A         B           C              Level 2A                10

A         B           C               Level 3A               10

A         B           C                Level 4A              10

A         B           C                 Level 5A             10

A         B           C                  Level 6A            10

A         B           C                   Level 7A           10

If a user changes the data at level 3A from 10 to 20, then the delta 10 should reside in correction node and not disaggregate between the child nodes.

The report would look like:

A         B           C             Level 1A                               20

A         B           C                Level 2A                            20

A         B           C                   Level 3A                         20

A         B           C                     Level 4A                       10

A         B           C                        Level 5A                    10

A         B           C                          Level 6A                  10

A         B           C                             Level 7A               10

A         B           C                     Level 3A_Correction      10

We have followed naming convention to achieve this.

The issue is the level 7A is passed in the backend.

Any pointers how to identify which level the user is entering the data and store the data at that level itself.

We are using Analysis Office as the front end tool.

Thanks in advance.

Regards,

Priyanka

[cornelia_lezoch · Accepted solution]

Hello Priyanka,

from what you describe, I assume you use a hierarchy with text nodes and disaggregation in the query.

In this scenario it is not possible to have an individual calculation that is done before the disaggregation.

you have two options:

a) in the settings for the disaggregation you can disaggregate ain relation to another structure element. So if you create another structure element which has value = 1 for the correction node and value = 0 for all other nodes, it result in what you desire

b) create a hierarchy with postable nodes (info objekts for each level). then you will have data on the combination of char=level with char=most detailed level = # and from this # you can rebbok the value to the desired leaf.

regards

Cornelia

[Former Member · Accepted solution]

Hello Cornelia,

Thanks for the reply. Sorry for the delayed response.

One thing i can confirm is that we are not using Text nodes. All the nodes are exiting as values in the same Infoobject.

about your options:

a) How do we design it? When I select Dis-aggregation with reference to another structure, the dropdown shows me all the Keyfigures from that query. So do you mean we create a dummy variable with 1 on the lower correction node and do it for the whole hierarchy?

b) I think we have this currently(But same infoobject at all levels). I can enter data at any level, but the cells are input enabled only when i select one of disaggregationj options & Type of distribution.

Will be eagerly waiting for you reply.

Regards,

Priyanka

[gregor_dieckmann]

Hi Priyanka,

what you want is a very special kind of disaggregation mode that does not exist. Technically this can be simulated using disaggregation of the delta with respect some reference key figure (structure element in the query) that has exactly the value 1 for the leaf corresponding to the 'postable' hierarchy node; the value of the other leaves has to be 0. But then you would need at least a reference key figure for all hierarchy levels and you would need to be able to set the reference for the disaggregation at run time (this feature also is not implemented yet).

With VBA you might be able to simulate this using just one reference key figure: whenever a value is changed on the hierarchy node you set the value 1 for the corresponding leaf and value 0 for all other leves. Then delta disaggregation with respect to the reference key figure would do the job.

Regards,

Gregor