cancel
Showing results for 
Search instead for 
Did you mean: 

Issue with Member Formula across alternate hierarchies

Former Member
0 Kudos

Hi All,

We are facing an issue with ratio calculation in one of our BPC 10 NW implementations.

The formula is as follows:

[(Account 1) , (Current Product in the Nested Row Axis)]*1000 / [(Account 2), ( All Products excl. C)]

Product Dimension (PARENTH1) is as follows:

ALL PRODUCTS

     - A

     - B

     - C

     - D

     - E

To achieve All Products excluding C, we have an alternate hierarchy (PARENTH2) as follows:

ALL PRODUCTS EXCL. C

     -     A

     -     B

     -     D

     -     E

So the Member Formula is as follows:

((([ACCOUNT].[ACCOUNT1],[PRODUCT].CurrentMember)*1000)/([ACCOUNT].[ACCOUNT2],[PRODUCT].[PARENTH2].[]TOTAL_EXCL_C))

This formula however seems to behave very oddly. When used as a part of a report with various other formulas, it just comes empty. However, if I go to intersection of this formula and select 'Refresh Selected Data' it gives the correct value.

Is it because the system gets confused when 2 alternate hierarchies are used with the same formula in a nest?

Another odd sighting is that, if the Product dim member in the 1st row of the report is ALL PRODUCTS EXCL C, it gives the correct value, however all the other rows are empty which further makes me think, the system is mixing up the parents in the formula.

Has anyone else faced a similar situation?

Could someone please help us with this issue.

Thanks!

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member186338
Active Contributor
0 Kudos

Hi Rajesh,

First of all I see syntax error in the provided formula:

((([ACCOUNT].[ACCOUNT1],[PRODUCT].CurrentMember)*1000)/([ACCOUNT].[ACCOUNT2],[PRODUCT].[PARENTH2].[]TOTAL_EXCL_C))

And also, try to remove PARENTH2 reference (and ACCOUNT - not required):

(([ACCOUNT1]*1000)/([ACCOUNT2],[PRODUCT].[TOTAL_EXCL_C]))

B.R. Vadim

Former Member
0 Kudos

Oops sorry, typing error. I generally keep the brackets ready and type the members within it. Messed it this time round!

I have tried the approach you have mentioned as well. Infact that's how my formula looked when I built it at first. Then when it didn't work, I started expanding it, to include the [DIMENSION] and the [PARENT]

former_member186338
Active Contributor
0 Kudos

It's a different story - you have to explicitly mention any [DIMENSION] prefix except for account type dimension. [PRODUCT].CurrentMember - is useless.

Vadim

former_member186338
Active Contributor
0 Kudos

Just tested the same sample formula:

[202020202]/([PAGESTOT],[TITLES].[2MAGAZINES]);SOLVE_ORDER=10

where:

202020202 - account Net Sales

PAGESTOT - account total pages

[TITLES].[2MAGAZINES] - user defined dimension TITLES, 2MAGAZINES - parent member in hierarchy PARENTH2

Everything working correctly.

B.R. Vadim

Former Member
0 Kudos

Was that the only row in your report? Could you also follow it by a few other member formulas (perhaps the same account with a different product?)

In my case too, the formula works fine if it is the only row in the report or if I refresh that particular intersection only using 'Refresh Selected Data'.

Thanks!

former_member186338
Active Contributor
0 Kudos

No, number of rows:

Correct results for base members of TITLES and 2MAGAZINES (PARENTH2 of TITLES) - yellow, but incorrect for parent members of PARENTH1 TITLES - red.

Vadim