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

Question

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,

Richard Kemp-Eyre · Answer

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"))

Bhaskar Konar · Answer

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.

Rajasekhar Bandaru-Oracle · Answer

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