5 Replies Latest reply: Jun 20, 2012 8:42 AM by RobertR3 RSS

    Essbase Calc Script

    704787
      Hi All,
      I am writing a calc script to calculate the values of some members in a particular dimension based on the smart lists. However, the script runs fine as far as the calculation is concerned but the new blocks are not created on the running of script. But as soon a I enter 1 or 0 and forcefully create the blocks the script gives the exact expected results.
      I have tried using SET CREATEBLOCKONEQ ON command but with no hope. Any Help is appreciate. Thanks in advance.

      Here is the script. The accounts and period is dense and rest all is sparse..
      SET CREATEBLOCKONEQ ON;
      FIX(&BudYear,@IDESCENDANTS("Salary Cost Center"), USD,"No Product",@idescendants(Position), @IDESCENDANTS("To be hired"))
      Jan(
      IF (@ISMBR ("Staff costs - Cost of sales") AND (Sales -> "Description" -> "Start Month" == 1))
      "Jan"= "Jan"->"No Entity"->"No Items"-> "No Account" * Sales -> "Description" -> "number of employees" ;
      "Feb"="Feb"->"No Entity"->"No Items"-> "No Account" * Sales -> "Description" -> "number of employees" ;
      "Mar"="Mar"->"No Entity"->"No Items"-> "No Account" * Sales -> "Description" -> "number of employees";
      "Apr"="Apr"->"No Entity"->"No Items"-> "No Account" * Sales -> "Description" -> "number of employees";
      "May"="May"->"No Entity"->"No Items"-> "No Account" * Sales -> "Description" -> "number of employees";
      "Jun"="Jun"->"No Entity"->"No Items"-> "No Account" * Sales -> "Description" -> "number of employees";
      "Jul"="Jul"->"No Entity"->"No Items"-> "No Account" * Sales -> "Description" -> "number of employees";
      "Aug"="Aug"->"No Entity"->"No Items"-> "No Account" * Sales -> "Description" -> "number of employees";
      "Sep"="Sep"->"No Entity"->"No Items"-> "No Account" * Sales -> "Description" -> "number of employees" ;
      "Oct"="Oct"->"No Entity"->"No Items"-> "No Account" * Sales -> "Description" -> "number of employees" ;;
      "Nov"="Nov"->"No Entity"->"No Items"-> "No Account" * Sales -> "Description" -> "number of employees";
      "Dec"="Dec"->"No Entity"->"No Items"-> "No Account" * Sales -> "Description" -> "number of employees";
      ELSEIF (@ISMBR("Staff costs - Cost of sales") AND ( Sales -> "Description" -> "Start Month" == 2))
      "Jan"= #Missing;
      "Feb"="Feb"->"No Entity"->"No Items"-> "No Account" * Sales -> "Description" -> "number of employees" ;
      "Mar"="Mar"->"No Entity"->"No Items"-> "No Account" * Sales -> "Description" -> "number of employees";
      "Apr"="Apr"->"No Entity"->"No Items"-> "No Account" * Sales -> "Description" -> "number of employees";
      "May"="May"->"No Entity"->"No Items"-> "No Account" * Sales -> "Description" -> "number of employees";
      "Jun"="Jun"->"No Entity"->"No Items"-> "No Account" * Sales -> "Description" -> "number of employees";
      "Jul"="Jul"->"No Entity"->"No Items"-> "No Account" * Sales -> "Description" -> "number of employees";
      "Aug"="Aug"->"No Entity"->"No Items"-> "No Account" * Sales -> "Description" -> "number of employees";
      "Sep"="Sep"->"No Entity"->"No Items"-> "No Account" * Sales -> "Description" -> "number of employees" ;
      "Oct"="Oct"->"No Entity"->"No Items"-> "No Account" * Sales -> "Description" -> "number of employees" ;;
      "Nov"="Nov"->"No Entity"->"No Items"-> "No Account" * Sales -> "Description" -> "number of employees";
      "Dec"="Dec"->"No Entity"->"No Items"-> "No Account" * Sales -> "Description" -> "number of employees";.............
      ...................................
      ..................................
      ....................................
      ENDIF;)
      ENDFIX
        • 1. Re: Essbase Calc Script
          715835
          Try this: SET CREATENONMISSINGBLK ON;

          Hope this helps.

          Tim Young
          • 2. Re: Essbase Calc Script
            RobertR3
            To create the blocks, solve for a sparse member first. Especially since you are assigning values for all months (which will clear over any values used to create the blocks).
            I'd try something like this before the first fix that you have listed.....

            Fix(@IDESCENDANTS("Salary Cost Center"), USD,"No Product",@idescendants(Position), @IDESCENDANTS("To be hired"), @Relative("YearTotal", 0))
            &BudYear = 0;
            End Fix

            One note about solving for sparse, you need to list at least one member from EVERY OTHER dimension, dense included, in the Fix statement (so you may need to add more dimesnions to the fix statement I listed).

            Robert
            • 3. Re: Essbase Calc Script
              704787
              I tried using set create non block on eq but it did not work for me.
              I am testing the other recommendation and will get back to you on this in sometime
              • 4. Re: Essbase Calc Script
                VManek
                Have this script before your existing Fix that calcuates the data and after set command.....

                /* The following should create blocks */

                FIX( @IDESCENDANTS("Salary Cost Center"),
                USD,
                "No Product",
                @idescendants(Position),
                @IDESCENDANTS("To be hired"),
                "JAN"
                )
                &BudYear
                (
                IF (@ISMBR ("Staff costs - Cost of sales") AND (Sales->"Description"->"Start Month" == 1))
                &BudYear = &BudYear + 0;
                ENDIF;
                );
                ENDFIX

                Note: By having "&BudYear = 0" will overwrite existing value (if block already exists) and so it is recommended to have something that will not overwrite it. Also, many times block creation logic can hamper performance and so it is necessary that you specify only those members combinations that you need to work on in your FIX statement.

                Hope this helps.
                • 5. Re: Essbase Calc Script
                  RobertR3
                  While dealing with block creation is maddening at times, and I agree that performance considerations come into play (that's why I stay away from "SET CREATENONMISSINGBLK ON;" and "SET CREATEBLOCKONEQ ON;") in this case it was nothing to worry about in assigning a constant to &BudYear because the rest of the script is reassigning values Jan thru Dec anyway.
                  And as for the performance concerns (which is always something to worry about when dealing with DBs for fortune 50 companies) that is why you encase the sparse assignment inside of a Fix, to narrow scope to only what you need created.
                  Ideally, you woud want to redo the meat of the script to do the solve for sparse only on what you need anyway. But since this is a message board and I don't have access to his outline, much less the rest of his script, I can't do that for him. (nor would I want to on a free message board as that is what I get paid for)

                  In my experience, trying to create blocks inside of an IF statement doesn't work. Must be something about an IF not being able to resolve what doesn't exist, and this won't do at a time you need to create blocks.

                  Good luck to OP,
                  Robert