Powerplay---aggregation--Need Help

[shuklap]

Powerplay handles aggregation as:



Lvl 1Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  PL0010Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  $10

Lvl 2Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  └ PL0009Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  $50

Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  └ PL0008Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  ($40)

Lvl1 is a natural aggregation of the child records in Lvl 2.



However, the user wants as follows:

Lvl 1Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  PL0010Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  $10

Lvl 2Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  └ PL0009Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  $50

Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  └ PL0008Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  $40



The user does not want to see negative values in the child records. user requests that the negative values be ââ,¬Å"maskedââ,¬Â in the OLAP cube to show positive values. However, user wants to show natural aggregation of the parent (Lvl 1).



Cheers,

[dwood09]

hi shuklap,

signage ... what a doozie this is ...

I have masked signage in powerplay by applying the following process ... now this may get ugly, but bear with me ...

1.  You need a field in your data source so that for each account / element that you want to reverse the signage for, you record a -1.  All elements where you don't want to reverse the sign populate the field with a 1.

2.  Bring this new field in to transformer, along with your accounts / elements

3. create the "source" measures, per normal

4. create another measure from the signage field that you created with your element / account dimension.

5. change this new signage measures "regular" rollup to "minimum"

6. for each measure that needs to have the signage applied to it you will also need a calculated measure.  The caclulation should be the original "Source" measure multiplied by the new signage measure

7. the regular rollup must be set to "After"

8. exclude the original source measures and the signage measure from viewing at the cube properties level

NB: If you are also using "currancy" then this will need to be applied at the calculated measure level, and not at the "source" level.

Hope this helps.

cheers,
DW

[O2B_UpNorth]

I'm not sure if this is where you were headed or not, but I'll take a shot.

At times, I've seen a scenario like this:

level1= Gross MarginÃ,  Ã,  Ã,  Ã, $10

Ã,  Ã,  level2= SalesÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  $50
Ã,  Ã,  level2= Cost of Goods SoldÃ,  Ã, Ã,  Ã,  Ã, Ã,  ( $40 )

where the requirement was to have the Cost of Goods Sold category within level2 display as a positive instead of a negative.

In these cases Cognos makes it easy to eliminate the negative signage of the COGS item, by selecting that category in the Category Map, entering its Properties dialog, and turning on the "Reverse the sign" option.

Not sure if this is the scenario you were after or not, but it will work in cases like those I've described above.