I have a rule query in Architect that is probably not complicated but I cannot work it out.
I have a cube 'Revenue Staging by Flight'
with dims:
Time by Day
Revenue X3 Account Code
Revenue X3 Cost Centre
Aircraft Reg
Flights
Airports
Departing & Arriving
Revenue Detail
I have a cube 'RP Financial Reporting by Flight'
with dims:
Plan Version
Time by Day
RP Cost Group
Aircraft Reg
Airports
Flights
Departing & Arriving
RP by Flight Measures
I have 3 Mapping cubes:
RP X3 Account Code Mapping
with Dims:
X3 Account Code
RP X3 Account Code Mapping
RP X3 Cost Centre Mapping
with Dims:
X3 Cost Centre
RP X3 Cost Centre Mapping
RP Cost Group Mapping
with Dims:
RP X3 Account Code Group
RP X3 Cost Centre Group
RP Cost Group Mapping
In order to narrow down our account codes and cost centres, each account code is assigned to an account code group, each cost centre is assigned to a cost centre group and each account code group and cost centre group is assigned to a 'Cost Group' effectively narrowing down 30,000 combinations into 20 Cost Groups.
I am trying to create a rule to move the data from the Revenue Staging by Flight cube into the RP Reporting by Flight cube and mapping each combination into its cost group.
So far the rule in the RP Financial Reporting by Flight cube is:
Code: Select all
#Revenue
['Actual','RPFF01']=N: IF(ATTRS('RP Cost Group',!RP Cost Group,'Name') @=DB('RP Cost Group Mapping','RPA025','RPC003','RPG001'),
DB('Revenue Staging by Flight',!Time by Day,'Total Revenue X3 AC','Total Revenue X3 CC',!Aircraft Reg,!Flights,!Airports,!Departing & Arriving,'Total Revenue'),0);
I believe from what I have worked out is that you cannot cycle through dimensions that don't exist in the cube that the rules are in (which may be obvious). I have looked through each rule function and the TM1 rules guide but could not find a solution. I have attached screenshots of the mapping cubes.
I have two questions:
1) Is it possible to make the code dynamic so that it will cycle through all the Account Code and Cost Centre groupings?
2) How can I then refer to those Account Code and Cost Centre groupings when pulling the data from the Revenue Staging by Flight cube?
Any help is greatly appreciated, thanks.