nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Help with Development of Payroll GL Report in BIP

Jared Custer

Good Day,

We're preparing for go live at the end of this month on Oracle Fusion Cloud. Our team is learning OTBI and BIP in preparation for this, and I have a question related to the development of a BIP report to return payroll dollars and hours with employee (i.e. Employee ID, Employee Name, Job Code, etc.) and general ledger (i.e. Company/Cost Center/Account) detail. I've attempted to use various tables such as PAY_RUN_BALANCES, PAY_COSTS, PAY_ELEMENT_ENTRIES_F, and other but have been unable to use and/or link them successfully to return the correct results. Has anyone created something similar before and could provide some guidance in terms of the tables to be used? Perhaps even have some related SQL? Any assistance would be greatly appreciated. Thank you!

Jared

Find more posts tagged with

BIP

Reporting

Table

Payroll

Accepted answers

All comments

Mallikarjuna Kuppauru-Oracle

Hi @ jcuster13

Can you share me your product version details.?

Regards,

Arjun

Jared Custer

Hello Mallikarjuna,

Thank you for the quick reply. I'm placing my product version details just below.

"Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production

Version 19.20.0.0.0"

Jared

Mallikarjuna Kuppauru-Oracle

HI @jcuster13

Oracle Fusion Application Cloud version?

Regards,

Arjun

Renuka Nuguru-Oracle

Hello Jared,

What i understood, you are using Fusion SaaS OTBI and BIP reporting.

I would recommend you to go through out of the box report delivered with the product.

Below given path contains out of the box reports for Payroll for OTBI and BIP reports and data models.

/Shared Folders/Human Capital Management/Payroll/

Below path contains delivered OTBI reports and analysis.

/Shared Folders/Human Capital Management/Payroll/Transactional Analysis Samples

=================

Below link provides good documentation on the reports, subject areas, security, data dictionary and more.

Take advantage of sample reports
Review data lineage mapping which provides the mapping from subject areas till the database table columns.
Review Prebuilt Analytics and Reports View Human Resources reports

https://docs.oracle.com/en/cloud/saas/human-resources/24a/analyze-and-report.html

Thanks,

Renuka

Jared Custer

@Mallikarjuna Kuppauru-Oracle Thanks for your reply. Here is the information you requested.

Oracle Fusion Cloud Applications

23D (11.13.23.10.0)

Jared Custer

@Renuka Nuguru-Oracle Thanks for your reply and suggestions. I have looked at the resources you suggested, but it's still not clear to me how to go about creating this report in BIP. I created it in OTBI, but ultimately determined I could not use OTBI because the output is greater than the 75K row output limit for data only csv publishing. The challenge is the bringing together the General Ledger information (i.e. Account, Cost Center, Entity) with the Employee Level Data (i.e. Employee ID, Employee Name, Job Code, etc.).

Renuka Nuguru-Oracle

If you can get OTBI report working as per your requirement.

You can pull physical SQL from OTBI analysis report and create a data model with the physical sql. Schedule the report instead of running BIP report online.

Navigate to Administration -> Manage Session. Locate the username for the analysis in question, select View Log and paste the log into Notepad.

How to pull physical sql of OTBI analysis report?

Click on the navigate to "My Profile" > "Administration" from the upper right portion of the screen.

Navigate to Session Management > Manage Sessions

Scroll to bottom of page and locate the USER session. Confirm the Analysis Path which you executed the report, Click 'View Log'

Paste the entire log into Notepad (which retains better formatting than other Editors) and check that you see “sending query to database” which will show the physical SQL being sent.

You can modify your physical sql as per your requirement and create data model in BIP and check the data and schedule the report.

Thanks,

Renuka

Jared Custer

Hello @Renuka Nuguru-Oracle Thank you for the guidance. I followed the steps provided and was able to get the log file. I also documented and distributed to the team here for use and reference. That said, there are multiple sections in the log which are prefixed by "Sending query to database" and some of the scripting doesn't look like SQL to me. Should the '</Parameters>' and '<Projection>' rows be included in any SQL being included in a Data Model?

Renuka Nuguru-Oracle

Hello,

Create a simple report to understand the physical sql from the log file and upload simple log and also your main report log. I can guide you where to look for the physical sql.

I first start with the main sql where you have the physical sql, create data model and test the results, apply hardcoded filters in the where clause to minimize the data. Later you can add parameters and pass the bind variables to the where clause of the main SQL.

Create Pixel-Perfect Reports documentation

https://docs.oracle.com/en/cloud/saas/otbi/create-manage-pixel-perfect-reports.html

Customer connect for HCM reporting sharing center

https://docs.oracle.com/en/cloud/saas/human-resources/24a/fahca/how-you-take-advantage-of-sample-reports.html#s20066684

Thanks...