on 08-13-2009 3:17 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I never got this to work.
Swaroop
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
User | Count |
---|---|
5 | |
5 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.