There are a lot of cases when the user have to enter some coefficients and use script logic to calculate finance figures based on this coefficients. The examples of coefficients are: prices, discount, tax percents and other. The coefficients are entered on the base member level but in reports we need to show average values of the coefficients on the node level. For example: average price per quarter, average discount per product group…
There is no simple way to achieve this goal, BPC will simply sum the base member values of the coefficients and show this sum as a node value – absolutely useless.
My proposal is to define additional member for each coefficient member and use dimension formula for the additional member to calculate coefficient from summable members.
Below you can see a simplified real life example:
In our system we are planning advertisement sales for different titles in different legal entities and of different types in a separate cube.
We have 3 types of adv sales: Display, Classified and JobOpps. For types we don’t use separate dimension, we have members dedicated for each type (with the corresponding P&L lines in the main financial planning cube).
Members of the ACCOUNT dimension:
*** Price per adv page
*** Number of adv pages
*** Planned discount
DEFAULT.LGF SCRIPT CALCULATED:
*REC(EXPRESSION=[ACCOUNT].[PriceD]*[ACCOUNT].[PagesD],ACCOUNT=GrossSalesD) *REC(EXPRESSION=[ACCOUNT].[PriceC]*[ACCOUNT].[PagesC],ACCOUNT=GrossSalesC) *REC(EXPRESSION=[ACCOUNT].[PriceJ]*[ACCOUNT].[PagesJ],ACCOUNT=GrossSalesJ)
*** Net Sales
*REC(EXPRESSION=[ACCOUNT].[PriceD]*[ACCOUNT].[PagesD]*(1-[ACCOUNT].[DiscountD]),ACCOUNT=NetSalesD) *REC(EXPRESSION=[ACCOUNT].[PriceC]*[ACCOUNT].[PagesC]*(1-[ACCOUNT].[DiscountC]),ACCOUNT=NetSalesC) *REC(EXPRESSION=[ACCOUNT].[PriceJ]*[ACCOUNT].[PagesJ]*(1-[ACCOUNT].[DiscountJ]),ACCOUNT=NetSalesJ)
DIMENSION FORMULA MEMBERS:
*** Calculated discount to be used in reports – with average in nodes
DiscountCalcD = IIF([NetSalesD]=0,NULL,([GrosSalesD]-[NetSalesD])/[NetSalesD]) DiscountCalcC = IIF([NetSalesC]=0,NULL,([GrosSalesC]-[NetSalesC])/[NetSalesC]) DiscountCalcJ = IIF([NetSalesD]=0,NULL,([GrosSalesJ]-[NetSalesJ])/[NetSalesJ])
*** Calculated price to be used in reports – with average in nodes
PriceCalcD = IIF([PagesD]=0,NULL,[GrosSalesD]/[PagesD]) PriceCalcC = IIF([PagesC]=0,NULL,[GrosSalesC]/[PagesC]) PriceCalcJ = IIF([PagesJ]=0,NULL,[GrosSalesJ]/[PagesJ])
In the different reports we are able to see correct base and node figures (TIME nodes, TITLES nodes, ENTITY nodes) with all standard MEASURES (PERIODIC, YTD, QTD) for: