This discussion is archived
1 Reply Latest reply: Dec 9, 2012 4:43 PM by DanV RSS

Hyperion Data is not over write after XWrite

dhooan Newbie
Currently Being Moderated
We have a major issue with workforce data “Xwrite” to PnL. User move all employees from a LE/SBU/Dept to another LE/SBU/Dept in Workforce; the data is “xwrite” to New LE/SBU/Dep in PnL, but the data in PnL for Old LE/SBU/Dept is not cleared out. Please see below Calc Script, I know script is kind of big, any help would be great.


//ESS_LOCALE English_UnitedStates.Latin1@Binary
SET UPDATECALC OFF;
SET AGGMISSG ON;
SET CLEARUPDATESTATUS OFF;
SET LOCKBLOCK HIGH;
SET CACHE HIGH;

FIX("Budget","FY13","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec","HSP_InputValue","Working",@LEVMBRS("Account",0),@LEVMBRS("Entity",0),@LEVMBRS("Employee",0),@LEVMBRS("SBU",0),@LEVMBRS("Department",0))
DATACOPY "Local" TO "USD";
ENDFIX ;

FIX("Working",@LEVMBRS("Compensation Assumptions",0),@LEVMBRS("Employee Expenses",0),@LEVMBRS("Entity",0),@LEVMBRS("Employee",0),@LEVMBRS("SBU",0),@LEVMBRS("Department",0))

FIX("Budget","FY13","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

"HSP_InputValue" (

@CALCMODE(BOTTOMUP);
@CALCMODE(BLOCK);

IF (@ISMBR("Local"))
"Local";
ELSEIF (("Local"->"HSP_InputCurrency" == #MISSING) OR ("Local"->"HSP_InputCurrency" == 0 ))
IF(@ISUDA("Entity","USD"))
"Local";
ELSEIF (@ISUDA("Entity","ARS"))
"Local" * "Version"->"Currency"->"Entity"->"Employee"->"SBU"->"Department"->"HSP_Rate_ARS" / "Version"->"Currency"->"Entity"->"Employee"->"SBU"->"Department"->"HSP_Rate_USD";
ELSEIF (@ISUDA("Entity","CAD"))
"Local" * "Version"->"Currency"->"Entity"->"Employee"->"SBU"->"Department"->"HSP_Rate_CAD" / "Version"->"Currency"->"Entity"->"Employee"->"SBU"->"Department"->"HSP_Rate_USD";
ELSEIF (@ISUDA("Entity","DKK"))
"Local" * "Version"->"Currency"->"Entity"->"Employee"->"SBU"->"Department"->"HSP_Rate_DKK" / "Version"->"Currency"->"Entity"->"Employee"->"SBU"->"Department"->"HSP_Rate_USD";
ELSEIF (@ISUDA("Entity","EUR"))
"Local" * "Version"->"Currency"->"Entity"->"Employee"->"SBU"->"Department"->"HSP_Rate_EUR" / "Version"->"Currency"->"Entity"->"Employee"->"SBU"->"Department"->"HSP_Rate_USD";
ELSEIF (@ISUDA("Entity","GBP"))
"Local" * "Version"->"Currency"->"Entity"->"Employee"->"SBU"->"Department"->"HSP_Rate_GBP" / "Version"->"Currency"->"Entity"->"Employee"->"SBU"->"Department"->"HSP_Rate_USD";
ELSE
#MISSING;
ENDIF;
ELSEIF ("Local"->"HSP_InputCurrency" == 1000)
"Local";
ELSEIF ("Local"->"HSP_InputCurrency" == 59134)
"Local" * "Version"->"Currency"->"Entity"->"Employee"->"SBU"->"Department"->"HSP_Rate_ARS" / "Version"->"Currency"->"Entity"->"Employee"->"SBU"->"Department"->"HSP_Rate_USD";
ELSEIF ("Local"->"HSP_InputCurrency" == 59135)
"Local" * "Version"->"Currency"->"Entity"->"Employee"->"SBU"->"Department"->"HSP_Rate_CAD" / "Version"->"Currency"->"Entity"->"Employee"->"SBU"->"Department"->"HSP_Rate_USD";
ELSEIF ("Local"->"HSP_InputCurrency" == 59136)
"Local" * "Version"->"Currency"->"Entity"->"Employee"->"SBU"->"Department"->"HSP_Rate_DKK" / "Version"->"Currency"->"Entity"->"Employee"->"SBU"->"Department"->"HSP_Rate_USD";
ELSEIF ("Local"->"HSP_InputCurrency" == 59137)
"Local" * "Version"->"Currency"->"Entity"->"Employee"->"SBU"->"Department"->"HSP_Rate_EUR" / "Version"->"Currency"->"Entity"->"Employee"->"SBU"->"Department"->"HSP_Rate_USD";
ELSEIF ("Local"->"HSP_InputCurrency" == 88938)
"Local" * "Version"->"Currency"->"Entity"->"Employee"->"SBU"->"Department"->"HSP_Rate_GBP" / "Version"->"Currency"->"Entity"->"Employee"->"SBU"->"Department"->"HSP_Rate_USD";
ELSE
#MISSING;
ENDIF;

);

ENDFIX ;

ENDFIX ;



/*Aggregate all #Missing at level 0 of Working Budget DCD 09/11/2012 */


FIX (&NextYear,"WORKING","BUDGET","HSP_InputValue","Local","USD")

CALC DIM ("Account","Entity","SBU","Department","Employee","Period");

ENDFIX;

FIX("Budget","FY13","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec","HSP_InputValue","Working","Merit Adjustment %",@IDESCENDANTS("Total Headcount",0),@IDESCENDANTS("Period",0),@IDESCENDANTS("Entity",0),@IDESCENDANTS("Department",0),@IDESCENDANTS("SBU",0),"Existing Employees","New Employees")
DATACOPY "Local" TO "USD";
ENDFIX ;



FIX("Budget","FY13","HSP_InputValue","Working","Local","USD","Existing Employees",@IDESCENDANTS("Entity",0),@IDESCENDANTS("Department",0),@IDESCENDANTS("SBU",0))

"Regular Headcount" (

@XWRITE("Regular Headcount", PnlCube, "Existing Employee Headcount","000");

)
ENDFIX

FIX("Budget","FY13","HSP_InputValue","Working","Local","USD","New Employees",@IDESCENDANTS("Entity",0),@IDESCENDANTS("Department",0),@IDESCENDANTS("SBU",0))
"TBH Headcount" (

@XWRITE("TBH Headcount", PnlCube, "New Employee Headcount","000");

)

ENDFIX


FIX("Budget","FY13","HSP_InputValue","Working","Local","USD","Existing Employees",@IDESCENDANTS("Entity",0),@IDESCENDANTS("Department",0),@IDESCENDANTS("SBU",0))

"Adjusted Employee Salary" (

IF ("Regular Headcount" <> 0)

@XWRITE("Adjusted Employee Salary", PnlCube, "611010","000");
ENDIF
)

ENDFIX

FIX("Budget","FY13","HSP_InputValue","Working","Local","USD","New Employees",@IDESCENDANTS("Entity",0),@IDESCENDANTS("Department",0),@IDESCENDANTS("SBU",0))

"Adjusted Employee Salary" (

@XWRITE("Adjusted Employee Salary", PnlCube, "611020","000");

)

ENDFIX

Thanks

Edited by: dhooan on Nov 29, 2012 2:38 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points