1 Reply Latest reply: Dec 9, 2012 6:43 PM by Dan V RSS

    Hyperion Data is not over write after XWrite

    dhooan
      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