cancel
Showing results for 
Search instead for 
Did you mean: 

MDX Dimension formula - sum using property

Former Member
0 Kudos

Hi All,

I am trying to add a dimension formula on the account dimension sheet and the goal is to add all the accounts that have a property TEST='Y' into a new account TESTACCOUNT

Here is the formula that I am using but it errors out while processing.

ID----


FORMULA

TESTACCOUNT----


SUM([ACCOUNT].[H1].properties("TEST")="Y"),solve_order=10

Can you please tell me where I am going wrong.

Thanks in advance for your help.

Swaroop

Edited by: SwaroopLalam on Aug 13, 2009 4:18 PM

Edited by: SwaroopLalam on Aug 13, 2009 4:19 PM

Accepted Solutions (0)

Answers (4)

Answers (4)

Former Member
0 Kudos

Hi Rafael,

I thought I used the full qualifier when I said Account.H1.xxxxxx

Can you be more specific on what I should be doing for getting this to work.

Appreciate it.

Swaroop

Edited by: SwaroopLalam on Oct 6, 2009 9:06 AM

Former Member
0 Kudos

I never got this to work.

Swaroop

Former Member
0 Kudos

Thanks, Swaroop. What version are you on? I'm on 7MS SP5.

Former Member
0 Kudos

What I would suggest is adding a GET MEMBERSET statement to the logic, such as

*XDIM_MEMBERSET ACCOUNT=descendants(ACCOUNT.CurrentMember,ACCOUNT.[LEV1],LEAVES) or a variation that pulls all property values

And maybe the statement could try to use single quotes around the Y and bracket the statement.

{ SUM([ACCOUNT.H1.properties("TEST")='Y')],solve_order=10 }

I am sure there is a method to this, we just nee dto find the correct code in MDX.

Keep trying....and I will check some items.

Edited by: Petar Daniel on Aug 14, 2009 3:54 PM

Edited by: Petar Daniel on Aug 14, 2009 3:55 PM

Former Member
0 Kudos

Sure. Please keep me posted.

Thank you!

Swaroop

Former Member
0 Kudos

Try using this as a basis for your process

' sum(FILTER(descendants(ACCOUNT.[NETINCOME],99,leaves),account.properties("FINSTMT")="PL"),measures.periodic),solve_order=15

Thanks to my friends at the SAP EPM NCC team.

Former Member
0 Kudos

Hi Petar,

Thanks for your prompt reply. I tried the following and even that did not work. Am I doing something wrong here? Please see the error I received while processing the dimension;

'sum(FILTER(descendants(ACCOUNT.[TESTACCOUNT],99,leaves),account.properties("TEST")="Y"),measures.periodic),solve_order=15

ERROR:

- [ACCOUNT].[H1].[#TESTACCOUNT] CustomRollupFormula([Account].[Account_H1_ID6].[TESTACCOUNT]) (1, 61) The 'Account' dimension contains more than one hierarchy, therefore the hierarchy must be explicitly specified.

after that I gave the Heirarchy [H1], below;

'sum(FILTER(descendants([ACCOUNT].[H1].[TESTACCOUNT],99,leaves),account.properties("TEST")="Y"),measures.periodic),solve_order=15

ERROR:

- [ACCOUNT].[H1].[#TESTACCOUNT] CustomRollupFormula([Account].[Account_H1_ID6].[TESTACCOUNT]) (1, 65) The 'Account' dimension contains more than one hierarchy, therefore the hierarchy must be explicitly specified.

Thanks,

Swaroop

Edited by: SwaroopLalam on Aug 17, 2009 5:14 PM

Former Member
0 Kudos

Swaroop, was this not the correct syntax? I get the same error trying to use a similar formula.


sum(FILTER(descendants(ACCOUNT.TESTACCOUNT,99,leaves),account.properties("TEST")="Y"),measures.periodic),solve_order=15

Former Member
0 Kudos

Hi Swaroop,

I think you need to fully qualify the mdx expression, that is, if you have 5 hierarchies in account dimension you need to declare the mdx to execute in one of them and at the top level of the other 4.

Regards,

Rafael

Former Member
0 Kudos

I think it would be a much easier task if you just create a script statement, unless you are adding only parent values. The script logic would be easier to use than the dimension calculation. At a minimum, you may want to look through th eMDX library to see if there is a function that will help with this request in MDX. But the fastest method in may opinion is to have a Script statement.

*When account.property

*IS "TEST"

*REC(FACTOR=1, Account='NAmeofaccount'

*endwhen

*commit

But you may need to adjust the scope to make sure this runs on all accounts, so I would suggest a SELECT statement and XDIM_ADDMEMBERSET to the process.

Hope that helps.

Former Member
0 Kudos

Hi Petar,

Thanks for the response.

We do not want to store the sum() value using the REC. Script will require the default logic to be trigered every time the accounts update and so I thought of the dimension formula (MDX) option since it is just additon of accounts based on property. I did try the MDX library for the SUM function but that did not work. All they had was the SUM(ACOUNT1:ACCOUNTn) but I was looking for the property based SUM() of accounts.

Should I be using any other function instead?

Thanks,

Swaroop

Former Member
0 Kudos

Hi Swaroop,

I think you can achieve what you want but using another PARENTH property. You can add a PARENTH2 and then create an account that will become the parent of all the accounts that you grouped using the property you used in your MDX formula. Just input the ID of the parent account you create as PARENTH2 property of those accounts.

Best regards,

Marvin Santos

Former Member
0 Kudos

Hi Marvin,

It is obvious that the hierarchy will do that but we already have 5 hierarchies in our account dimension and are trying not to add anymore due to the system slowness. Moreover we are talking about a handful of accounts with this property, about 20 accounts.

Swaroop