Description
Oracle Business Intelligence Publisher (BI Publisher, formerly XML Publisher) is an enterprise reporting solution that streamlines report and form generation. With a single template, it can generate reports in many formats (PDF, RTF, Excel, HTML, and so on) in many languages.
How to Prepare the BI Reports by using the Simple SQL Query
Please find the below setups for Preparing the BI Reports.
How to find out the Basic table information in Fusion applications
Search the Google “oracle enterprise repository.”
Or Use the URL: http://www.oracle.com/webfolder/technetwork/docs/HTML/oer-redirect.html.
Once we can search the “oracle enterprise repository” or use the above URL, it will display the below image
Once we can click on the Financial tab, it will display the version of the Oracle. Here I am selecting the Version-Release R12
Once Click on the Version-Release R12 the below screen page will open
Same page we can scroll down we can find the Tables and Views for Oracle Financial Cloud
Once you click the Tables and Views, the below screen page will open.
In the below screen, we can select the AP_INVOICES_ALL table name.
From AP_Invoices_ALl table I am selecting the data like INVOICE_AMOUNT, INVOICE_NUM, INVOICE_DATE
Step 1: Login as Application User
Step 2: you will be on the home page as below.
Step 3: we need to navigate to Functional Setup Manager, click on User Name, and you will be able to see Setup and Maintenance option. It will navigate you to functional setup manager.
Step 4: below is the functional setup manager window; we could able to see all the offerings as provisioned and enabled
Step 5: we could see Financial’s Offering as provisioned and enabled; we need to click on the Implementation projects button in the below screenshot.
Step 6: Search for our Project in the search window.
Step 7: Click on the project name (hyperlink), it will open the project page
Go to the Fusion applications Navigator and select Reports and Analytics.
It will display the below screen and Click on Browse Catalog Icon which is highlighted in Yellow Colour.
Right side Click on the Expand the New tab and select the Data Model.
After click on Data Model below screen image will open and then click on Diagram and then select the SQL Query
Once we can click on SQL Query, it will display the below image and enter the Name of the Report and write the SQL Query
In the below screen I am Creating the Invoice data like for Invoice date, amount and Number
Query is Select INVOICE_AMOUNT, INVOICE_NUM, INVOICE_DATE from AP_Invoices_ALL, and then click on Ok.
After Click on Ok it will display the below Screen image and View Data
In the below screen we can select the number of rows to display in the report, and we can view the report in either Table view or Tree view and then click on Save as sample data and then Create the Report.
First, we need to save the data has a Save as Sample Data.
Once We can click on Save as Sample Data, we will get confirmation message
Once Data is saved Sample data we can Save symbol which is Highlighted in Yellow color
Enter the Report name and description and save the report in respective folder and click ok
Click on Create Report Which is highlighted in Yellow Color.
Select the Report layout like Table, Chart, Pivot Table and Click on Next
Here we can drag and drop the report how we want the report. Here I can select the report Invoice num, Invoice Date and Invoice Amount
If we want to show the Invoice Grand Total, we can enable the checkbox Show Grand Totals Row
Click on Next. It will display the below image.
We can save the report in Respective Folder and click on ok.
Report is display like this. If we want we can save this report or Print
Here we can use the Report Output Format.
Summary
This post detailed how to prepare the BI Reports by using simple sql queries in fusion application.
Queries?
Do
How to Prepare the BI Reports in Oracle Fusion Applications
Description
Oracle Business Intelligence Publisher (BI Publisher, formerly XML Publisher) is an enterprise reporting solution that streamlines report and form generation. With a single template, it can generate reports in many formats (PDF, RTF, Excel, HTML, and so on) in many languages.
How to Prepare the BI Reports by using the Simple SQL Query
Please find the below setups for Preparing the BI Reports.
How to find out the Basic table information in Fusion applications
Search the Google “oracle enterprise repository.”
Or Use the URL: http://www.oracle.com/webfolder/technetwork/docs/HTML/oer-redirect.html.
Once we can search the “oracle enterprise repository” or use the above URL, it will display the below image
Once we can click on the Financial tab, it will display the version of the Oracle. Here I am selecting the Version-Release R12
Once Click on the Version-Release R12 the below screen page will open
Same page we can scroll down we can find the Tables and Views for Oracle Financial Cloud
Once you click the Tables and Views, the below screen page will open.
In the below screen, we can select the AP_INVOICES_ALL table name.
From AP_Invoices_ALl table I am selecting the data like INVOICE_AMOUNT, INVOICE_NUM, INVOICE_DATE
Step 1: Login as Application User
Step 2: you will be on the home page as below.
Step 3: we need to navigate to Functional Setup Manager, click on User Name, and you will be able to see Setup and Maintenance option. It will navigate you to functional setup manager.
Step 4: below is the functional setup manager window; we could able to see all the offerings as provisioned and enabled
Step 5: we could see Financial’s Offering as provisioned and enabled; we need to click on the Implementation projects button in the below screenshot.
Step 6: Search for our Project in the search window.
Step 7: Click on the project name (hyperlink), it will open the project page
Go to the Fusion applications Navigator and select Reports and Analytics.
It will display the below screen and Click on Browse Catalog Icon which is highlighted in Yellow Colour.
Right side Click on the Expand the New tab and select the Data Model.
After click on Data Model below screen image will open and then click on Diagram and then select the SQL Query
Once we can click on SQL Query, it will display the below image and enter the Name of the Report and write the SQL Query
In the below screen I am Creating the Invoice data like for Invoice date, amount and Number
Query is Select INVOICE_AMOUNT, INVOICE_NUM, INVOICE_DATE from AP_Invoices_ALL, and then click on Ok.
After Click on Ok it will display the below Screen image and View Data
In the below screen we can select the number of rows to display in the report, and we can view the report in either Table view or Tree view and then click on Save as sample data and then Create the Report.
First, we need to save the data has a Save as Sample Data.
Once We can click on Save as Sample Data, we will get confirmation message
Once Data is saved Sample data we can Save symbol which is Highlighted in Yellow color
Enter the Report name and description and save the report in respective folder and click ok
Click on Create Report Which is highlighted in Yellow Color.
Select the Report layout like Table, Chart, Pivot Table and Click on Next
Here we can drag and drop the report how we want the report. Here I can select the report Invoice num, Invoice Date and Invoice Amount
If we want to show the Invoice Grand Total, we can enable the checkbox Show Grand Totals Row
Click on Next. It will display the below image.
We can save the report in Respective Folder and click on ok.
Report is display like this. If we want we can save this report or Print
Here we can use the Report Output Format.
Summary
This post detailed how to prepare the BI Reports by using simple sql queries in fusion application.
Queries?
Do drop a note by writing us at doyen.ebiz@gmail.comor use the comment section below to ask your questions.
