Oracle Communities

Oracle Analytics and AI

Child Item

Ability to only return data based on user BU

Hello All

We have an analysis developed in OTBI that we want to make available to users across our Business Units.

However, we only want the data relating to the Business Unit of the user running the report to return.

Eg: Employee works for BU1, should only be able to see the data relating to BU1, and no data relating to BU2, BU3 and BU4.

Is it possible to ad a session variable to the report ( and if so, how please) to do this?

Thanks

Helen

Find more posts tagged with

Reporting

OTBI

Accepted answers

Richard Kemp-Eyre

Hello Helen,

We do this for our common reports where we want to limit the data for a particular user based their BU.

The easiest way is to set a report prompt and then in that prompt you select the Default BU to be retrieved based on the users Session Username. I use the following SQL:

SELECT "Business Unit"."Business Unit Name", DESCRIPTOR_IDOF("Business Unit"."Business Unit Name")

FROM "Workforce Management - Worker Assignment Real Time"

WHERE "Worker"."User Name" = VALUEOF(NQ_SESSION."USER")

Change the Subject Area depending on the one you need, though I find Subject Areas with Time dimension don't always return a value e.g. Absence or OTL. However you can always run the SQL based on the Worker Assignment Subject area in the prompt even though your main query is against another Subject Area (assuming it has the BU Dimension).

A couple of points:

Your users will need the Role access to the subject area
Your users' role will need to enable them to report on their own data, otherwise the SQL will return a NULL (after much thinking).

You can use this approach to return the selection of values in any prompt field.

Screenshot below of one of my OTBI reports and XML attached FYI (you'll need to tweak two filter values to run it yourself)

HR Workforce Report XML.txt

All comments

Richard Kemp-Eyre

Hello Helen,

We do this for our common reports where we want to limit the data for a particular user based their BU.

The easiest way is to set a report prompt and then in that prompt you select the Default BU to be retrieved based on the users Session Username. I use the following SQL:

SELECT "Business Unit"."Business Unit Name", DESCRIPTOR_IDOF("Business Unit"."Business Unit Name")

FROM "Workforce Management - Worker Assignment Real Time"

WHERE "Worker"."User Name" = VALUEOF(NQ_SESSION."USER")

A couple of points:

Your users will need the Role access to the subject area
Your users' role will need to enable them to report on their own data, otherwise the SQL will return a NULL (after much thinking).

You can use this approach to return the selection of values in any prompt field.

Screenshot below of one of my OTBI reports and XML attached FYI (you'll need to tweak two filter values to run it yourself)

HR Workforce Report XML.txt

Helen Thomas

Hi Richard,

Thank you so much for this explanation and example. Its just what we need.

Regards

Helen

Richard Kemp-Eyre

Hi Helen,

No problem. I've even used Logical SQL in the prompt SQL Results field to retrieve a default value. It's an easy way to control the values available to the report users.