[OBIEE 10g] Making A Column Selector In A Prompt

 

OBIEE Making a column selector in a prompt 

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