Help Users Do It Right the First Time
In Hyperion Planning and Essbase on premise (beginning with version 11.1.2.1), PBCS and EPBCS a Business Rule or calc script can be used to help direct your uses to correct mistakes or remind them to complete other tasks by using the @RETURN function. This functionality is not widely known, so it is underutilized but it can be very powerful. It may be used with data validation rules (the “belt and suspenders” approach) or by itself. The function will exit the calc script or business rule immediately if the specified condition is met and will provide a customizable error message. If you wish the rule to not run at all, put the @RETURN function at the beginning of the script. Put at the end of the rule if you wish it to just provide information or a warning. The error message from the @RETURN function will also appear in the Essbase application log if using Hyperion on premise along with a transaction aborted entry.
Some use cases:
Inform users
- Budget deadlines (Example 1)
- The next task they need to complete
- After the rule completes, display the dimension members for which it was run (Example 2)
Warn users if input data is incorrect or out of bounds, examples
- Percentages do not add up to 100% (Example 3)
- Start Month is not before or the same as the end month (Example 3)
- Forecast entered exceeds budget
- Adding new hire in Workforce Planning where start date is a date in the past
Support processes
- Prohibit allocations from being run more than once at the same time to prevent block contention issues (Use a flag where if the calc is running the flag=1 and when the calc is complete change the flag to zero or #MISSING. If another user attempts to run the calc, with the @RETURN at the beginning of the calc, inform the user that the allocation is currently being run)
Enforce validation rules
- Have user confirm they really wish to run the rule (especially if data is being cleared)
- Allow the rule to run for only valid combinations, for example scenario/version combinations
- Do not allow a budget to be seeded from prior year with the DATACOPY command more than once (by using flags)
Assist developers
- Developing business rules
- Debugging business rules (especially useful when there are complex nested if statements)
Syntax
The @RETURN can test for metadata or data conditions and must be placed within an IF statement. If the condition is met, a pop-up will appear with a text message. The business rule may also contain multiple @RETURNs, but will stop at the first one where the condition is met. The syntax of the function is @RETURN(“Message”,ERROR). While the Oracle documentation allows for the use of WARNING, INFO and ERROR for the message type, only ERROR will successfully work. I tested this on the current version of EPBCS.
The @RETURN is especially powerful to test cases where IF statements cannot be used. For example, the DATACOPY statement cannot be within an IF statement. So if you do not want the DATACOPY to run if certain conditions are met, the script can be set to stop running using @RETURN.
Example 1
Here is a very simple example using EPBCS that provides information only. It is at the bottom of the script so it does not stop the business rule from running.
FIX(“OEP_Actual”)
“PC Revenue”(
IF(@ISMBR(“PC Revenue”))
@RETURN(@HspMessage(“Reminder: Budgets are due today at 5pm PST”), ERROR);
ENDIF)
ENDFIX
When run from Rules, this pop-up appears.
When the run is attached to a form, this pop-up appears when saving the form.
Example 2
The text returned by the @RETURN function can also be dynamically generated, as in this example. Nested Concatenates may also be used.
/* @RETURN pop-up to display year that was run */
FIX(“OEP_Actual”,{Years})
“OEP_Working”(
IF(@ISMBR(“OEP_Working”))
@RETURN(@CONCATENATE(“The business rule was run for: ,@NAME(@CURRMBR(“Years”))),Error);
ENDIF)
ENDFIX
When run from an EPBCS form:
Example 3
Here is an example using PBCS that provides a warning but does not stop the business rule from completing, as it is at the end of the script.
/* @RETURN Pop-up */
/* CPG% input by product must total 100% and Start Month must be prior to or the same as the End Month */
FIX ({ScenarioVar},”Wkg”,”LCU”,&FormCurrentYear,{Company_UV_BR},”DriverPeriod”,”No_Product”,”Load”)
FIX(@RELATIVE({Program_UV_BR},0))
“CPG_Pcnt”(
IF((“Total CPS”!=0 OR “Total CPS”!=#MISSING) AND (“CPG_Pcnt”->”TotProduct”!=1 OR “Start_Month”==#MISSING OR “End_Month”==#MISSING OR “End_Month”<“Start_Month”))
@RETURN(@HspMessage(“1. CPG Percent Total must equal 100 AND 2. Start Month and End Month must be selected AND 3. End Month must be greater than or equal to Start Month”), ERROR);
ENDIF
)
ENDFIX
ENDFIX
When run from a PBCS form:
