Formula Account

[Former Member]

Hello everyone,

I am working with BPC 7.0 NW.

I need to use the attribute formula in the dimension accounts.

What I get is this:

ACCOUNTA = ACCOUNT1 + ACCOUNT2

ACCOUNTB = ACCOUNT3+ ACCOUNT4

ACCOUNTC = ACCOUNTA - ACCOUNTB

ACCOUNTA AND ACCOUNTB are correct.

ACCOUNTC usually is 0 or the value of accounts.

I tried to use the attribute solveorder but the result does not change.

If I use ACCOUNTC = (ACCOUNT1 + ACCOUNT2) - (ACCOUNT3+ ACCOUNT4) get the correct result but I have formulas that exceed 255 characters.

Any ideas?

Thank you very much

Simone

[esjewett]

Simone,

You cannot have a dimension member formula that references a member that is calculated itself by a member formula. So, your formula for ACCOUNTC will not work as you have it set up.

You have a few options:

1. Write out the entire formula. This works for short formulas. There is a way to get around the limitation on length by using an MDX library as Nilanjan points out. I don't believe you can have a function that takes arguments in the NW version (possible this has been fixed), but you can hard-code the accounts to be added up.

One additional step that you must do is to define the syslib file in the second tab of your dimension member sheet. You will have a line that reads something like *SYSLIB(mdxfilename.lgf). Frankly I forget if it is *SYSLIB or *INCLUDE, so you may need to try them both. You can then put "functionname()" into your dimension member formula and it will run the calculation in the function. Maybe someone else can weigh in with the exact syntax. For some reason I seem to remember that Dries is the expert on this, so maybe this will get his attention

2. Pre-calculate the lower-level values. You could use either script logic or account transformation business rules to pre-calculate accounts ACCOUNTA and ACCOUNTB. These pre-calculations could either be run in batch (preferably) or in your DEFAULT.LGF file, which would recalculate whenever data was updated.

3. Use a hierarchy. If all of the accounts needed in the formula for ACCOUNTC are additive, then you could use a hierarchy to sum them up (PARENTH1 or a secondary hierarchy). Hierarchy nodes can be used in dimension member formulas, so if you did this your current ACCOUNTC formula would become valid.

If possible, I recommend going with the second or third approaches, as the MDX logic library approach can be confusing to administrators unless you plan to use it extensively or you are confident that you can document it clearly and in a way that is easily found and referenced in the future. It is easy to forget where these functions are maintained.

Ethan

[Former Member]

Hello and thanks for your reply.

I tried to use solveorder but absolutely can not get it to work.

The report on the first refresh calculated part of the accounts are correct. If I do another refresh the calculated change and become all wrong. is it possible?

Now I try your second solution.

Thank you very much

[Former Member]

Hi Simone,

Please try the second option. I have used this in lot of scenarios.

Regarding the solveorder, can you please let me know how you have written the formulas for the accounts?

[Former Member]

Hi Simone,

This should have been done using the solveorder. You should have Account A and B with higher solveorder than Account C. Then A and B will be calculated before B.

If you want to go with the second method. Then you can download the file MdxLib.lgl from UJFS T Code. It will be available under systemlibrary -> logic library.

Add the below lines at the end of the MdxLib.lgl:

*Function MyFunc(%Arg1%,%Arg2%,%Arg3%,%Arg4%)
	[Account].[%Arg1%] + [Account].[%Arg2%] - [Account].[%Arg3%] + [Account].[%Arg4%]
*endfunction

After adding this, upload the same document to the same location.

Then, in the member sheet, for account C, you should write:

MyFunc(Account1, Account2, Account3, Account4)

Hope this helps.