Session Variable Multiple Values in Default Initializer

Question

Hello,

i have created a session variable in the rpd for the purpose of defaulting a department dashboard prompt for, a select set of users, to their appropriate departments that they are responsible for.

My question is; if the session variable returns no rows, is there a way to set the default initializer to select all the values in the department dashboard prompt? I have only been successful in setting the default initializer as single department value.

Thanks

Gianni Ceresa · Answer

WITH tmp AS (
select department
from employee_department
where sup_full_id =UPPER('User1')
)
select department from tmp
union
select department from employee_department 
where 0 = (select count(*) from tmp)

Something like that is supposed to work : if you have rows for 'User1' it returns you a DISTINCT on that list, if nothing there it returns you all the values (with a distinct as well thanks to the UNION ).

Thomas Dodds · Answer

You need an initblock to fill in a flag for a user with a default if they don't have that requirement, then you need another initblock to evaluate that flag, and then fill in the row-wise variable with either the limited list or the full list.

Kielc26 · Answer

Hi,

Thanks for your response. Ideally yes I would try to take care of this within the init block itself but i am having trouble doing so.

Here is the query to generate the departments for 1 of these 5 users responsible department;

select distinct department

from employee_department

where sup_full_id =UPPER('User1')

RESULTS:

Department 1

All other users would see would need to default to all the departments. The query would look like something below;

select DISTINCT department

from employee department

RESULTS:

Department 1

Department 2

Department 3

Soo on and so forth.

Thanks

Kiel