Numeric ID used in Member Formula not working properly

[Former Member]

Hello experts,

I'm working with member formulas and I'm facing the following issue. I've a calculated account member (Account ID: 520000_001) and I need that member to has the same value of account 110505. I'm writing in 520000_001 FORMULAH2 property the ID "110505" (without quotes), and it's putting me in the reports in the account 520000_001 the value 110.505, and not the value of the 110505 account.

I've tried with the following syntax in the FORMULAH2 property:

110505 (Put the numeric value) - I know this syntax work because I'm using it with other alphanumeric ID's (ie. BG52500)

'110505 (Excel syntax for specifying an explicit text - Put the numeric value)

[110505] (Returns nothing)

[Cuenta].[110505] (Returns nothing)

[Cuenta].[H1].[110505] (Returns nothing)

The 110505 account has value, and i'm querying in the correct data region. We've 2 hierarchies, the account belongs to both ( I mean, it's present in a node in PARENTH1 and another node in PARENTH2).

Any clue?

Thanks in advance!

[Former Member · Accepted solution]

Hi Andres,

what do you mean by "I've a calculated account member (Account ID: 520000_001) and I need that member to has the same value of account 110505"? Is 520000_001 calculated as a parent in either of your two hierarchies? Or should it be calculated with the formula?

How is 520000_001 used in the two hierarchies?

BR,
Arnold

[Former Member · Accepted solution]

Hi Arnold,

Let me explain it better.

I have the "110505" account (base member), which is used in both PARENTH1 and PARENTH2 hierarchies.

The 520000_001 is a base member too, only used in PARENTH2 hierarchy. In this member, i need to put in FORMULAH2 property the opperation (i.e. 110505+110510 or just 110505), but it doesn't work, it sums both values or put "110505" as a number when I do a query. If I do the same for example with G110505, it works, it brings the G110505 value.

Any suggestion? Thanks for your response

[Former Member · Accepted solution]

Hi Andres,

I have tried to create a little test in my BPC.

I have set up accounts 12345, 23456, 34567, 45678 and 56789. 12345 is a base account and I set it up to be added to 23456 in H1 and 34567 in H2. 45678 is also a base account and I set it to add into 56789 in H2.

For 45678 I then set FORMULAH2 as [12345] and I got the same result you got. I then changed account 12345 so it was only used in H1 and suddenly the formula worked.

So I don't think it is to do with the ID being numeric but more with your account 110505 being used in both hierarchies.

My first thought would be to use a script to copy the amount on account 12345 to some other account which would only be used in H2 and in the formula you are trying to create. Instead of a script you could also use the account based calculation business rule for this or use a script instead of the formula.

BR,

Arnold

[Former Member · Accepted solution]

Hi Arnold,

Yes, I'm doing something like that, I've set a new property CUENTA_FCI, and created a mirror member FC110505 (only used in H2). So:

Member: 110505  CUENTA_FCI Val: FC110505

Then in a SL i just do *REC(Cuenta=CUENTA_FCI) with 110505 in the scope. After that, I would be able to use formulas with no issue (as i've been doing with the alphanumeric members).

A last question, is this sytnax incorrect for FORMULAH2? [Cuenta].[H2].[110505] ?

Thanks!

[Former Member · Accepted solution]

Hi Andres,

In my account dimension I have always just used [account] as the syntax and never specifically mentioned any hierarchy. I believe theoretically  [110505] should work as well as [Cuenta].[110505] as well as [Cuenta].[H2].[110505].

BR,

Arnold

[former_member186498]

Hi Andres,

yes the cause is that your account 110505 it's just a number, dimension member sheet it's a normal excel worksheet so he isn't able to understand that you mean the account ID and not the value, this because in the formula it's possible to give a formula that contains also values.

I think the possible solutions is to rename the accont transforming it in a string, e.g. 110505_0 or A110505.

Another possibility instead of a formula is to set 520000_001 as a parent of 110505 but in this case I think you will have also future issues in the reports when you're using 110505 account.

Watch out also using E letter, e.g. 110505E2 excel will use this as a number with scientific notation.

Remember in all dimensions it's better never use numeric ID but a string with character inside.

Regards

     Roberto

[Former Member]

Hello Roberto,

Thanks for your response. Is very strange, I mean, this is an account planning system, so it's just normal to use account codes as ID's for modeling...

The parenth aproach isn't possible in this case, since the account would be used in many member formulas, so I would need a PARENTHN for each case.

Any other suggestion?

Thanks