Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 212 Oracle Analytics News
- 42 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Help with Development of Payroll GL Report in BIP
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
Answers
-
0
-
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
0 -
0
-
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.
Log in to your BI environment and go to Catalog ...../analytics
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
Thanks,
Renuka
0 -
@Mallikarjuna Kuppauru-Oracle Thanks for your reply. Here is the information you requested.
Oracle Fusion Cloud Applications
23D (11.13.23.10.0)
0 -
@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.).
1 -
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
0 -
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?
0 -
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
Customer connect for HCM reporting sharing center
Thanks...
1
