1. Oracle Enterprise Performance Management Cloud Operations Guide
  2. Troubleshooting EPM Cloud Issues
  3. Troubleshooting Business Rules and Member Formula Errors and Performance
  4. Best Practices
  5. Syntax Considerations

Syntax Considerations

  • Always use @LEVMBRS rather than @RELATIVE if used on the entire dimension.
  • Use @CHILDREN instead of @RELATIVE, if applicable.
  • Use @REMOVE and @LEVMBRS if you only want to exclude some members from a FIX statement.

Performing Calculations Using Dates Stored in Planning

Oracle Hyperion Planning allows you to enter date values. For example, using the MM/DD/YYYY format, a start date may be entered as 11/01/2019 and an end date as 06/01/2020. Oracle Essbase stores date formatted values as numeric values. For example, the preceding start date is stored as 20191101 and end date as 201200601. You can use Essbase functions, for example, the @ROUND, @INT or @TRUNCATE function, to calculate the number of months between any start date and end date. The following example shows how to calculate the number of months between a start date and end date using the @ROUND function:

  1. Calculate the number of months between the end date year and the start date year as follows:
    (@ROUND ("End Date",-4) - @ROUND ("Start Date",-4))/10000*12
    The result of this calculation (20200000 – 20190000)/10000 *12 is 12.
  2. Calculate the number of months between the start of the end date year and the end date and add it. 
    (@ROUND ("End Date",-2) - @ROUND ("End Date",-4))/100
    The result of the calculation (20200600 – 20200000)/100 is 6.
  3. Calculate the number of months between the start of the start date year and the start date and subtract it.
    (@ROUND ("Start Date",-2) - @ROUND ("Start Date",-4))/100
    The result of the calculation (20191100 – 20190000)/100 is 11.
  4. Combine the preceding steps into one formula (12+6-11) to calculate the number of months (7) between the start date and end date .
    (((@ROUND ("End Date",-4) - @ROUND ("Start Date",-4))/10000*12) + 
((@ROUND ("End Date",-2) - @ROUND ("End Date",-4))/100)-
((@ROUND ("Start Date",-2) - @ROUND ("Start Date",-4))/100))

Using the @CURRMBR Function

The @CURRMBR function, which returns the name of the dimension member currently being calculated, is especially useful for scoping and managing complex calculations. However, you must consider its performance impact.

Performance is not affected when using the @CURRMBR function on a sparse dimension because the block corresponds to one sparse dimension member only. However, the performance of this function is slower when used on a dense dimensions because it runs calculations at the block level and not at the cell level. As a result, this function calculates all the dense dimension members, even if a particular dense dimension member is not present in the query. Thus, sparingly use this function with dense dimensions.

Additionally, when used on dense dimensions, the @CURRMBR function may produce unexpected results or errors if used in conjunction with other functions such as @CONCATENATE. For example, if a dynamically calculated member in a query contains the formula "Actual"->@MEMBER(@CONCATENATE(@NAME (@CURRMBR ("Account")),"_Total")) and the Account dimension is sparse, the query will run without error as long as the account in the query generates a valid outline member. However, if the Account dimension is dense, the query results in the following error even if the account in the query generates a valid outline member.

Error executing formula for [member name] (line 0): attempt to cross a null @ member in function [@X]

This error is produced because the @CURRMBR function calculates at the block level and therefore calculates all accounts in the dense block. Not all accounts within the block generate a valid outline member, resulting in the preceding error message. For more information on the @CURRMBR function, see @CURRMBR in Oracle® Essbase Technical Reference.