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