OBIEE 10G - A special subject area to handle the dashboard parameters prompts

Saw Object


All the prompts in OBIEE are column based prompt. You can't create a list if you don't select a column. And as it's the only way to set up a variable for a formula, it's impossible to bypass this constraint.

In our case, we don't care about the value in the dimension column as we want parameter values as for instance the hierarchy level :

Obiee Parameter Prompt Result

The idea behind the scene is :

  1. to create a column which is on the top of a dual view (or skinny table) in the physical layer.
  2. to create a dummy business model because BI Server requires us to have a minimal of one logical join.
  3. to create a special subject area (presentation catalogue) to separate the functional of the parameters columns
  4. to be able to choose this column in order to create one or several others parameter prompts.

The advantages of this solutions are that :

  • as you query a skinny table (or dummy table view), the performance are very good
  • as you use a column, you can translate in other languages the label of the parameter prompt
  • you can add a parameter value without changing the original dashboard prompt

Creation of the Parameter Table

In this example, we show the creation of a parameter table with a dummy table view but you can choose to create a table to store your parameters.

  • In the physical layer, right click on a physical schema and choose New Physical Table
  • select “Select” as Table Type from the drop down menu
  • and enter the Sql below

Example :


Obiee Dummy Table

Second, you need to create the column report_level from the sql statement.

  • click on the column tab and create it

Obiee Dummy Table Columns

If you choose this column in a query, the values MONTH and QUARTER will be returned.

Creation of the Business Model Layer

  • Drag and drop TWO times this table in order to create two logical table source :
    • Parameters1
    • Parameters2
  • Select this two tables,
  • Right click / Business Model Diagramm / Selected Tables only

Obiee Dummy Business Model

  • and create a new complex join between them.

Obiee Dummy Model 2

This step is important beacause Obiee require that a fact table has minimum one complex logical join

Creation of the Presentation Catalog

  • Right click in the presentation layer / New Presentation Catalog and name it Parameter
  • Just drag and drop one of the two logical table and you are done.
  • You can cache this new presentation catalog to the others users by giving the correct privilege. In the Oracle BI Presentation Dashboard / Administration / Manage Privileges.
  • Reload the metadata in the BI Presentation Dashboard in Administration / Reload Files and metadata

Obiee Parameter Subject Privilege

In the picture above, you can see that only the Presentation Server Administrator have the right to the Parameter Area but everybody can use a object (report, prompt) create with it

Creation of the dashboard prompt

Now that we have our parameter column in our parameter subject area, it's possible to choose it in order to create a parameter prompt.

  • Go to answer
  • Click on the icon : New Dashboard prompt on the left side
  • And select the subject area Parameter

Obiee New Dashboard Prompt

  • in the left frame, select the column of the select table (in our case report_level)
  • and define the options that you need as the setting of a presentation variable

Obiee Parameter Prompt

  • and you get :

Obiee Parameter Prompt Result

What about now if I don't want MONTH and QUARTER but MONTH and YEAR ?

You can change the values in your parameter table or you can use the SQL value capabilities of the prompt :

  • Go back to your prompt
  • select SQL Value in the show result
  • and tape this SQL :

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

You can not enter this SQL :


because Parameter 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 :

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 Parameter 


As we use a dual or a skinny table as source table, the performance are really good.


You can add to your parameter subject area one column by parameter or you can change the values with the SQL capabilities from the prompt. In the two case, a sql statement against a little table of Oracle is performed and doesn't therefore require to read data by an I/O operation on a disk.

Discover More
Obiee Parameter Prompt Result
OBIEE - Dashboard parameter prompt - The WHEN 1=0 or ifnull solution

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...
Obiee Dashboard Prompt Parameter
OBIEE 10G - How to create a prompt to handle a parameter ?

A variable parameter is a variable that is not dependent upon a column. In 10g, all the prompts in OBIEE are column based prompt. In 11g, you can use the variable prompt To create a parameter prompt,...

Share this page:
Follow us:
Task Runner