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:
DATA ENTRY:
*** Price per adv page
PriceD
PriceC
PriceJ
*** Number of adv pages
PagesD
PagesC
PajesJ
*** Planned discount
DiscountD
DiscountC
DiscountJ
DEFAULT.LGF SCRIPT CALCULATED:
***Gross Sales
GrossSalesD
GrossSalesC
GrossSalesJ
*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
NetSalesD
NetSalesC
NetSalesJ
*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:
PagesD
PagesC
PajesJ
GrossSalesD
GrossSalesC
GrossSalesJ
NetSalesD
NetSalesC
NetSalesJ
DiscountCalcD
DiscountCalcC
DiscountCalcJ
PriceCalcD
PriceCalcC
PriceCalcJ
B.R. Vadim