Thursday, July 5, 2012

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

No comments:

Post a Comment