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.

Create Bind Variables from LDAP User Attribute Values

To bind user attribute values stored in your LDAP directory to a data query you can define the attribute names to Publisher to create the bind variables required.

Prerequisite

The attributes that can be used to create bind variables must be defined in the Security Configuration page by an administrator.

The attributes are defined in the Attribute Names for Data Query Bind Variables field of the LDAP Security Model definition. Any attribute defined for users can be used (for example: memberOf, sAMAccountName, primaryGroupID, mail).

How Publisher Constructs the Bind Variable

You can reference the attribute names that you enter in the Attribute Names for Data Query Bind Variables field of the LDAP Security Model definition in the query.

The following shows how bind variables are constructed:

xdo_<attribute name>

Assume that you've entered the sample attributes: memberOf, sAMAccountName, primaryGroupID, mail. These can then be used in a query as the following bind variables:

xdo_memberof
xdo_SAMACCOUNTNAME
xdo_primaryGroupID
xdo_mail

Note that the case of the attribute is ignored; however, the "xdo_" prefix must be lowercase.

Use these in a data model as follows:

SELECT
:xdo_user_name AS USER_NAME,
:xdo_user_roles AS USER_ROLES,
:xdo_user_ui_oracle_lang AS USER_UI_LANG,
:xdo_user_report_oracle_lang AS USER_REPORT_LANG,
:xdo_user_ui_locale AS USER_UI_LOCALE,
:xdo_user_report_locale AS USER_REPORT_LOCALE,
:xdo_SAMACCOUNTNAME AS SAMACCOUNTNAME,
:xdo_memberof as MEMBER_OF,
:xdo_primaryGroupID as PRIMARY_GROUP_ID,
:xdo_mail as MAIL
FROM DUAL

The LDAP bind variables return the values stored in the LDAP directory for the user that's logged in.