I have two cubes, 'accountParameters' and 'accountGroupParameters', which solely exist to store some parameters (obviously) in a calculation model.
The dimensions are as follows:
accountParameters: company, year, month, version, currency, accountParams, accounts
accountGroupParameters: company, year, month, version, currency, accountGroupParams, accountGroups
The cubes share the first five dimensions, the other two, handling either the accounts themselves or their respective groups, naturally differ from each other.
The 'accounts' dimension contains 102 elements.
The only elements in the 'accountParams' dimension that are interesting for this problem are 'Value' and 'AccountGroup'.
The 'accountGroups' dimension corresponds with the entries in the 'accounts' cube, i.e. it contains the elements 'AccountGroup01', … 'AccountGroup20'. Furthermore, there's a text attribute that stores the account group's clear name called 'GroupName'.
Once again, the only element of the 'accountGroupParams' that matters right now, is 'CumulatedValue'.
The idea is to cumulate the values from all accounts belonging to an account group in the 'accountGroupParameters' cube.
My current rule on the 'accountGroupParameters' cube states:
Code: Select all
['CumulatedValue'] = N: If(
DB( 'accountParameters', !company, !year, !month, !version, !currency, 'AccountGroup', 'Account001' ) @= ATTRS( 'accountGroups', !accountGroups, 'GroupName' ),
DB( 'accountParameters', !company, !year, !month, !version, !currency, 'Value', 'Account001' ),
0
) +
… +
If(
DB( 'accounts', !company, !year, !month, !version, !currency, 'AccountGroup', 'Account102‘ ) @= ATTRS( 'accountGroups', !accountGroups, 'AccountName' ),
DB( 'accounts', !company, !year, !month, !version, !currency, 'Value', 'Account102' ),
0
);
If there's additional info you need or something is unclear (I'm not that good at explaining stuff...), please ask.
I'm looking forward to your replies!
Cheers,
Jan