OBIEE - Dashboard parameter prompt - The WHEN 1=0 or ifnull solution

> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics) > OBIEE - BI Presentation Service (SAW/OBIPS)

1 - About

You may need to create a dashboard prompt to set a presentation parameter (as for the hierachy level) to change the behaviour of your dashboard.

The dashboard prompt is based on the column of a subject area. You must therefore choose one.

It's why I recommend you first this solution : OBIEE 10G - A special subject area to handle the dashboard parameters prompts because it follow the mechanisms of OBIEE

But if you don't have the possibility to create a presentation column to handle your parameters, other solutions exist such as the:

  • CASE WHEN 1=0
  • or ifnull

dashboard prompt.

3 - Dashboard Prompt

Create a new dashboard by choosing any column of the subject area. Then in the show column, choose in the list “Sql Result” and enter this kind of formula.

3.1 - WHEN 1=0

SELECT CASE WHEN 1=0 THEN Products."Prod Category" ELSE 'MONTH' END FROM SH
UNION
SELECT CASE WHEN 1=0 THEN Products."Prod Category" ELSE 'YEAR' END FROM SH

As one is always different of zero, you will obtain MONTH and YEAR as value in the drop down list.

Advertising

3.2 - IFNULL

SELECT ifnull('"My Table1"."My Column1"',CAST("A Table"."A column" AS CHAR)) FROM "SH" 
UNION ALL
SELECT ifnull('"My Table2."My Column2"',CAST("A Table"."A column" AS CHAR)) FROM "SH" 

As the first value is always not null, you will obtain as value in the drop down list this two string:

  • “My Table1”.“My Column1”
  • and “My Table2.”My Column2“

that you can use to create a parametrized logical query.

The second argument can not be a number then may have to cast it.

4 - Support

You can not enter this SQL :

SELECT 'MONTH' FROM SH
UNION
SELECT 'YEAR' FROM SH

because SH describe a presentation catalog and not a table. In the Sql of OBIEE, you really need to have for each column the name of the table otherwise you will obtain this error :

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. 
[nQSError: 42021] The query does not reference any tables. (HY000)
SQL Issued: SELECT 'MONTH' FROM SH

5 - Log / Performance

In the log, we can see that 2 queries are performed :

-------------------- Sending query to database named orcl SH (id: <<3236>>):

select distinct case  when 0 = 1 then T4238.PROD_SUBCATEGORY  else 'MONTH' end  as c1
from 
     SH.Products T4238


+++Administrator:2b0000:2b0007:----2009/06/08 14:47:20

-------------------- Sending query to database named orcl SH (id: <<3275>>):

select distinct case  when 0 = 1 then T4238.PROD_SUBCATEGORY  else 'YEAR' end  as c1
from 
     SH.Products T4238

And if we do an explain plan, we can see that Oracle perform a real access to the data (in the worst case a full table).

[email protected]>EXPLAIN plan FOR
  2  SELECT DISTINCT CASE  WHEN 0 = 1 THEN T4238.PROD_SUBCATEGORY ELSE 'MONTH' END  AS c1
  3  FROM
  4       SH.Products T4238;
 
Explained.
 
[email protected]>@?/rdbms/admin/utlxpls
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
-------------------
Plan hash VALUE: 3222145590
 
-------------------------------------------------------------------------------
| Id  | Operation          | Name     | ROWS  | Bytes | Cost (%CPU)| TIME     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |    72 |  1008 |     4  (25)| 00:00:01 |
|   1 |  SORT UNIQUE NOSORT|          |    72 |  1008 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| PRODUCTS |    72 |  1008 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
 
9 ROWS selected.

Then be cautious when you choose your presentation column, let's prefer the little table as possible or use this solution instead : OBIEE 10G - A special subject area to handle the dashboard parameters prompts

Advertising