SATYA RANKI REDDY OBIEE SOLUTIONS

OBIEE 11g Editing All Choices In Dashboard Prompt

 

 

Step1: Create a prompt under the Sql results use the following SQL

SELECT CASE WHEN 1=0 THEN Products.UPC ELSE 'All Items' END FROM Paint
UNION ALL
SELECT Products.UPC FROM Paint

a) Why we are using CASE WHEN 1=0 whether that condition is always false.Because the BI Server will refer to a table to fire query.If you doesn't give then it will give an error like

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 'All Items' FROM Paint UNION ALL SELECT Products.UPC FROM Paint

Step2:In the report you will get No results where UPC is equal to All Items

Step3: We are getting this because obiee (All choices) will pass null values.So NULL values in the obiee generated query we will not have any Where condition.

Step4:First Declare a Presentation Variable in Prompts after that in formula section (Fx) give this formula

CASE WHEN '@{UPC}'='All Items' THEN Products.UPC ELSE '@{UPC}' END

and make this column as IS Prompted


Step5:Now you are able to see the Results when it was "All Items"

Thanks

satya ranki Reddy