Tuesday, October 14, 2014

Using Presentation Variable with All Column Values or No Default Value Option

My Environment: OBIEE 11.1.1,7.1, Oracle DB 11g

A presentation variable is passed to a report in dashboard to filter it based on user selection. Sometimes there is a need to have the report filter default to 'All Column Values' or no value at all instead of a specific one. I blogged on a similar topic here

I created a simple dashboard prompt for Product Name:

A simple report for Product and Sales Amount was created:

 Report and prompt were added to my test dashboard and I received this when I ran the dashboard - 'No Results'

The SQL generated was:

SQL generated had a filter clause with PROD_NAME  = 'pv_product' which was the presentation variable I defined.

I made a simple change to the report and added '%' as a default value to the filter. I was hoping that it will work as a Pattern - Matching LIKE condition in Oracle.  

After my change the dashboard ran fine. 

Here's the best thing; the beautiful SQL generated:

The OBI Server did not even include Product Name filter in the query. This is amazing because having a LIKE clause is always a performance concern. 

I am just wondering why such information is not available in OBIEE docs and OBE series. For me at least, defining filters like this have been very helpful in recent projects. 

Hope it helps.