How to limit values prompt dashboard values when using choice list values — Oracle Analytics

Oracle Transactional Business Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

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

How to limit values prompt dashboard values when using choice list values

Received Response
16
Views
3
Comments
Azul250
Azul250 Rank 1 - Community Starter
in Oracle Transactional Business Intelligence

Hello,

In a dashboard Prompt we are using column prompts we want to limit the values based on the business unit access for users.

The OTBI Dashboard is showing only the data based on data access

And i would like to have the same business unit in the LOV of the dashboard prompt not all the values;

Do you know how it's feasible ?

Thanks,

Answers

  • Rajasekhar Bandaru-Oracle
    Rajasekhar Bandaru-Oracle Rank 6 - Analytics Lead

    H Azul

    Please use session variables to restrict the data based on user access. Refer to the help center documentation link.

    https://docs.oracle.com/en/cloud/saas/sales/facaa/user-party-id-session-variable.html

  • Bhaskar Konar
    Bhaskar Konar Rank 8 - Analytics Strategist
    https://community.oracle.com/products/oracleanalytics/discussion/26054/how-to-limit-values-prompt-dashboard-values-when-using-choice-list-values

    Hi @Azul250,

    Welcome to the Oracle Analytics Community.

    While defining the column prompt you have to use the SQL to populate the values (also you can use the same SQL to make it a default value).

    Here's an example of the SQL.

    SELECT
    
   "Workforce Management - Worker Assignment Real Time"."Business Unit"."Business Unit Name" s_1,
    
FROM "Workforce Management - Worker Assignment Real Time"
    
WHERE
    
("Worker"."User Name" =  VALUEOF(NQ_SESSION."USER"))

    Hope this help.

    Thank you.

  • Richard Kemp-Eyre
    Richard Kemp-Eyre Rank 5 - Community Champion

    Hi @Azul250,

    We use this technique extensively in our dashboard and report prompts, as we have multiple Legal Entities and BU's in the same Fusion instance. As Bhaskar said in the note above, you would use SQL in the Column Prompt. In the screenshot below I have used it to select the BU for the Default Selection, but you could use the exact same SQL to populate the Choice List.

    One thing to note however is that in the User's role permissions they must have the permission to retrieve their own data otherwise when they run the SQL, the VALUEOF(NQ_SESSION."USER") will be NULL and nothing will be returned.

    SELECT "Business Unit"."Business Unit Name", DESCRIPTOR_IDOF("Business Unit"."Business Unit Name")
    FROM "Workforce Management - Worker Assignment Real Time"
    WHERE "Worker"."User Name" IN (VALUEOF(NQ_SESSION."USER"))

    image.png