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 :
The idea behind the scene is :
The advantages of this solutions are that :
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.
Example :
SELECT 'MONTH' AS REPORT_LEVEL FROM DUAL
UNION
SELECT 'QUARTER' AS REPORT_LEVEL FROM DUAL
Second, you need to create the column report_level from the sql statement.
If you choose this column in a query, the values MONTH and QUARTER will be returned.
This step is important beacause Obiee require that a fact table has minimum one complex logical join
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
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.
You can change the values in your parameter table or you can use the SQL value capabilities of the prompt :
SELECT CASE WHEN 1=0 THEN Parameters.REPORT_LEVEL ELSE 'MONTH' END FROM Parameter
UNION
SELECT CASE WHEN 1=0 THEN Parameters.REPORT_LEVEL ELSE 'YEAR' END FROM Parameter
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 :
SELECT 'MONTH' FROM Parameter
UNION
SELECT 'YEAR' FROM Parameter
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 :
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 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.