In this article we will look into adding the Person Image to BI Report.
Want to learn Fusion Technical tools? Checkout this article
Some of the clients want to create user friendly reports with employee images and use the bursting feature to address it to employees on Birthday’s or anniversaries. Some of the clients display the complete profile information of an employee along with the employee photo.
let’s create a simple Report to have the employee picture displayed on the output.
Employee images get stored in PER_IMAGES in BLOB field and the image can’t been seen as such in the data model.
SQL Query to get the images information from PER_IMAGES table:
SELECT A.PERSON_NUMBER, B.DISPLAY_NAME, C.IMAGE FROM PER_ALL_PEOPLE_F A, PER_PERSON_NAMES_F B, PER_IMAGES C WHERE A.PERSON_ID = B.PERSON_ID AND B.NAME_TYPE='GLOBAL' AND A.PERSON_ID = C.PERSON_ID
When you run this SQL Query in the Data Model, you will get the output like below:
Now click on export to download the xml file and create a BI template with these fields.
Once you add all the fields into the template using the Table Wizard, the XML tag for the Image field will look as below:
With this template, we will get the BLOB output that we saw on the data model screen only. However our requirement is to capture the employee image on the Output and not this BLOB output.
Let’s update the tag for this IMAGE field to:
<?if:IMAGE!=' '?> <fo:instream-foreign-object content-type="image/jpg" height="1 in" width="1 in"><?IMAGE?></fo:instream-foreign-object> <?end if?>
This code will convert the BLOB text to image.
Just double click IMAGE filed.. go to Advanced tab and copy paste the above code and click OK. Save the template and upload it to BI Report.
Final output would be like below:
However if the employee count is huge and you try to run the report for all employees at once, you might get an error that “Report data size (2667550510 bytes) exceeds the maximum limit (2147483648 bytes). Stopped processing.” In such cases add some filters by Legal Entity, Business Unit, Department, Job etc to get your requirement achieved.
Tip: We can use the same approach for any BI Report which needs to use Person Images on the output. There are some settings in the code that you can change to change the height and width of the image that is being displayed.
Top 10 Popular Posts from our Knowledgebase
- DataFusing FREE Cloud based SQL Developer like tool for Oracle Cloud
- Core HR Entity Relationship Diagram (ERD) for HCM Cloud
- Most Frequently used Document IDs from Oracle Support
- FREE Oracle Training & Accreditation with Oracle Learning Explorer
- How HDL Transformation Formula works with ElementEntry Sample
- FREE Oracle Fusion Demo Instance (VISION) Access
- Top 10 FREE useful tools for Fusion Consultants
- Learning Fusion HCM Technical Skillset
- Sample HCM Data Loader (HDL) files for all Business Objects
- Most frequently used Tables list in Fusion HCM