Leveraging Session and System Variables in Oracle Cloud ERP BI Publisher Reports
In Oracle Fusion Cloud Applications, when creating Business Intelligence (BI) Publisher reports, session and system variables are powerful tools that enable you to personalize report data dynamically based on the logged-in user. These variables give you the ability to tailor reports with user-specific information such as usernames, roles, languages, and other session attributes, ensuring the output is relevant and secure.
What Are Session and System Variables?
Session and system variables are special placeholders that BI Publisher automatically sets or that can be called from Oracle Fusion Middleware to reflect details about the current user and their session context. These include user IDs, assigned roles, locale settings, and even LDAP attributes when configured.
Important BI Publisher User System Variables
Some of the most commonly used system variables in BI Publisher for Oracle Cloud ERP include:
xdo_user_name: The username of the user running the report (e.g.,administrator).xdo_user_roles: A comma-separated list of roles assigned to the user (e.g.,XMLP_ADMIN, XMLP_SCHEDULER).xdo_user_report_oracle_lang: The report language from user preferences (e.g.,US, DE, FR orNL).xdo_user_report_locale: The user’s report locale (e.g.,en-US).xdo_user_ui_oracle_lang: The user interface language preference.xdo_user_ui_locale: The user interface locale.
Important BI Publisher User Session Variables
Some of the most commonly used session variables in BI Publisher for Oracle Fusion Cloud Applications include:
HRC_SESSION_UTIL.GET_USER_PERSONID: Logged in User Person ID
FND_GLOBAL.USER_NAME: Logged in User Username
FND_GLOBAL.USER_GUID: Logged in User GUID
USERENV(‘SESSIONID’): Logged in User Session ID
USERENV(‘LANG’): Logged in User Language
SYS_CONTEXT(‘USERENV’,‘LANG’): Logged in User Language
Why Use These Variables?
Using these variables allows you to:
Personalise report content based on user attributes.
Filter data dynamically so that each user sees only records relevant to their role or assignment.
Localise reports automatically by applying user-specific language and locale settings.
Incorporate user-related information seamlessly in report headers, footers, or data sections.
How to Include User System Variables in Your BI Publisher Data Model
To leverage these variables in your reports, define them as parameters in your BI Publisher data model and reference them with a colon : prefix in your SQL queries or templates.
Example SQL snippet:
SELECT :xdo_user_name AS USER_ID,
:xdo_user_roles AS USER_ROLES,
:xdo_user_report_oracle_lang AS REPORT_LANGUAGE,
:xdo_user_report_locale AS REPORT_LOCALE,
:xdo_user_ui_oracle_lang AS UI_LANGUAGE,
:xdo_user_ui_locale AS UI_LOCALE
FROM dual;
This query returns the current user’s session details as part of the dataset, which you can display or use for conditional logic in your report.
Sample Use Case: Data Filtering by User Name
Consider a report that returns user details only for the logged-in user:
SELECT PerUsers.USERNAME,
PerUsers.USER_ID,
PerUsers.USER_GUID,
PerUsers.START_DATE,
PerUsers.END_DATE,
:xdo_user_name AS XDO_USER_NAME
FROM per_users PerUsers
WHERE LOWER(PerUsers.username) = :xdo_user_name;
Unlike system variables, session variables do not require a colon : prefix in your SQL queries.
Example SQL snippet:
SELECT PerUsers.USERNAME,
PerUsers.USER_ID,
PerUsers.USER_GUID,
PerUsers.START_DATE,
PerUsers.END_DATE,
fnd_global.user_guid AS FND_GLOBAL_USER_NAME
FROM per_users PerUsers
WHERE PerUsers.username = fnd_global.user_guid
This query ensures that the user only sees data related to themselves, enforcing row-level security at the reporting layer.
Summary
Session and system variables in Oracle Fusion Cloud Applications BI Publisher reports are invaluable for creating dynamic, secure, and personalised reporting experiences. By incorporating these variables into your data models and templates, you enable per-user data filtering, support localisation, and integrate user context seamlessly.
When designing reports, ensure that you leverage these variables to enhance security, relevance, and user engagement.
Need some assistance?
Ready to bring clarity to customer, supplier and employee communications? With BI Publisher, we specialise in customising customer, supplier, and employee-facing documents such as invoices, purchase orders, payslips, and statements to meet your exact branding, compliance, and operational needs. From layout design to data logic enhancements, we help you deliver clear, consistent, and impactful outputs across your organization. Contact us today to discover how our expertise in BI Publisher can streamline your document processes and elevate your business communications.
