Include User Information Stored in System Variables in Your Report Data

Your report data model can include information about the current user that's stored in system variables.

The user information is stored in system variables as described below.

System Variable	Description
xdo_user_name	User ID of the user submitting the report. For example: Administrator
xdo_user_roles	Roles assigned to the user submitting the report. For example: XMLP_ADMIN, XMLP_SCHEDULER
xdo_user_report_oracle_lang	Report language from the user's account preferences. For example: ZHS
xdo_user_report_locale	Report locale from the user's account preferences. For example: en-US
xdo_user_ui_oracle_lang	User interface language from the user's account preferences. For example: US
xdo_user_ui_locale	User interface locale from the user's account preferences. For example: en-US

Publisher populates the system variables in an online report. In a scheduled job, Publisher doesn’t populate the XDO_USER_REPORT_LOCALE, XDO_USER_UI_LOCALE, XDO_USER_UI_ORACLE_LANG, XDO_USER_REPORT_ORACLE_LANG, and XDO_USER_REPORT_LOCALE system variables.

Add the User System Variables as Elements

To add the user information to the data model, you can define the variables as parameters and then define the parameter value as an element in your data model.

You can also simply add the variables as parameters then reference the parameter values in your report.

The following query:

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

returns the following results:

<?xml version="1.0" encoding="UTF-8"?>
<! - Generated by Publisher - >
<DATA_DS>
<G_1>
<USER_ROLES>XMLP_TEMPLATE_DESIGNER, XMLP_DEVELOPER, XMLP_ANALYZER_EXCEL, XMLP_ADMIN, XMLP_ANALYZER_ONLINE, XMLP_SCHEDULER </USER_ROLES>
<REPORT_LANGUAGE>US</REPORT_LANGUAGE>
<REPORT_LOCALE>en_US</REPORT_LOCALE>
<UI_LANGUAGE>US</UI_LANGUAGE>
<UI_LOCALE>en_US</UI_LOCALE>
<USER_ID>administrator</USER_ID>
</G_1>
</DATA_DS>

Sample Use Case: Limit the Returned Dataset by User ID

The following example limits the data returned by the user ID.

select EMPLOYEES.LAST_NAME as LAST_NAME,
  EMPLOYEES.PHONE_NUMBER as PHONE_NUMBER,
  EMPLOYEES.HIRE_DATE as HIRE_DATE,
 :xdo_user_name as USERID 
from   HR.EMPLOYEES EMPLOYEES
where lower(EMPLOYEES.LAST_NAME) = :xdo_user_name

Notice the use of the lower() function, the xdo_user_name is always be in lowercase format. Publisher doesn't have a USERID so you must use the user name and either use it directly in the query; or alternatively you could query against a lookup table to find a user id.