Using Session Variables in BI Reports to get logged in user info
Using Session Variables in BI Reports to get logged in user info

 1,970 total views

Session variables are used frequently in BI Publisher when the reports are being exposed to employees to add data security on top of the existing query.

We need to write query to pull only the logged in user information and for this we will use the session variable. Apart from this user, there are many other session variables and we are going to look at them now.

Logged in User Person ID – HRC_SESSION_UTIL.get_user_personid​
Logged in User Username – :xdo_user_name
Logged in User Username – FND_GLOBAL.USER_NAME
Logged in User GUID – fnd_global.user_guid
Logged in User SessionID – userenv(‘sessionid’)
Logged in User Language – USERENV (‘LANG’)
Logged in User Business Group – fnd_profile.value(‘PER_BUSINESS_GROUP_ID’)

You can add these to the SQL Query as per your requirement and get appropriate results.

If you have any questions, please feel free to reach out to me by posting in comments section.

If you are interested in learning Fusion Technical tools go through this post

If you liked the article, please share it with your friends/ colleagues/ teammates or anyone who might also benefit from it.

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

This Post Has 2 Comments

  1. Zahid Rahim

    How to use these session variable in BI Publisher DataModel Query:
    Like How to write this in SQL Query
    person_id = VALUEOF(NQ_SESSION.”PERSON_ID_HCM”)

  2. Sricharan

    Use “Logged in User Person ID – HRC_SESSION_UTIL.get_user_personid​”.. The one you mentioned does not work in BI Reports.. It works for OTBI. Not too sure if PERSON_ID_HCM is valid for OTBI too.

Leave a Reply