• , Consultant

There is a very useful function available to business rule authors, @RETURN . This function allows you to return a message to the user executing the business rule and terminate the business rule. One very valuable use of this function is when verifying the values input into run time prompts. I use it extensively in building business rules for workforce and capex planning where there may be multiple inputs obtained using run time variables.

The format of the function is @RETURN( msg, ERROR). The msg can be a literal or, more interestingly, can be the @CONCATENATE function which can be used to return values of dimensions or even the value input into the run time variable.

  •  The @RETURN can only be used in a Member Calc Block.
  •  The documentation says that there are multiple values for the second parameter but the only one that works is ERROR without quotes.
  •  The message is returned to the user and logged to the Essbase application log.
  •  FIX to a single intersection so that the edits are only done once.
  •  May need SET CREATENONMISSINGBLK ON if the intersection that you use for the edit does not exist. Make sure to turn it off after the edits.
  •  The @RETURN terminates the business rule execution but cannot stop the saving of the data on the form.
  •   Example 1 - fix and return literal message :
    • FIX( "Budget","Working",{gv_1_Entity},{gv_1_CostCenter},{gv_1_Project},{gv_Exempt},"No Employee","Local_Input",&svBudYear,"BegBalance" )



      /* Must be at the base level of Entity, Cost Center and Project */

      IF( NOT @ISLEV("Entity" , 0) )

      @RETURN( "You must select a base level Entity member.", ERROR );


      IF( NOT @ISLEV("CostCenters" , 0) )

      @RETURN( "You must select a base level Cost Center member.", ERROR );


      IF( NOT @ISLEV("Projects" , 0) )

      @RETURN( "You must select a base level Projects member.", ERROR );


      /* Backfill end month must be after the hire month if entered */

      IF( {gv_EndMonth} != #Missing )

      IF( {gv_EndMonth} < {gv_HireMonth}->"MonthNumber" )

      @RETURN( "The backfill end month must be after the hire month.", ERROR );



      /* AIP or Sales or Production no two */

      IF( {gv_AIP_Pct} > 0 )

      IF( {gv_SalesIncentive_Pct} > 0 )

      @RETURN( "If an AIP % is entered then Sales Incentive Target % cannot be.", ERROR );


      IF ( {gv_ProdIncentive_Pct} > 0 )

      @RETURN( "If a AIP % is entered then Production Incentive Target % cannot be.", ERROR );




      /* Sales not Production */

      IF( {gv_AIP_Pct} == 0 )

      IF( {gv_SalesIncentive_Pct} > 0 )

      IF( {gv_ProdIncentive_Pct} > 0 )

      @RETURN( "If a Sales Incentive Target % is entered then Production Incentive Target % cannot be.", ERROR );







  •  Example 2 - Using @CONCATENATE :
    • IF( ( {lv_Promotion_Pct} / 100.0 ) < "Promotion Type 1 Minimum Increase Percent"->"No Employee"-->"NoBusinessUnit">"NoOperatingUnit"->"NoDepartment"->"NoEmployeeType"->"NoProject" ->"NoProduct"->"BegBalance" OR ( {lv_Promotion_Pct} / 100.0 ) > "Promotion Type 1 Maximum Increase Percent"->"No Employee"->"NoBusinessUnit"->"NoOperatingUnit"->"NoDepartment"->"NoEmployeeType">"NoProject" ->"NoProduct"->"BegBalance" )

      @RETURN(@CONCATENATE( "The Promotion Percent must be between ",

      @CONCATENATE( @CalcMgrDoubleToString(@ROUND( "Promotion Type 1 Minimum Increase Percent"->"No Employee"->"NoBusinessUnit"->"NoOperatingUnit"-->"NoDepartment"->"NoEmployeeType"->"NoProject"->"NoProduct"->"BegBalance" * 100, 2 ) ),

      @CONCATENATE( " and ", @CalcMgrDoubleToString(@ROUND( "Promotion Type 1 Maximum Increase Percent"->"No Employee"-->"NoBusinessUnit"->"NoOperatingUnit"->"NoDepartment"-->"NoEmployeeType"->"NoProject"->"NoProduct"->"BegBalance" * 100, 2 ) ) ) ) ),ERROR);

I have shown you examples of how to use the @RETURN function to add functionality to your business rules to enforce edits and inform the user of the errors. I hope you find this useful.

Contact MindStream Analytics

The consultants at MindStream Analytics are here to help you take your planning, budgeting, forecasting, consolidations, and reporting to the next level. Fill out the form below to learn how.

Latest testimonial

MindStream Analytics is a leading consulting firm focused on helping clients improve business understanding and decision making. With years of experience in the analytics and Business Performance Management area, MindStream offers customers services ranging from software selection and implementation to best practices for financial planning. MindStream will work with customers towards a solution that enhances value and offers more insight into their data. MindStream believes in the power of technology combined with new procedures to give customers better analytic capabilities