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}')))
and
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.