Skip to Content
0
Jun 26, 2009 at 02:02 AM

Referencing annual prices for monthly units * price calculations

48 Views

Hello,

We have a PLANNING application that has the following dimensions.

Z_Account,Z_Time,Z_Company,Z_Version,Z_Driver,Z_DataSrc,Z_Driver,Z_Produ

ct,Z_Customer. We have plan sales quantities by product and customer for

every month in the year against an account called 'SalesUnits'

(combination is Z_Account = SalesUnits, Z_Time = 2009.JAN to 2009.DEC,

Z_Driver = None,Z_Customers = various customers, Z_Product = various

products, Z_Version = Forecast, Z_DataSrc = Manual, Z_Company = various

companies). However, sales prices are planned for and entered on an

annual level.

For this we store the sales prices for the combination

Z_Account = "DriverAccount", Z_Time = 2009.INP, Z_Driver= "SalesPrice",

Z_Customer = NoCustomer, Z_Product= various products,Z_Company = various

companies,Z_Version = Forecast, Z_DataSrc= Manual. The requirement is to

write script logic that multiplies sales quantities of each

product/customer/ month by the price of the product/year and write the

values to another account (SalesRevenue) for each product/customer/month

combination.

How would one go about writing the script logic for above?

We tried the following ways. However, we get errors either during

validation or during execution of the script.

Try 1: In this case we created a property called 'Input_Year' in the

time dimension and stored the value 2009.INP as property for all the

months 2009.JAN to 2009.DEC

*XDIM_MEMBERSET Z_Account = SalesUnits,DriverAccount,SalesRevenue
*XDIM_MEMBERSET Z_Product = AllProducts
*XDIM_MEMBERSET Z_Customer = AllCustomers
*XDIM_MEMBERSET Z_DataSrc = Manual
*XDIM_MEMBERSET Z_Drivers =ALL_ACT
*XDIM_MEMBERSET Z_RptCurrency = LC
*XDIM_MEMBERSET MEASURES = PERIODIC
*function PRO(%DIMENSIONNAME%,%PROPERTYNAME%)
%DIMENSIONNAME%.Properties("%PROPERTYNAME%")
*endfunction
#INPUTYEAR=PRO(Z_Time,INPUT_YEAR)
*WHEN Z_ACCOUNT
*IS "SalesUnits"
*REC(Expression = %Value% * 
([Z_Account].[DriverAccount],[Z_Drivers].[SalesPrice],[Z_Time].[#INPUTYE 
AR],[Z_Customer].[NoCustomer]),Z_Account = "SalesRevenue") 

*ENDWHEN
*COMMIT

The above code could not be validated. Is there any way to include a

variable as dimension member in a tuple in a REC statement? NB: The code

works if we hard code [Z_Time].[2009.INP] in the tuple. However, we want

to avoid hard coding of years, months and dimension items that would

change frequently.

-->continued in reply due to characters limit