OBIEE making a column selector in a prompt

On of our customers has a very wide table containing 50+ 'settings' columns and four 'result' columns. For there report they only use one of the setting columns, but they wanted total freedom to select this column (1 of 50+).
A normal prompt for this report would take up a very large portion of the screen and was found very unpractical / unaesthetic.
Here is how we solved this, based on the 'PAINT' example:

First we made a simple report based on the columns Year, Year Ago Dollars and Dollars. For debugging purpose we added the Logical SQL view.
On the year column we added the following filter based on a presentation variable.

No switch to the Advanced tab:
Have a close look at the request XML especially the filter part:


Periods."Year"
FILTER01_LEFT

Now edit the FILTER XML to:

@{FILTER01_LEFT}{'1'}
@{FILTER01_RIGHT}{'1'}
Don't forget the single quotes (').

Press the set XML button: and switch back to your criteria view:

Save the report!
If you run the report and have a look at the logical SQL:


you will see that the filter have evaluated into 1=1, which is always TRUE. It's a good practice not to let it evaluate to NULL = NULL since not every database will give back the same result.
Now create a new prompt called FILTER01_LEFT as a dropdown list based on SQL:

In the SQL statement you put:
SELECT case when 0=1 then Markets.Region else '1' end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else 'Markets."Total US"' end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else 'Markets.Region' end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else 'Markets.District' end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else 'Markets.Market' end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else 'Products."Total Product"' end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else 'Products.Type' end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else 'Products.Brand' end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else 'Products.UPC' end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else 'Products.Color' end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else 'Products.Finish' end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else 'Products."Size X"' end FROM Paint
Test the result:

Add the second prompt FILTER01_RIGHT as edit box prompt:

Test the result:

Save the prompt and open a new dashboard page, put the prompt and the report on the dashboard page:

Test the result

Till Next Time

John Minkjan is a senior BI-consultant at Ciber in the Netherlands, the English text of this article is also published on his compagny blog http://knowledge.ciber.nl/weblog/