Session Variable Multiple Values in Default Initializer — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Session Variable Multiple Values in Default Initializer

Received Response
2
Views
5
Comments
Kielc26
Kielc26 Rank 1 - Community Starter
edited Aug 13, 2024 7:09PM in Oracle Analytics Cloud and Server

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

Answers

  • Felipe_Idalgo
    Felipe_Idalgo Rank 5 - Community Champion

    Hi,

    Try to test your variable directly on prompt like this:

    Ifnull(NQ_SESSION.YOUR_VAR,<YOUR_COLUMN_WITH_ALL_VALUES>)

    Felipe Idalgo

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi,

    why to not manage it in the query populating the variable? If there is no value then you return all the values?

  • Kielc26
    Kielc26 Rank 1 - Community Starter

    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

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    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.

  • Gianni Ceresa
    Gianni Ceresa Mod 
    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 ).