OBIEE Prompts - 2 prompts on same column, protect Filter and more - Part1

This post is about using OBIEE 11g Prompts and was created on OBIEE 11.1.1.7. The main intention was to write about "creating 2 prompts on the same column". As background, it also covers how "Is Prompted" works, why do we need the "protect filter" option, as a basics.
At the end there will be few other "prompt related" issues.
I split the post to Part 1, the relevant background and Part 2, creating 2 prompts on same column.  

I didn't intend to write about creating 2 prompts on same column, since I was sure it was very well covered by others. Few days ago a business partner (Rotem, enjoy your honeymoon at Galapagos) told me, all she could find were tips guiding her to create 2 copies of the relevant column in RPD and then base the Presentation Variable on them... So I will cover few more options, but first lets ask ourselves why do we need few prompts on the same column?
Few use cases such as:

1. Selection values that should be compared (Customer name in prompt1 compared to other customer or customers in prompt2).
2. From Date, To Date prompts.

Lets start with the basic. What happens when we create a Dashboard Prompt?

We can create:

• Column Prompt
• Variable Prompt
• Image Prompt

 What is the difference between Column and Variable Prompt?  
 Column Prompt is based on a Real Column or a Formula, while Variable Prompt has no actual column behind and is aimed to create presentation or request variables only. You can create those variables with Column Prompt as well.
Variable Prompt enables us to create the variables, in a simple a quick way and doesn't have all the extra definitions of Column Prompt.

Column Prompt, "Is Prompted" and "Protect Filter"
As I said, Column Prompt is based on a Real Column or a Formula. When we create a Column Prompt we are required to select a Column.

Later we can edit or change the formula:

Why is the formula important? Because when the formula in the prompt and formula in analysis are the same you can use "Is Prompted" in Analysis filters:

Why do I insist on talking about "formula" and not "column"? Because we can edit the formula both in analysis and in prompts. So, when the formula in both places are the same "the magic" works.

EXAMPLE1: Lets create the above example (Prompt on the column "Time"."Per Name Year") and 2 sample Analysis with a single column and filter "Is Prompted".
In the first Analysis the column would be "Time"."Per Name Year" and in the second "Time"."Per Name Year"||'' (the Year string concatenated with empty string. Exactly the same result, but different formula).

As expected the prompt influences only the first analysis where the formulas are the same:

EXAMPLE2: Lets do the same but change the Column Prompt Formula to "Time"."Per Name Year"||'' (the one from Year2 analysis):

As expected only the Year2 Analysis is influenced by the prompt:

EXAMPLE3: Now to see why we need "Protect Filter". We will set the prompt back to "Time"."Per Name Year" and create a third analysis with a column "Time"."Per Name Year" and a filter "Time"."Per Name Year"='2007'.

With the Filter:

The result is: !!!

The filter was for 2007, but we see the prompt overpowered it and the analysis return 2008!
Why? OBIEE is somewhat enthusiastic about prompts. Whenever the prompt and filter have the same formula, the prompt is in control. You want to prevent it? Select any of the check boxes in the prompt definition:

The logical default is "Protect Filter", but "Convert this filter to SQL" will do the trick as well:

Last part of this OBIEE Prompts 101 part will be, "List Values".

Prompts List Values

By default, in Column Prompt, the default List Values are all the column values, while Variable Prompt has no default selection and you must define it, if you want the user to have values to select from (unless it's plain text).

What are the options?

Both For Column Prompt:

And Variable Prompt:

We can select:

• None (for Calendar and Text user input)
• Specific Values (for Calendar and Text user input)
• All Column Values
• Specific Column Values
• Custom Values (Variable prompts only)
• SQL Results
• Members of Group
• All Column Values and Specific Groups (Column Prompt Only)

Lets talk about few of them:
All Column Values: The default for Column Prompt. Can be used in Variable Prompt. Then you have to select a specific column. You might ask, "Isn't it what a Column Prompt does?"

Two differences: 

• "Is Prompted" doesn't work on such Variable Prompts. 
• You can create as many Variables you want on the same column.

  

We will not talk about Groups related option here and most of other are self explanatory, but lets see a very simple (and almost full proof) way to create an SQL Results.

EXAMPLE4: I want the prompt to suggest only Years before 2008. I'll create an Analysis where I select the relevant data:

 In Advanced Tab we can see the issued SQL. You will see a cleaner version if you press "New Analysis":

Copy the content:

And paste it as the SQL Result:

How to create 2 Prompts on Same Column from the above is trivial, but we will see few less trivial examples.