Financial Management Blogs by Members
Dive into a treasure trove of SAP financial management wisdom shared by a vibrant community of bloggers. Submit a blog post of your own to share knowledge.
cancel
Showing results for 
Search instead for 
Did you mean: 
former_member186338
Active Contributor
4,985


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

Labels in this area