Is it possible to perform calculation using dimension property values

[Former Member]

Hi Guys,

I am working on Resource planning (Billable hrs). Here is my scenario

Dimensions want is used it in my calculation :

1) KPI : Billable Hours

2) Employee (Property: Price per hour)

When a manager plan the billable hours would like to see calculation of Revenue . This should be Billable hours * Price per hour

I am not able to put this statement in my Logic script. Any possibility to use dimension property value in script logic i.e., in REC statement. Can you please help me out.

Regards,

BR

[Former Member]

Hi,

How do you propose to calculate in cases where the employees have the same Price per hour?

Wouldn't it be better to have Price as a Statistical Account in your Account Dimension and then do a simple *REC statement to multiply Billable Hours with Price/Hour.

You could have one input form for each of these input, post which the Amount is automatically generated in BPC

Regards,

Rajesh

[former_member186338]

Hi Rajesh,

It's possible to use FOR/NEXT even with the same price per hour:

*SELECT(%PRICEPO%,"[PRICE]",ACCOUNT,"[ID]=...") // %PRICEPO% will contain all values of price, including duplicates.

*XDIM_MEMBERSET ACCOUNT=HOURS //Billable hours

*FOR %PR%=%PRICEPO%

*WHEN ACCOUNT.PRICE

*IS %PR%

   *REC(EXPRESSION=%VALUE%*%PR%, ACCOUNT=REVENUE)

*ENDWHEN

*NEXT

For duplicates the calculations will be performed number of times, but, because we use multiple WHEN/ENDWHEN loops the result will be correct.

But this code will be EXTREMELY SLOW!!!

Vadim

[Former Member]

Hi Rajesh,

I do not understand maintaining price in the account dimension.

Price/Hr is always linked with employees. I created two properties (Group & Price per hr) and in the calculations only checking the group did my final revenue calculation

Reason to maintain price per hr is only master data reference but cannot be used directly in my logic.

Due to limitations I grouped all employees into:

Group          Price /hr

A                    70 USD

B                    80 USD

  C                    90 USD

  D                    100 USD

Regards,

BR

[former_member186338]

Hi,

For this simple case (only 2 variants) it's possible to use property, but any changes will require to change script. And it will require the administration team involvement. This is not a good practice.

The dedicated account is better.

By the way, *COMMIT is useless in your code, at the end of WHEN/ENDWHEN loop autocommit happens.

Vadim

[former_member186338]

Hi,

I do not recommend you to use numeric values in the properties in calculations. It's possible in some cases but will require very slow FOR/NEXT loop.

Use additional members to store the required values instead of properties. The FLD operator is still missing in BPC NW.

B.R. Vadim

P.S. In your case you have to add member PRICE and fill it with price per hour. If it's a permanent value you can put it on some dummy members of all other dimensions.

Message was edited by: Vadim Kalinin P.S. Added

[Former Member]

Hi Vadim,

Instead of using property value (Numeric) in calculations I did it in this way.

Created a property called Group in the Dimension (Employee).

Ex: A, B, C & D . This will be used in my logic script

//Calculate the revenue from the billable hours and price per hour

*WHEN C_EMPLOYEE.GROUP

*IS A

*REC(EXPRESSION = %VALUE% * 50 ,C_KPI = REV)

*ENDWHEN

*WHEN C_EMPLOYEE.GROUP

*IS B

*REC(EXPRESSION = %VALUE%  * 100,C_KPI = REV)

*ENDWHEN

*COMMIT

I think its easy for Admin team to maintain the master data and in the same time don't expect many changed in the price per hour (Employees)

Do you see any other approach.

Regards,

BR