EPM report, node aggregation

[Former Member]

Hi All,

I got the following challenge.

ROW:

- Account (defined as fixed Hierarchy)

- Practice (defined as Base level) - I have Hierarchy Practice_ALL

- CostCenter (defined as Base level) - I have Hierarchy CC_ALL

Column:

- Category

- Time

Account group_1 = Hierarchy node for Account1 and Account2

I have more than one Account_group

what is happening:

Report sample:

Account, Practice, CostCenter, Value (cat+time)

Account_group_1, Practice1, CC_1, 10

Account_group_1, Practice2, CC_2, 20

Account1, Practice1, CC_1, 7

Account2, Practice1, CC_1, 3

Account1, Practice2, CC_2, 10

Account2, Practice2, CC_2, 10

....

My expectation are:

Account_Group_1, Practice_ALL, CC_ALL 30

Account1, Practice1, CC_1, 7

Account2, Practice1, CC_1, 3

Account1, Practice2, CC_2, 10

Account2, Practice2, CC_2, 10

or

is there any way to override the "Practice" and "Costcenter" with Practice_All and CC_ALL where the Account = NODE?

Thanks.

Regards,

[former_member186338 · Accepted solution]

Hi Pheno,

You have to set selection for:

- Practice Practice_ALL and base level

- CostCenter CC_ALL and base level

Then you can remove unnecessary lines from report...

Vadim

[Former Member · Accepted solution]

Hi Vadim,

Thanks.

If I put Base level, it also show the practice and  costcenter base level against the GroupNode.

When you say PRactice_all and CC_ALL, are you saying by Member Only or Member and Base?

Thanks

[former_member186338 · Accepted solution]

Member and Base

Vadim

[Former Member · Accepted solution]

Thanks.

I tried that before, but, still gap.

When the Account is BASE, I do not want to see the Practice_ALL and CC_ALL, I want to see only Base level of Practice/CC.

in another word

1. If Account is node/parent:  I want to see only, practice: Practicel_all and CC_ALL collapsed.

2. If Account is Base: I want to see only base for practice and CC

Thanks

[former_member186338 · Accepted solution]

BPC by design can produce only symmetrical reports (asymmetrical can be achieved by member exclude or just removing lines).

If you have the following selection (member only):

ACCOUNT: Account_group_1, Account1, Account2

Practice: Practice_ALL, Practice1, Practice2

CC: CC_ALL, CC_1, CC_2

You will get all possible combinations.

Then you can delete unnecessary lines (or exclude some member combinations).

Hope it's clear...

Vadim

[Former Member · Accepted solution]

Ok Thanks.

I  will try that.

I got one "crazy" solution for this, I don't know if it would be good idea with

create some kind of formulas like (using one audit trail member "SHOW")

if account.calc is Parent and practice.calc is Parent and CC parent is Parent then 1 (Show)

else if account calc is parent and practice calc is base and cc.calc is base then 0 (Hide)

else if account.calc is base and practice.calc is parent and cc.calc is parent then 0 (Hide)

else if account.calc is base and practice.calc is base and cc.calc is base then 1 *(Show)

....

then filter by 1.

It can work?

[former_member186338 · Accepted solution]

You can't hide row using the formula... Only VBA

[Former Member]

Hi Pheno,

You have two ways to achieve your resulted report.

1) As Vadim mentioned after Creating a report you can use Exclude Members in Edit Report options .

2) And the another way is to use Active Member Recognition in options.

               > Create a report with your first line of Account,Practice,Costcenter members.

               > And manually input the remaining members ID's ( Account,Practice,Costcenter) in your                     excel cell .

I hope these ways would be helpful to you better than going for a VBA.

Aravind

[Former Member]

thanks a lot it worked perfectly.

Only issue i'm having is the costcenter have too many members and it is creating errors

"One or more errors occurred. The execution of report Default Report failed. Please contact your administrator. Exception of type 'System.OutOfMemoryException' was thrown. "

Anytips?

PS. I extended the numbers in FPMXLCLIENT.dll.Config to more.

Dank

[former_member186338]

Sorry, but not clear... Can you provide some details about the issue?

[Former Member]

Hello,

Try Options->User Options->Others and Select Force Symmetric Refresh for Large Assymetric Axis.

Thanks

Prashant

[Former Member]

Prashant is right as he said try that option which will get you out from this error.

Aravind

[Former Member]

I also tried to "Try Options->User Options->Others and Select Force Symmetric Refresh for Large Assymetric Axis." -> Did not worked..

eventually, i did different combination, and it is fine for now.

Thanks.

regards,