Using a Business Rule to Populate System Date in Planning

Oracle Essbase stores dates from Oracle Hyperion Planning as numerical values, for example, the Start Date May 21, 2021 is stored as 20210521.

Business processes other than Planning that use the Essbase database utilize a date functionality that uses the number of elapsed seconds since midnight, January 1, 1970 (a non-numerical format different than how Planning stores dates) to store such dates.

It is possible to convert Essbase date format to Planning format for use in forms to populate the system date (today's date) in Planning date members.

For example, assume that you want to dynamically add a system-generated date (based on the current system date) to a Planning member as "Start_Date" using a calculation in a form. This need may stem from an audit requirement to dynamically add a read-only, non-user date input against a record, for example, to identify when a new contract or customer was added.

To convert the Essbase date value May 21, 2021 to equivalent Planning numerical value, multiply the month by 100 and year by 10,100, and then add the products of these calculations to the number of days as shown below:

Year = 2021 * 10,000 = 2021000 
Month = 05 * 100 = 500
Day = 21
2021000 + 500 + 21 = 2021521

You can do this in a business rule by holding the Essbase date elements in variables until you write the result to a Planning member. To accomplish this, Essbase provides functions that help you extract today’s date (@TODAY) and to derive the day, month or year from that date (@DATEPART).

The following example shows how to use @TODAY and @DATEPART to create variables that store the date, month, and year from system date in Essbase format, perform the calculation to convert them to Planning date format, and store the Planning date format in a variable to populate "Start_Date" as required:

Var_Day = @DATEPART(@TODAY(),DP_DAY);
Var_Month ==@DATEPART(@TODAY(),DP_MONTH)*100;
Var_Year = @DATEPART(@TODAY(),DP_YEAR)*10000;
Var_TodayDate = @sum(Var_Day, Var_Month, Var_Year);

Start_Date = Var_TodayDate;

You could use a formula, using syntax similar to the following, to dynamically add the system date as the "Start_Date" to a member:

@MEMBER(@HspNthMbr(@name(@descendants("AllMembers")),nextMember))->"Start_Date"=Var_TodayDate;