Using Presentation Variable in Dashboard Prompt

User743281-Oracle

I want to use a presentation variable in a SQL expression to set the choice list values of a dashboard prompt.

The requirement: one prompt on one page of the dashboard and it needs to be used in a second prompt on another page. The two prompt are from different subject areas. Thus I can not use the option "Limit values by" for second prompt.

I tried a simplified case where both prompts on the same dashboard page. The environment is 11G. Here are the steps:

- Create a column prompt 'Employee Login' and set to presentation variable 'EmpoyeeLogin'.

- Create second promp 'Name' and set Choise List Values as SQL Results. For the SQL statement, I put the following

SELECT "Employee"."Employee Name" FROM "Sales - CRM Pipeline"  WHERE "Employee"."Employee Login"='@{EmployeeLogin}' FETCH FIRST 65001 ROWS ONLY

I am not getting the desired result with these expressions. The choice-list values of second prompt seems to return all the values for employee name. I searched on this topic in oracle community. It seems several people have similar question. But I could not find an answer.  Where am I going wrong? Can I not use a presentation variable in a where clause in this manner?

Sherry George

Your test case should work. Could you add some screenshots. Remember that you have to hit 'Apply' button of the first prompt to have the result reflected in the second prompt. (you can of course remove the apply button if required)

User743281-Oracle

Hi Sherry,

Thank you for your reply. The issue is intermittent. 

When I tested it yesterday, I did hit the apply button but it never worked. But it worked this morning and returned the filtered value as expected. However, when I tried again this afternoon without change anything. It does not work by returning all the values of employee names.