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.
Articles Related
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.
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.
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.
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
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).
sh@orcl>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.
sh@orcl>@?/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