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 BurstingHow 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 RolesFor 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 any sense…
- Execute Reports from Navigator without exposing the BI CatalogIn 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 decision to…
- How to modify XML tags in BI Publisher Output?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 for them…
- Using Session Variables in BI Reports to get logged in user infoSession 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…
- SQL Query to fetch Flattened Organization Hierarchy TreeIf 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 tablesNormally 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 figured out…
- SQL Query to get User last login informationMost 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 last 30days…
- How to default date parameters in BI Report dynamically?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. If the scheduled…
- SQL Query to get Geography InformationIn 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 tables. Countries:SELECT…
- Password Protect Excel Output in BI PublisherOne 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 Output. And…
- Prevent Merging Columns in Excel Output using RTF templateWhile 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 RTF template,…
- SQL Query to get Fast Formula and related infoBelow 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)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 free to…
- Most frequently used Date, Number and String functionsDo 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 bodyNormally 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 can use…
- How to get the Payroll Balance details in BI Report?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 it difficult…
- Scheduling BIP Report using SOAP Services?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 below payload.…
- Etext template tips and tricks Part 1In 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 to etext…
- How to get Position Hierarchy in SQL Query?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 = pphf.POSITION_ID…
- SQL Query to get Source System IDs for all Business ObjectsIn 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 stores the…
- Etext template tips and tricks Part 2Do 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 templatesHave 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 optionsDo 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 themHave 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 reporteesIn 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 an employee.…
- Refresh Manager Hierarchy Process in Fusion HCMDo 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 infoDo 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 ReportsDo 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 ReportDo 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 ReportDo 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 PositionDo 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 ExtractDo 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 issueDo 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 HistoryDo 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 AnalysisDo 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 ReportDo 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 ReportsDo 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 GradesDo 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 EntriesDo you know how to write SQL Query to get the assignment level payroll element entries? Read on to know more details.
- Variables and its usage in RTF templateDo 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 QuestionsDo 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 TerminationDo 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 RecordsDo 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/instanceDo 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 BIPDo 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 packageDo 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 UCMDo 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 ReportDo 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 tablesHave 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 FunctionalityDo 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 IncumbentsDo 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 timeframeList 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 ReportsDo 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 AccountsDo 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 differencesDo 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 CoverageDo 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 PublisherList 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 PublisherDo 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 ReportsBrief 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 ReportsDo 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 EmployersDo 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 UsagesSQL Queries to pull Action Types, Actions, Action Reasons and Action Reason Usages. Read on to know more details.
- Security Matrix with User and Role MappingsDo 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 ReportDo you know how to implement Parallel processing for BI Report? How to use “Submit Another Task”? Read on to know more details.
- Using Dummy Bursting Query in BI Report to achieve dynamic filenameDo you know how to generate dynamic filename for a BI Report? Read on to know more details
- Adding External PDF attachments to bursted PDF outputDo you know that you can add external attachments to the existing bursted PDF output? Where should we place that external file and how to do it? Read on to know more details.
- Run BI Publisher Report from Custom Payroll Flow PatternDo you know that BI Publisher Reports can be run from Payroll Flow Patterns? Can we pass input parameters dynamically? Can other tasks set the parameters for BI Report task? Read on to know more details.
- Passing Message Tokens for Messages in BI Report for Dynamic NotificationDo you know that you can pass values for Message Tokens in the BI Publisher Report? What are the advantages of this approach? Read on to know more details.
- BI Publisher Audit and Execution ReportsDo you know how to audit BI Publisher Reports to see who ran the reports and how much time it took? Read on to know more details.
- Solve manual FTP server update issue for HCM Extracts post every P2T refreshTired of manually updating HCM Extracts after every P2T refresh? Do you know there is an automated solution for it? Read on to know more details.
- DataFusing FREE Cloud based SQL Developer like tool for Oracle CloudSharing another wonderful FREE Cloud based SQL developer like tool for Oracle Cloud. It’s simple, secure and easy to use. Check it out.
- How to deal with Datetimes stored in Database as UTC timezone?Do you know that all datetime fields will be stored in database with UTC timezone? How do you convert datetime to different timezone? Read on to know more details.
- How to get the LDG associated with Legal entity?Do you know how to fetch the Legislative Data Group Info for a Legal Employer? Where are they tagged to each other? Read on to know more details
- What is the alternative for DATA_CACHE which is being deprecated from 21A?Do you know that DATA_CACHE column is being deprecated from 21A? Are you aware of the alternative to it? If you don’t modify existing reports, then they won’t work post 21A. Read on to know more details.
- Accessing Reports in My Folders of other users (Locked/No more exist)Do you know how to access the content of locked users from their My Folders in BI Catalog? Read on to know more details.
- Customize Notification Templates and solve issue with Refresh TimeDo you know how to customize BI Approval notification templates? Have you faced issues with the template not getting reflected? Read on to know more details.
- Query for Rehire Recommendation FlagDo you know that you can set the Rehire Recommendation flag while terminating an employee? Read on to know more details
- Oracle HCM Cloud Data Dictionary TablesDo you know that Data Dictionary tables exists in Oracle HCM Cloud? Read on to know more details.
- How to generate CSV output from BI Reports/HCM Extracts?Do you know how to generate CSV output from BI Report/ HCM Extracts? Do you know all possible ways of doing it? Read on to know more details.
- How to Resolve issues with User-Level Report Timezone on BI Report?Do you know why some reports show a day difference when you run the report? how to resolve such issues? Read on to know more details.
- How to use Regular Expressions in SQL Query?Do you know how to use regular expressions in SQL Queries? Read on to know more details.
- SQL Query to get the Object Group EmployeesDo you know how to pull employees assigned to an object group? Read on to know more detaisls.
- How to skip the blank line at the end of the output file using etextDo you know how to skip the blank line at the end of file using etext template? Do you think it is possible? Read on to know more details.
- How to Create BI Report using static CSV fileDo you that you can create report based on a static CSV file? You can also integrate the static data with other data sets. Read on to know more details.
- How to Create BI Report using static Excel file .xlsDo you know that you can create BI Report based out of a static Excel File from local machine? What would be the use cases and how to do it? Read on to know more details.
- SQL Query to get the modified fields from the Assignment table using Audit functionalityDo you know how to get the updated/ created/ deleted fields from Audit tables when auditing is enabled? Read on to know more details.
- SQL Connect: A Robust alternative to TOAD and SQL Developer for Oracle CloudDo you know that there is a robust alternative to TOAD/SQL Developer for Oracle Cloud? Read on to know more details on SQLConnect.
- SQL Query to get the Payroll Name, Time Card Required Flag from Payroll Relationship ScreenDo you know how to get the Payroll Relationship, Payroll Name and Time Card Required Flag from Payroll Relationship Screen? Read on to know more details.
- SQL Query to pull the Payroll Balance FeedsDo you know how to extract the Payroll Balance Feed details for a balance? Read on to know more details.
- Personal Payment Method, its importance and query for Active EmployeesDo you know what is personal payment method and how to query the personal payment method details for active employees? Read on to know more details.
- SQL Query to get Unique and Active Line Managers NamesDo you know how to fetch the unique and active line managers? Can we have that as a list of values for a DFF? Read on to know more details.
- How to Export Jobs from one POD to another using HDL?Do you know how to export the Job Business object into HDL which can be used to import Jobs into target POD? Read on to know more details.
- SQL Query to fetch ORC Candidate Job Application StatusDo you know how to fetch the Candidate Job Application Status in ORC? What are the different Phases/States for a candidate Job Application? Read on to know more details
- How to Reverse Terminate employees using HDL?Do you know how to reverse terminate employees using HDL? Can you use SQL Query to generate the HDL file for reverse termination? Read on to know more details.
- How to display HTML formatted text using HTML2FO function and RTF template?Do you know how to display the HTML formatted text on the output using RTF template? How to change the font and size for HTML formatted text? Read on to know more details.
- How to Download and install BI Publisher Plugin for MS Word and Excel?Do you know how to install the BI Publisher Plugin for Windows for MS Word and Excel? What else is provided with installation? Read on to know more details.
- SQL Query to extract Business objects and attributes enabled for AuditingDo you know how to extract the Business Objects/Attributes configured for auditing? Read on to know more details.
- How to add Hyperlink to the RTF template?Do you know how to add the hyperlink to fields on RTF template? Read on to know more details.
- SQL Query to identify person relationship with OrganizationDo you know how to get the Person’s relationship with the organization? Candidate, Employee, Ex-Employee, Pending Worker, etc. 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.