Using @RETURN in the real world

Both the business and users are always looking to enhance validations on planning forms and business rules one way to do this is using the RETURN function. Starting with 11.1.2.1 release of EPM we got a new Calc Script function added ‘@RETURN’ the Essbase Tech Reference defines this as “ Exits the calculation immediately under specified logical conditions. You can use the IF... ELSEIF calculation command block to specify the logical error conditions, and use the @RETURN function to exit the calculation with customized error messages and levels”

Well that definition is great and gives us an idea of what the function does, it doesn’t tell us a good way to use it.  Over the last couple of years I have encountered some great use cases, and I think this is the time to share those.

• User confirmation on rule run
• Member validation on RTP
• Input validation
• Rule run confirmation output

In all of these use cases the key thing to note is that it’s not the syntax of the @RETURN function that’s key, it’s how it’s used in conjunction with the rest of your application. 

Use Case 1 - The first one came from a recent question someone had posted on the OTN forums. They were looking for a way to prompt the user for confirmation before running a business rule which would clear data. Seemed like a simple thing to do, but it made me think, and thinking leads to creative solutions.  So the solution I came up with was to add an RTP to the rule, asking the user to type in “YES” if they were sure they wanted to clear the data. Once I have that input in an RTP it’s a simple process of putting it in an IF statement and you have the result you’re looking for.

IF ( @hspstringcompare({RTP_AreYouSure}, "YES"))

     @RETURN("You were sure!",ERROR);

ELSE

     @RETURN("Unsure were you?",ERROR);

ENDIF

In your real use of this, you would obviously replace the “you were sure!” error with some more code to execute, or change the’ if’ around so that it only traps the exit condition. 

Use Case 2 - this one is something I put in place to make sure that a ruleset was run only on appropriate Scenario and Version, but could be adapted in any number of ways. Again in this example we are going to use RTPs, but this time they are just passing members, from a form, or input, whatever your case is, that’s not too important.  All you need to do is fix on your RTPs and do an IF to make sure you have the correct members
 

IF (not @ISMBR("Plan"->"Working"))

     @RETURN("You must select the Plan Scenario and Working Version to run calculations",ERROR);

ENDIF;

I recommend making this a separate rule at the start of the ruleset, but you could put it at the top of your existing rule.

Use Case 3 - This is not one that I have personally used, but there are a lot of places where you would want to use it; it also happens to be the use case I see the most often demonstrated, including the tech reference example. Here you are just doing a quick check to make sure that the value, a driver for example, is within a specified range.  You can compare against a value passed through an RTP or a value existing in a member.  Again we will use a simple if statement to check our condition.

               

                IF (acct1001 > 500)

                                @RETURN(“Value input in acct1001 must not exceed 500”,ERROR);

                ENDIF;

Use Case 4 - The last case I'm going to demonstrate allows you to present the user with some confirmation of rule completion, show them where it was run, and even provide them with some follow up steps if required.  This is the one use case where I do not use an IF statement, and just input the RETURN function.  You can use @CONCATENATE to build up here is an example:

 @RETURN(@CONCATENATE(@CONCATENATE(@CONCATENATE(@CONCATENATE(@CONCATENATE(@CONCATENATE(@CONCATENATE(@CONCATENATE(@CONCATENATE("Data is only moved for selected year, please run for future years if. Rule was run for Jobcode:",@NAME({RTP_Rev_Program}))," From:"),@NAME({FromOU}))," To:"),@NAME({ToOU}))," For "),@NAME({FirstMonth}))," To Jun of "),@NAME({FirstYear})),ERROR);

The resulting message in this case would look something like this:

 

Well as promised at the beginning of this post, that’s 4 different uses of the function, it should be noted that these are not all of the different ways that you can use RETURN, and that you can easily combine many of these uses in a single rule, but remember, once the function is called, the rule will stop.

One final benefit, since this function will show as an error, you can reference back into the job console to see a history of where the messages have been shown in the details of the error, this can be helpful for identifying user behavior ( too many error messages in the console may even mean additional user training may be useful) , or just as an audit to ensure that conditions are applied correctly.

I have presented 4 use cases here, if you have more please leave a comment, I would love to hear how others are also using this function. Well it’s time for me to return back to doing ‘real’ work, hope this helps you, until next time.

-Dan