on 05-20-2015 8:53 AM
Hi,
I am looking for logic to calulate the below requirement.
Employee dimension has property NO_DAYS(used to capture no of days employee work in year)
I need to calculate effective FTE
Effective FTE = NO_DAYS/365
Example: NO_DAYS = 182
Effective FTE = 182/365 = 1/2 = 0.50 FTE.
I want to use script logic for calculation.
*WHEN EMPLOYEE
*IS *
*REC(EXPRESSION = %VALUE%*([EMPLOYEE].[NO_DAYS])/365)
*ENDWHEN
I am getting this error
UJK_VALIDATION_EXCEPTION:Member "NO_DAYS" not exist
Hi,
"Employee dimension has property NO_DAYS" - it's a bad idea to use property to store some value. You can't use it in the REC expression directly, only using slow FOR/NEXT!
Simply create some account NO_DAYS and fill it with the required values. Then use this account in script logic calculations.
Vadim
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Vadim,
Thanks for the valuable information.
So we cannot use dimension property values directly in *REC statements calculations.
In my HR scenarios, HR will send master data of employee with No of days employee work in a year.
You have suggested to use No_Days as account(Ex; FTE) and update the values.
So users has to update No of days per employee and update/upload the file?????
Is it not waste of time from User point of view.
Can we write a script to copy values from [EMPLOYEE].[NO_DAYS] to GL account FTE?
Can you advise me how to get around with this?
Regards,
Tej
Hi Tej,
I do not understand the issue - it have to be done only once! And only for single month...
"HR will send master data of employee with No of days employee work in a year" - what is the difference between updating the master data property and storing the single value in the cube???
Vadim
P.S. Forget about property!
Hi Vadim,
You are right, it is done once in a month/Quarter.
Updating HR master file or Storing Value in the cube?
I am planning to upload the HR master file directly into BPC as Employee Dimension.
If the NO_DAYS need to be updated as entry as transnational Cube entry in Account FTE, then user has to do manual work or update account FTE/employee-wise using input schedule.
I have 7000 employees so planning to update in Employee master data as a property. And use this property later in calculation of FTE
Use the Employee Master data property NO_DAYS in *REC to calculate FTE is not possible as you have suggested earlier.
Hence i am checking is there any way, i can update the Account FTE with employee wise using script logic.
Hi Tej,
If your planning to use HR master data flat file to upload as Employee master data. You can use the same file to upload the no. of days as transaction data to an account(NO_OF_DAYS for example).
Just create a new transformation file wherein you will map something like below
EMPLOYEE = *COL(1)
SIGNEDDATA=*COL(2)
ACCOUNT=*NEWCOL(NO_OF_DAYS)
Likewise hardcode the other dimensions using *NEWCOL and you can upload the values easily.
Hope it is helpfull...
Regards,
Priyanka
Hi Vadim,
As suggested, i have updated No of days in Employee master file as Account Dimension 30000000 --> FTE account.
Now when i am using this below logic, system is posting all my entries as ZERO
*XDIM_MEMBERSET CHANGE_LEDGER = CL_159
*XDIM_MEMBERSET LOCATION = LO_NONE
*XDIM_MEMBERSET PROJECT = PJ_NONE
*XDIM_MEMBERSET RPTCURRENCY = LC
*XDIM_MEMBERSET TIME = 2016.INPUT
*XDIM_MEMBERSET VERSION = VE_ACTUAL
*XDIM_MEMBERSET ACCOUNTHR = 10000000
*WHEN COSTCENTRE
*IS <> CC_NONE
*REC(EXPRESSION =(%VALUE%*([ACCOUNTHR].[30000000])/365))
*ENDWHEN
UJKT results are in attachment.
If i use below record statment, system is posting correctly as per logic
*REC(EXPRESSION =(%VALUE%*(182)/365))
So my error is with *REC(EXPRESSION =(%VALUE%*([ACCOUNTHR].[30000000])/365))
Where am i doing mistake now? Do i have to use Select statement and FOR/NEXT statements?
Regards,
Tej
Hi,
I think you are missing select statement to read attribute of Employee.
Thanks,
Raju
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
6 | |
5 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.