How to capture single node value in hierarchy

Requirement :-

As per the business requirement, we need to hide hierarchy node and accordingly measure values must be updated at parent node level.

By default, even we hide the child node, parent node displays the aggregated measure value including the hidden node in hierarchy.

Scenario :-

In a BW hierarchy, there are two nodes and there sub nodes as follows:

Note: Hierarchy displayed Inverted

Now we need to apply below mentioned condition:

If (XYZ) is greater than 0 (Positive Value) then Put it under PR_NODE.

If (XYZ) is smaller than 0 (Negative Value) then put it under NODE_111

Issue:-

After applying the hidden condition, XYZ node is hidden from NODE_111,

However, measure against NODE_111, still include the value for hidden node that is XYZ (Please see the value of NODE_222 & NODE_111)

For more details please see the below screen shot.

Logic involved:-

We have captured the value of hidden node and subtract it from the subtotal to get the balanced hierarchy.

Solution

Steps to achieve the correct results and getting value calculated automatically

1. Create New Running Total

2.In Evaluation Condition Section Select “Use Formula”

3.In Formula mention the Node for which you want to capture a value.

   For E.g. I have below mention Hierarchy “Test_Hierarchy”

   At report level it look like below mention Screen Shot

4.Then mentions the node for which you want capture a value.

   In below example i want to capture value of “XYZ” which is 656250082.00 then I will right down below mention formula.

5.Then save & close Formula editor and close the running total window.

6.Now we will have value of XYZ node in “Node Value” variable, and we will subtract this value from Node_111

   and Node_222 to get the expected output.

7. You can use this object for different purpose like adding or subtraction from subtotals depending upon the requirements.

Thanks,

Rakesh Lotankar

BO Consultant

Accenture