Simple Dimension Formula

[Former Member]

Hi All,

I am trying to divide two accounts where AccountB is from prior year end

AccountA

AccountB

KPI = ([Accounts].[AccountA]/([Accounts].[AccountB],[Time].Currentmember.Properties('BEGIN')))

However this fails to validate during processing at expanding formulas and indicates incorrect syntax (actually It never gets to OLAP processing)

Please let me know if anyone has any ideas.

[james_lim · Accepted solution]

Hiren,

You should specify Hierarchy information even though it has 1 hierarchy,

By default, H1 is the hierarchy name.

Please try below things.

KPI = (Accounts.H1.AccountA/(Accounts.H1.AccountB,Time.H1.Currentmember.Properties('BEGIN')))

Thanks.

James Lim.

[Former Member · Accepted solution]

Hi James,

Thanks for the help, I used this exact syntax:

([ACCOUNT.H1].[ACCOUNTA]/([ACCOUNT.H1].[ACCOUNTB],[TIME.H1].currentmember.properties('BEGIN')))

But i get an invalid syntax error: - Incorrect syntax near '))'.

So i thought of temporarily simplying the syntax to just ACCOUNTB of Prior year:

([ACCOUNT.H1].[ACCOUNTB],[TIME.H1].currentmember.properties('BEGIN'))

But it still comes up with the same error: - Incorrect syntax near '))'.

So i removed the brackets:

[ACCOUNT.H1].[ACCOUNTB],[TIME.H1].currentmember.properties('BEGIN')

Now it comes up with error: - Incorrect syntax near ')'.

All errors are whene expanding formulas and not from the processing olap step.

Hiren.

Edited by: Hiren Amin on Jul 27, 2011 5:15 PM

[james_lim · Accepted solution]

Hiren,

Please try to use double quotation for the property.

In addition, does [TIME.H1].currentmember.properties("BEGIN") has same value of time member id?

If it doesn't have same time member id, it will not return proper value even though it process dimension formula.

so please check it again.

Regards,

James Lim

[Former Member]

Hi James,

We tried the following Code:

[Accounts.H1].[AccountB],[TIME.H1].currentmember.properties("BEGIN")

This time the error we got was at the step OLAP processing formulas:

- [Accounts].[#AccountB] CustomRollupFormula([Accounts].[Accounts_ID1].[AccountB]) (1, 27) Parser: The syntax for ',' is incorrect.

Edited by: Hiren Amin on Jul 27, 2011 6:02 PM

[james_lim]

Hiren,

Dimension formula will return a result based on the cell value.

Therefore, you should specify it correctly.

([Accounts.H1].[AccountB],[TIME.H1].currentmember.properties("BEGIN"))

Above formula will return a cell value of AccountB and other CV with Time.H1.currentmember.properties("BEGIN")

If you are missing parentheis ( ), it can't parse it properly.

That's why it return error.

Please try it again.

In addtion, I am not sure what the value of the Property Begin but if it is a string, you need to use STRTOMEM function because it will return string not memberid but that function will decrease performance.

Anyway, please try it again.

Regards,

James Lim

[Former Member]

Hi,

The STRTOMEMBER worked, however you are correct, our begin property does not resolve to OLAP members when at year/quarter level.

I thought of using IIF to only execute on Months, but thought is there any MDX syntax that we can use to look up the last period from the prior year?

Thanks.

Hiren