Learn BI Publisher Reports Online
Learn BI Publisher Reports Online

 5,486 total views

In this article I will guide you through learning BI Publisher Reports and some of the advanced features of BI Reports.

Links to the BI Publisher articles:

  • Adding hyperlink in Email using BI Bursting
    How to add hyperlink to email body message using bi publisher bursting? Have a look at this article to know more information.
  • Security Matrix – Users and their Roles
     991 total views For every implementation, there would be functional consultant who looks into the security and their job is to create a security matrix with all users list and their roles mapped on to a sheet. It would be tough to create this sheet manually when there are 1000’s of employees and it doesn’t make […]
  • Execute Reports from Navigator without exposing the BI Catalog
     855 total views In most of the cases, Employees will not have access to Reports and Analytics to access the reports. However if there is a requirement to create a report and provide access to employees, giving access to Reports and Analytics might expose other reports or seeded reports too, hence it is not a wise […]
  • How to modify XML tags in BI Publisher Output?
     742 total views There could be scenarios where in the client needs the report output in XML format and that XML file will be sent to some third-party vendor and it will be imported to their legacy systems for other processing. And the vendor might expect the XML file with some pre-defined tags for the attributes […]
  • Using Session Variables in BI Reports to get logged in user info
     1,462 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 […]
  • SQL Query to fetch Flattened Organization Hierarchy Tree
    If you have a requirement to fetch the flattened organization hierarchy tree you can use the SQL in this article to get the information.
  • SQL Query to get the HDL file from staging tables
     1,016 total views Normally when we load the data using HCM Data Loader aka HDL, the File Delete Option would be defaulted to ‘Y’ and the input zip file would be deleted post processing of that file. However out of my enthusiasm, I searched for options to get that HDL file from staging tables and finally […]
  • SQL Query to get User last login information
     2,118 total views Most of the customers want to know the list of users who logged in to Fusion Applications and want to encourage other employees too to use the Fusion system. For this, the customer needs to generate a report with the list of users who have logged in within a period of time like […]
  • Dynamic Date Parameters in BI Report
     1,239 total views For most of the BI Reports we have the input parameter as “AsOfDate” alias sysdate or current date… Sometimes we also have a date range “From Date” and “To Date” to fetch transactions between those dates. And these dates need to have a default value when we open the report/schedule the report.  Want […]
  • SQL Query to get Geography Information
     1,353 total views In this article we will look into getting the Country, State, County, City and Zipcode information from backend tables. These queries will be handy when there is a requirement to get the Country Name, State Names etc apart from the Country Code and State Code which are present in most of the address […]
  • Password Protect Excel Output in BI Publisher
     945 total views One of the missing piece in BI Publisher earlier was the password protection for Excel 2007 Output and it has been introduced in 18c update. Earlier password protection was possible for PDF output only and there have been a number of bugs and enhancements pending around the feasibility for password protection for Excel […]
  • Prevent Merging Columns in Excel Output using RTF template
     1,323 total views While creating a BI Publisher Report, we develop templates using RTF template as it provides multiple output formats like Interactive Mode, PDF, XLSX, XML, CSV etc which are not possible with any other templates like XLS, E-TEXT, etc. One problem most of the people face is when they try to download the excel output using […]
  • SQL Query to get Fast Formula and related info
     1,261 total views Below are the SQL’s to extract Fast Formula related data from underlying tables:Fast Formula Functions and their underlying Packages: SELECT f.BASE_FUNCTION_NAME ,f.description ,f.DEFINITION || ‘(‘ || listagg(fp.name || ‘ ‘ || decode(fp.class, ‘I’, ‘IN’, ‘O’, ‘OUT’) || ‘ ‘ || decode(fp.data_type, ‘N’, ‘NUMBER’, ‘T’, ‘VARCHAR2’, ‘D’, ‘DATE’), ‘,’) WITHIN GROUP (ORDER BY fp.sequence_number) […]
  • Most Frequently Used SQLs for List of Values (LOVs)
     1,521 total views Below are the list of SQLs that are most frequently used to get the list of values for business objects. That’s all for now…  Will keep updating this post with other queries as I want to maintain single point of reference for all List of Values. If you have any questions, please feel […]
  • Most frequently used Date, Number and String functions
    Do you know the most frequently used Date, Number and String functions in SQL query? Read on to know more details
  • Bursting attachment to Email along with html content in body
     801 total views Normally when we burst the BI Reports we either do the html bursting so that the content will appear in the email body or burst with an attachment so that the end user can open the pdf and view it. However if there is a requirement to combine both of them, then we […]
  • How to get the Payroll Balance details in BI Report?
     1,087 total views If the client has implemented Payroll, we have a common requirement to pull the balances in BI Report multiple times as part of third party integrations. Getting the Payroll Balance requires calling the balance package and providing the right inputs. Many of us get it wrong in writing the balances query and find […]
  • Scheduling BIP Report using SOAP Services?
     773 total views We can run the BI Reports ad-hoc, or schedule them to email/FTP at weekly/daily basis. However we will explore the option of scheduling the BIP Report using SOAP webservices. SOAP WSDL: https://{hostname}/xmlpserver/services/v2/ScheduleService?wsdl Sample: https://abcd-test.fa.us6.oraclecloud.com/xmlpserver/services/v2/ScheduleService?wsdl Once we have the WSDL, we can use the SOAPUI to consume this WSDL and execute the scheduleReport request with the […]
  • Etext template tips and tricks – Part 1
     2,196 total views In this article we will look into how to create etext template. When we have a requirement to generate a fixed length or delimited output files using BI Publisher or HCM Extract we will use the e-text template to achieve this requirement. I have come across a lot of people who are new […]
  • How to get Position Hierarchy in SQL Query?
     635 total views We can use the below query to get the Position Hierarchy of a Position traversing up to the top level. SQL Query: SELECT hap.POSITION_CODE ,hap.position_id ,hap.name ,(SELECT name FROM HR_ALL_POSITIONS WHERE POSITION_ID = pphf.PARENT_POSITION_ID ) Parent_Position_NAME ,decode(hap.ACTIVE_STATUS, ‘A’, ‘Active’, ‘I’, ‘Inactive’) ACTIVE_Status ,pj.manager_level ,pj.name FROM HR_ALL_POSITIONS hap ,PER_JOBS_F_VL PJ ,PER_POSITION_HIERARCHY_F pphf WHERE hap.POSITION_ID […]
  • SQL Query to get Source System IDs for all Business Objects
     2,029 total views In one of our previous articles, we have seen how to configure Source System Owner in Fusion.Defining Source System Owner for HDL in Fusion Source System Owner and Source System ID are the Source Keys which we use to load/update data using HCM Data Loader (HDL). HRC_INTEGRATION_KEY_MAP is the underlying base table which […]
  • Etext template tips and tricks – Part 2
    Do you know the functions that can be used in etext template? Have you faced issues with getting the sample functions to make them work?
  • Test/Preview/Troubleshoot eText templates
    Have you ever created etext template to create delimited or fixed length files? Have you faced issues with debugging the template with ????? characters on the output? Check out this article to get your questions clarified.
  • Fix for BI reports generating stale/old data due to Cache options
    Do you know why the BI caching is used? If you don’t know the BI Caching settings, you might end up getting old/stale data. Read through this article to know the caching settings and their importance.
  • Creating Excel templates and troubleshooting them
    Have you created Excel templates to get the excel output? Do you know the pros and cons of excel templates? If you face any issues do you know how to troubleshoot them?
  • SQL Query to fetch Supervisor Direct and Indirect reportees
     1,451 total views In this article we will look into getting in the Direct and Indirect reportees of a Supervisor using SQL Query. We assign the Supervisor on the Employment screen and they get stored in the PER_ASSIGNMENT_SUPERVISORS_F table. We will have to join the PER_ALL_PEOPLE_F, PER_ALL_ASSIGNMENTS_M and PER_ASSIGNMENT_SUPERVISORS_F tables to get the manager information of […]
  • Refresh Manager Hierarchy Process in Fusion HCM
    Do you know about the use of Refresh Manager Hierarchy and where it is used? Do you know it also updates the tables which are used for OTBI Subject Areas?
  • SQL Query to fetch employee person and assignment info
    Do you know what all tables are involved in creating an employee master report with personal and assignment data? Read on to know more details
  • Fix for SQL query time out error in BI Reports
    Do you know how to resolve the SQL query time exceeds limit of 500 sec issue? Do you know the SQL pruning feature? Read on to know more.
  • Creating Zipped PDF file output from BI Report
    Do you want the output of BI Publisher in zipped format? Do you think it is possible in Fusion? Read on to know more details.
  • Display Person Image on BI Report
    Do you know how to display Employee’s image on the BI Report? Images are stored as BLOB files, then how can we display them? Read on to knore more details.
  • SQL Query to get the Time in Job and Position
    Do you know how to get the Time in Job and Position information of an employee from the assignment information in Fusion HCM using BI Report? Read on to know more details
  • Configure FTP Server and use it in BI Report and Extract
    Do you know how and where to configure the FTP server in Fusion HCM? How to use the FTP option in BI Reports and HCM Extracts? Read on to know more details.
  • Resolve Folder not accessible in BI Catalog issue
    Do you know how to resolve the issue when a custom folder is not seen in BI Catalog due to permissions issue? Read on to know more details.
  • SQL Query to get Scheduled Process Job History
    Do you know how to get the Scheduled Processes history from underlying tables? How to get the BI Report Job History? Read on to know more details
  • Displaying Person Images in OTBI Analysis
    Do you know how to display Person Images in OTBI Analysis? Can we export the images to PDF? Read on to know more details.
  • Dynamic Output and Bursting for BI Report
    Do yo know how to achieve the dynamic output file name in BI Reports? Have you tried bursting feature to get the dynamic file name? Read on to know more details.
  • Migration of Objects – BI Reports
    Do you know to migrate the BI Reports from one instance to another using analytics screen and xmlpserver screen? Read on to know more details.
  • SQL Query to get Position and Job Valid Grades
    Do you know how to query the valid grades for Position and Job business objects? Read on to know more details.
  • SQL Query to get Payroll Element Entries
    Do you know how to write SQL Query to get the assignment level payroll element entries? Read on to know more details.
  • Variables and it’s usage in RTF template
    Do you know that we can use Variables in RTF template for calculations and running totals. Do you know the syntax and the samples on how to use them? Read on to know more details
  • Top 50 BI Publisher Reporting Interview Questions
    Do you know the Top interview questions on BI Publisher Reporting? Will you be able to answer these when asked during an interview? Read on to know more details.
  • SQL Query to fetch Cancelled Work Relationship and Reverse Termination
    Do you know that we can fetch the cancelled work relationship details and who has cancelled it and at what time the transaction has been done? Read on to know more details.
  • SQL Query to fetch the Document of Records
    Do you know the underlying tables which store the Document of Records Information? Will the file gets uploaded to UCM? Can we access it from UCM? Read on to know more details.
  • How to get list of all BI Reports from a folder/instance
    Do you know how to get the list of reports in a folder or get all custom reports list from an instance? Read on to know more details.
  • Auto Fit Contents in Excel output with RTF template in BIP
    Do you know how to set dynamic width for columns in excel output using RTF template? This works along with keep values in same column setting. Read on to know more details.
  • List of functions available in per_extract_utility package
    Do you know the list of available functions in per_extract_utility seeded pl/sql package? These functions can be used in sql query to avoid unnecessary joins to tables. Read on to know more details.
  • Get the info of Attachments loaded to UCM
    Do you know how to get the documents information that have been uploaded to UCM using SQL Query? Read on to know more details.
  • Access contents of UCM file using BI Report
    Do you know that you can get the contents of the text files uploaded to UCM in BI Report? Read on to know more details.
  • SQL Query to find pending approval transactions from Workflow tables
    Have you used the approval related tables to fetch the In Progress/Approved/Withdrawn transactions? Can you do another transaction on the business object which has an in process approval? Read on to know more details.
  • Deleted Data Report using Audit Functionality
    Do you know that you can fetch deleted data for business objects in Fusion if Auditing is turned ON? Read on to know more details.
  • SQL Query to find the Position Incumbents
    Do you know how to build query to find out the Position Incumbents? Position Incumbents are nothing but the employees hired into the Position. Read on to know more details.
  • SQL Query to find changes within a specific timeframe
    List of Functions to convert date to a different timezone and use them in SQL Query to get the required information. Read on to know more details.
  • Deep Links and their usage in BI/OTBI Reports
    Do you know how Deep Links work and what are the advantages of them? Can you use the Deep Links on BI/OTBI Reports? Read on to know more details
  • SQL Query to fetch Bank, Branches and External Bank Accounts
    Do you know how to extract the Banks, Bank Branches and External Bank Accounts? Read on to know more details.
  • Assignment tables PER_ALL_ASSIGNMENTS_M and _F differences
    Do you know which table to use while building query on assignment information? PER_ALL_ASSIGNMENTS_M or PER_ALL_ASSIGNMENTS_F or PER_ALL_ASSIGNMENTS_M_? Read on to know more details.
  • SQL Query to get the Dropped Dependents from Benefits Coverage
    Do you know how to create the BI Report to get the Dropped Dependents from Benefits Coverage? Read on to know more details.
  • How to use IF IF-ELSE OR AND conditions in RTF template BI Publisher
    List of methods in which the conditional operators IF, IF-ELSE, OR, AND, CHOOSE can be used in RTF template. Read on to know more details.
  • Skip Schedule using Schedule Triggers in BI Publisher
    Do you know Schedule Triggers in BI Report? What are the use cases and how to use the schedule triggers? Read on to know more details
  • Introduction to Seeded LDAP Request Reports
    Brief introduction about the seeded LDAP Request Dashboard and LDAP Request Information Reports. What’s the usage of them and what info does there reports provide? Read on to know more details.
  • Applying Fusion Application Data Security to BI Publisher Reports
    Do you know how to implement Data Security in BI Publisher Reports? What are Secured Views? Which info is considered as PII Info? Read on to know more details
  • SQL query to pull Work Schedules for Assignments/Legal Employers
    Do you know how to pull the Work Schedule Information for employees and legal employers? Read on to know more details.
  • SQL Queries to pull Action Type, Action, Action Reason and Action Reason Usages
    SQL Queries to pull Action Types, Actions, Action Reasons and Action Reason Usages. Read on to know more details.
  • Security Matrix with User and Role Mappings
    Do you know how to generate the Security Matrix Report at the click of a Button? What all features are present in this report? Read on to know more details.
  • Automated Parallel Processing technique for BI Report
    Do you know how to implement Parallel processing for BI Report? How to use “Submit Another Task”? Read on to know more details.

The above list keeps refreshing whenever latest content is being added on BI Publisher. Keep checking for latest updates.

Join our fastest growing technology social groups to interact with experts in the Fusion space and get your queries resolved – Telegram group, Facebook Page and LinkedIn Group

Sricharan

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.

Leave a Reply