Hi,
in my Data Action I want to sum up around 20 different accounts, lets say X01, X02, ... X20 on level of company code and profit center.
The accounts are not belonging to specific hierarchy nodes. I don´t want to create a new dimension attribute just only for being used to set the filter for summing up.
The values of the accounts are on different granularity, e.g.
The sum should be stored in Account Z.
What is the most perfomant (and easiest) way to achive this?
The current solution I have is like this
VARIABLEMEMBER #COST_A OF [d/GLACCOUNT]
VARIABLEMEMBER #COST_B OF [d/GLACCOUNT]
VARIABLEMEMBER #COST_C OF [d/GLACCOUNT]
VARIABLEMEMBER #COST_D OF [d/GLACCOUNT]
DATA([d/GLACCOUNT] = #COST_A,[d/PRODUCT]="#",[d/SALESOFFICE]="#")=
RESULTLOOKUP([d/GLACCOUNT] = "X01",[d/PRODUCT]="#",[d/SALESOFFICE]="#")+
RESULTLOOKUP([d/GLACCOUNT] = "X02",[d/PRODUCT]="#",[d/SALESOFFICE]="#")+
...
RESULTLOOKUP([d/GLACCOUNT] = "X05",[d/PRODUCT]="#",[d/SALESOFFICE]="#")
DATA([d/GLACCOUNT] = #COST_B,[d/PRODUCT]="#",[d/SALESOFFICE]="#")=
RESULTLOOKUP([d/GLACCOUNT] = "X06",[d/SALESOFFICE]="#")+
...
RESULTLOOKUP([d/GLACCOUNT] = "X10",[d/SALESOFFICE]="#")
DATA([d/GLACCOUNT] = #COST_C,[d/PRODUCT]="#",[d/SALESOFFICE]="#")=
RESULTLOOKUP([d/GLACCOUNT] = "X06",[d/PRODUCT]="#")+
...
RESULTLOOKUP([d/GLACCOUNT] = "X10",[d/PRODUCT]="#")
DATA([d/GLACCOUNT] = #COST_D,[d/PRODUCT]="#",[d/SALESOFFICE]="#")=
RESULTLOOKUP([d/GLACCOUNT] = "X06")+
...
RESULTLOOKUP([d/GLACCOUNT] = "X10")
DATA([d/GLACCOUNT] = "Z",[d/PRODUCT]="#",[d/SALESOFFICE]="#")=
RESULTLOOKUP([d/GLACCOUNT] = #PRODCOST_A) +
RESULTLOOKUP([d/GLACCOUNT] = #PRODCOST_B) +
RESULTLOOKUP([d/GLACCOUNT] = #PRODCOST_C) +
RESULTLOOKUP([d/GLACCOUNT] = #PRODCOST_D)
Is this really performant? Is there an easier way?
Thanks - Thomas
PS: I´m missing an option to specify more than one value in the dimensionfilter in the RESULTLOOKUP function.