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
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"
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"
Posts
