Monday, January 27, 2014

OBIEE Prompts - 2 prompts on same column - Part 2

This is the second part of the post. In the first part, with the long name: OBIEE Prompts - 2 prompts on same column, protect Filter and more - Part1 I was talking about the difference between Column Prompt and Variable Prompt. In this part, we will start dealing with the 2 prompts on same column issue.

Why is it a problem? Because when we try to create a Dashboard Prompt and attempt to use the same column as Column Prompt, you will see the message "This column exists in prompts. This action will be ignored."
In most cases we need both columns for Presentation Variable.

In OBIEE 10g there were 2 main option to solve this issue:
A. The terrible option of creating another column in the Presentation Variable.
B. The less demanding creation of a column with same content but different function. For example:
  1. String: "Time"."Year" vs. "Time"."Year"||''
  2. Number: "Base Facts"."Revenue" vs. "Base Facts"."Revenue"+0
  3. Date: "Time"."Calendar Date" vs. TIMESTAMPADD(SQL_TSI_DAY, 0, "Time"."Calendar Date")
In 11g (as it's clear in the first Part 1 of this post) you can simply use Variable Prompts to create Presentation Variables.

For example:

EXAMPLE: Comparing two dates.

If all we want to do is to place 2 date values side by side,

we don't need all the complexity and can create a simple Column prompt with "is between" Operator (or let the  user select several dates):
 And simple "Is Prompted" filter:

Even when we need some complex calculations we can do it on Pivot Level.
For Example lets calculate change as Second Date minus First Date and % Change as 100*Change/First Date:

 All we need to do is to create "New Calculated Items" For Calendar Date:

One with Formula $2-$1, (that means second date value Minus the first)

And the other  100*($2-$1)/$1

We can see it's quite resilient to Null and division by Null problems.

But when we require some extra complexity, we need to have 2 Presentation Variables on Dates:
Since we are dealing with Dates here, I want to select Dates from calendar, but I don't see a Calendar option in User Input:
There is a little voodoo here: At first glance we might suspect there is no such an option. Even after I select User Input as "Choice List" and select "All Column Values" so I'm able to select the relevant Column ("Time"."Calendar Date") the User Input option doesn't have a "Calendar" as an option

Only after I press OK and edit the row again,the Calendar option appears:

Now we have 2 date Presentation Variables: P_First_D and P_Second_D

Lets use them in an analysis.

I created an Analysis with analysis Filter where DATE=first_Prompt or DATE=second_Prompt.

There is no date Column in the analysis. Instead I created two columns based on Revenue with Filter Function:
FILTER("Base Facts"."Revenue" USING ("Time"."Calendar Date" IN (date '@{P_First_D}{2010-01-16}')))
FILTER("Base Facts"."Revenue" USING ("Time"."Calendar Date" IN (date '@{P_Second_D}{2010-01-17}')))
Now I can subtract, divide or create any calculation I prefer, based on the existing calculation.
Please note, the performance of the first option described above (using calculated Items) is much better. 

In next part, i will do an example about 2 Prompts with multiple values in Character Variable. 

But before the end, a tip about Prompt Based Filter in Formula creation:

When we create a Filter in Formula

We get a very nice wizard that enables us to create a condition that includes Variables:

 While it seems OK:
After we press OK, the Variable disappears and must be returned manually:

What we actually get in this case is:
 FILTER("Base Facts"."Revenue" USING ("Time"."Calendar Date" IN (date '2010-01-16')))

While I expected:

FILTER("Base Facts"."Revenue" USING ("Time"."Calendar Date" IN (date '@{P_First_D}{2010-01-16}')))

You need to fix it manually.
Please note the nonstandard format of the date. My original date format was 01/16/2010. The filter wizard changed it. Trust it.

Just a little followup. I've seen here someone asking for validating of dates, so the second Presentation Variable in prompts is later than the first.

I'll create an analysis (condtion1), that returns values only if it's true:

column: date '@{P_First_D}{2010-01-16}'
Filter (after convert to SQL): date '@{P_First_D}{2010-01-16}' <= date '@{P_Second_D}{2010-01-16}'

Now all is left to do is put a condition on the relevant section in the dashboard with
requirement that row count of analysis condition1 is greater than 0.

Last part is to create a section with condition that requires the row count on analysis condition1 = 0. In this section I'll place a warning message.

Now when the prompt is empty or correct I see the regular dashboard, when the dates are wrong I see only the message. 


  1. Great help! Kudos to you!

  2. Hey, I'm trying to create a condition where From Date is <= To Date and To Date<=Current Date. Though I've put the filters as above, I'm getting an error.

    Error getting drill information: SELECT date'2016-08-24' saw_0 FROM "Workforce Management - Processed Time Cards Real Time" WHERE ( date '@{To_Date}' >= date'@{From_Date}' ) AND (date'@{To_Date}' <= (SELECT VALUEOF("CURRENT_DATE_REP_OTBI") FROM "Workforce Management - Processed Time Cards Real Time" FETCH FIRST 1 ROWS ONLY))

    Loving your blog so far!

  3. Wow, you awesome ... with this voodoo trick you save my day.

  4. Hi Boris. I have a date presentation variable ("Date"."Month Description" IN (@{cust_date})). How can I get the last 3months based on the date picked e.g if the date passed is 11-Nov-2017, I display data for Aug, Sept and Oct? How do I customize the variable to give me previous 3 months?

    1. In this case I would create 2 date variables / expressions:
      1. till_date: the date minus day_of_month
      2. from date: till_date - 3 month + 1 day

  5. This works like a charm, however, one thing that I don't see addressed here. I'm using format MM/DD/YYYY HH:MM:SS and each time I change any filter value, the hours move forward in this field (?) In other words, the default value does not stay fixed, but shifts. Any idea how to prevent that from happening?

  6. Amazing job. fantastic.

    I need help, I am trying to make 2 Variable prompts that will give between 2 dates (could be using 1 column or not necessary, there is start and end dates on my columns), problem is, this Variable prompt has to give results for 2 different repositories. I managed to do it for other prompts, but Date specifically, or the 'in between' I don't know how.
    Any ideas?