OBIEE in IL: OBIEE Prompts - 2 prompts on same column

This is the third part on the "2 prompts on same column" issue. I will be dealing with 2 character / string prompts. (There will be no "number prompts" post. It is mostly trivial). Specifically comparing specific lists of values.
You might want to see the first 2 parts:

First part (here) was prompt options background.
The second (here) was about 2 date prompts.

I had the following request:

A prompt to select possible values from a column.A prompt to select one of them as the focal (the other should be used to compare).

There is similar, but much easier solution at the end.

In this example I will select several Years in first prompt. They will be used for filtering the report and for selecting values.
In the second prompt I will select one of the previously selected years.
In the analysis I will compare results of the selected Year with the other years in the first prompt.
Reminder: "Year" in OBIEE Sample is defined as Varchar 4.

First I will create a Column Prompt on "Time"."Year" with Multiple Selection and Presentation Variable P_Selected_Years:

Since it's a Column Prompt, the "Is Prompted" option should work.

Next I want to create a prompt for selecting a single value from the selected years.

This post explains first why several "logical" options don't work. You can skip to the ***** line if you want the practical solution.

Wrong idea is to create a Variable Prompt on the column "Time"."Year" and mark "limit values by" option. Of course it wouldn't work here, since it's the same column.

Instead I will use List Values, based on SQL Results:

The SQL will be the result of this Analysis:

From Advanced Tab I will get the following SQL: SELECT "Time"."Per Name Year" saw_0 FROM "Sample Sales Lite" WHERE "Time"."Per Name Year" IN ('P_Selected_Years')

as expected we don't see the presentation Variable in the SQL. My favorite foolproof method to return it, is to copy the relevant part from the filter description:

and pasting it in the SQL instead of the P_Selected_Years string:

SELECT "Time"."Per Name Year" saw_0 FROM "Sample Sales Lite" WHERE "Time"."Per Name Year" IN (' @{P_Selected_Years}')
this way I don't make mistakes with apostrophes.

This seems to work with one value in the Presentation Variable P_Selected_Years:

and fails with two or more:

Why does it happen? Because OBIEE expects '2008','2009' as the content of the Variable, but actually gets '2008;2009' as one string. We don't have to deal with it in Analysis filters (it works well), but here it's a problem.
There is a solution for this problem here, by Deepak.

**********************

As a result, what I actually do is to create a Variable Prompt, with Presentation Variable P_YEAR, User Input = Choice List, Choice List Values = SQL Results and the SQL:

SELECT "Time"."Per Name Year" saw_0 FROM "Sample Sales Lite" WHERE "Time"."Per Name Year" IN (@{P_Selected_Years}['@'])

Exactly as before, in my previous attempt, only with ['@'] after the prompt definition and no apostrophes around @{P_Selected_Years}.

Now it works:

Naturally I will not allow multiple values in this part.

My sample analysis is quite simple: Comparing the Revenue of selected Year with Average Revenue of all the other years in the list.

I can use "is prompted" on the YEAR column in Filters, I don't have to bother with the presentation variable "P_Selected_Years".

To calculate the Selected Year Revenue I will use Formula Filter.
Revenue of Selected Year is:
FILTER("Base Facts"."Revenue" USING ("Time"."Per Name Year" = '@{P_YEAR}{2009}'))

Revenue of compared Year is:
("Base Facts"."Revenue"-FILTER("Base Facts"."Revenue" USING ("Time"."Per Name Year" = '@{P_YEAR}{2009}')))
/
(count(DISTINCT "Time"."Per Name Year")-1)

This means: Total revenue minus Revenue of Selected Year divided by (number of selected Years minus 1).

Now I can subtract, divide or do any calculation I want with the columns or I can add Regular Revenue column and calculate % of total...

It's almost ready, but I had 2 more problems:

The 2 Apply buttons
They wanted the selected Year and Compare Years in the Column Header.

I will deal with the Apply buttons later in the post. Lets start with the header.

I have no problem with selected year:

I change my Column Heading to be: Revenue selected year @{P_YEAR}, as long a there is a value in the Variable, all is fine:

It's the Compared Years that give me the headache. For them I have to do the following:
I need a third presentation Variable that would actually contain P_Selected_Years minus P_YEAR.
The SQL of this prompt is:

SELECT saw_0 FROM ((
SELECT "Time"."Per Name Year" saw_0 FROM "Sample Sales Lite" WHERE "Time"."Per Name Year" IN (@{P_Selected_Years}['@'])
) EXCEPT (
SELECT "Time"."Per Name Year" saw_0 FROM "Sample Sales Lite" WHERE "Time"."Per Name Year" IN (@{P_YEAR}['@'])
)) t1

Both as Select and Default:

As usual, I was too lazy to write the code by hand and first created an analysis where I had One part of the date (protected filter Per Name Year is equal to / is in @{P_Selected_Years}) Minus the other (protected filter Per Name Year is equal to / is in @{P_YEAR}):

It works fine:

But I want the third Prompt to be hidden.

In Dashboard Properties (Edit Dashboard first).

In Filters and Variables

I will add the third dashboard prompt (and remove it from the dashboard)

As we did before, I'll change the header of column, to include the Presentation Variable: Revenue Compared @{P_YEAR2}

The result is:

Now back to the Apply buttons.

Since the presentation Variable is updated only after the APPLY, I can't have both in the same Dashboard Prompt.

The solution is to Hide the APPLY and Reset options, at least for the first prompt (otherwise the users will be annoyed for pressing APPLY twice).

That might be a problem because the analysis is run each time we update values in prompts. In my case, I have an irrelevant result before selecting Main Year.

There is a nice solution by M&S Consulting described here.

Much easier solution

Lets change the requirements just a bit: a prompt for focal Year and a prompt for Years to compare (if same Year selected in both I want a warning instead of analysis).
It seems almost identical to the complex solution above.

I have One Prompt, with 2 parts (this way I don't have the multiple Apply problem):
First is a Variable Prompt with Presentation Variable based on the Year column.
The other is regular Column Prompt on Year that set another presentation variable.

If you want the message when Years to compare contains the Main Year. Create a condition analysis. In both parts the column would be "Time"."Per Name Year" the set operation is Intersect and the filter would be "is prompted" for the first part and Per Name Year is equal to / is in @{P_Main_Year}{2010}in the second. This analysis will return data only if same year selected in both prompts, and can be used for section condition (to show the error message):

The actual Analysis is based on Union with Revenue for Product Type. I decided the default would be comparing Year 2010 to 2009:
The column Headers are:

Product Type
Revenue Year @{P_Main_Year}{2010}
Compare Revenue @{P_Compared_Years}{2009}

First part of the union is the Compared Years content (based on the second part of the Prompt, so "is prompted" will work here).

The second column is placeholder with formula sum(0).
The third is the Revenue divided by number of years ("Base Facts"."Revenue"/COUNT(DISTINCT "Time"."Per Name Year")).

Now a union with similar content to the above. This time the second column will be "Base Facts"."Revenue" and third columns sum(0). The filter would be Per Name Year is equal to / is in @{P_Main_Year}{2010}