OBIEE11g: How to create Prompts or Presentation Variables when using "DIRECT DATABASE REQUEST"

Scenario:

We need to create a report using the Direct Database Request and also have to create a prompt to dynamically enter the values by the users. We all know that Direct Database Request is nothing but writing the query which hits the database directly through the RPD Connection Pool. This post explains you how to create the prompts in the database query itself. Though it is almost similar to creating a prompt on the database query minor tweaking has to be done to append this to Obiee.

1)    Create one Dashborad prompt with presentation variable as shown in the below screenshot and assign a default value in this presentation variable.

then Click on Ok

Save the Prompt with the desired name.

2)    Click on New--->Select Analysis---> click on Create Direct data base request as shown in the below screenshot

Give the Connection pool name (It should match with the connection pool name in the RPD and no two connection pools should have the same name) and SQL statement as per requirement.

In the SQL query statement  pass the presentation variable name information as follows.

Query:

 select products.prod_id  ,products.prod_category , products.prod_name ,    sum(amount_sold) from products, sales  where products.prod_id=sales.prod_id and products.PROD_ID IN( @{VAR}{'13'}) group by products.prod_id  ,products.prod_category , products.prod_name

 3)    Now Create one Dashboard and place the report and Dashboard prompt on the  dashboard

4)    Now select the values from the prompt,click on apply and see the values on the report are changing as per the selected prompt values.