Oracle Customer Engineering & Advocacy Lab (CEAL) Blog covers Oracle Analytics Cloud, Oracle Analytics Server and...

EPM 11.1.2.x - Populating Today’s Date in Planning/PBCS with a Business Rule

Jane Story
Senior Principal Applications Specialist - Customer Engineering & Advocacy Lab (CEAL Team)

In a previous CEAL blog, we have discussed
how to calculate dates in Planning/PBCS:


As discussed in that blog, for dates in
Planning/PBCS, Essbase stores the date as a numerical value e.g. a Start Date
of 21st May 2015 would be stored as 20150521.

In a non-Planning/PBCS Essbase database,
date functionality exists using the number of seconds elapsed since midnight,
January 1, 1970 i.e. a non-numerical format different to the way Planning/PBCS stores
dates to display on web forms.

is possible, however, to use the Essbase date format and then convert that date
into a Planning/PBCS format for use on a web form in order to populate a Planning/PBCS
date member with today’s date i.e. the system date.

So, as an example, say you want to add a
date to a Planning/PBCS member “Start_Date” with a web form calculation and that Start Date
should be today’s date. But the
requirement is that this should be a dynamically, system-generated date based
on the system date of today. One
possible reason for this use case could be to dynamically add a non-user input,
read only, date against a dynamically added record to audit when that record
was created e.g. when was a new contract added, when was a new customer added

Essbase dates are formed using day, month
and year parts. Functionality exists
within Essbase to get today’s date (@TODAY) but also how to take a part of that
date (@DATEPART – e.g. specifying day, month, year).

Take 21st May 2015 as an
example. This would be stored in Essbase
with date parts i.e. 21, 05, 2015 for day, month and year respectively. The equivalent date in Planning/PBCS would be
a single numerical value of 20150521.

To convert the Essbase date parts to a
Planning/PBCS numerical value, it is necessary to multiply the month and year
parts up. This is the mathematics using
the same example of 21st May 2015:

  • Year = 2015 * 10,000 = 2015000
  • Month = 05 * 100 = 500
  • Day = 21
  • 2015000 + 500 + 21 = 20150521
    i.e. the Planning/PBCS numerical format for the same date.

You can do this in a business rule by
holding the Essbase elements in variables until you write the result to a
Planning/PBCS member. The following
example shows how to take the system @today date (in Essbase format) and
convert it to Planning/PBCS date format:

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

 To then write this to a Planning/PBCS
date member:

· Start_Date = Var_TodayDate;

So, as discussed above, if you wanted to
add today’s date against this new record, a date which is today’s system date
and cannot be input by the user, you could use a formula like this to do this,
using the syntax about to create that date:

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

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.