Showing posts with label Oracle Fusion BIP Report. Show all posts
Showing posts with label Oracle Fusion BIP Report. Show all posts

Tuesday, 3 November 2020

Oracle Fusion BIP Report - How to register custom BIP report as ESS Job

 

Purpose: In this session, we learn how to register custom BIP report as ESS Schedule Process.

Step1: Navigate to Fusion Application > Go to Setting and Actions > Setup and Maintenance


Step2: Select Task > Click on Search


Step3: Search panel enter Manage%Enter%Schedual%Job  > Search > Select "Manage Enterprise Scheduler Job Definitions and Job Sets for Financial, Supply Chain Management, and Related Applications"


Step4: Click on "+" button


Step5: Select Manage Job Definitions tab > 
- Enter Display Name = 'AP Invoice Report'
- Name = 'AP Invoice Report'
- Path = /oracle/apps/ess/custom/
- Description = 'AP Invoice Report'
- Job Application Name = FscmEss
- Job Type = 'BIPJobType'
- Report Id= /Custom/Demo/APInvoiceReport.xdo 
- Allow Multiple Pending Submissions = False


Step6: Navigate to Manage List of Values Sources tab > Click on '+' button

- Application = Payable
- User List of value source name = Invoice_Num
- Description = Invoice_Num
- LOV Type = User Defined
- SQL Query = Select your LOV query which you defined in Data Model LOV
- Click on Save and Close.


Step7: LOV created 


Step8: Navigate to Manage Job Definitions tab > Select bottom side 'AP Invoice Report: Parameter tab' > Click on "+" button


Step9: Enter Parameter details and assign LOV as per attached screenshots > Click on Save and Close


Step10: Now Close Save and Close Job Definition.


Step11: You can see now the ESS Job Created Successfully.


Step12: Navigate to Tools > Click on Schedule Process.


Step13: Search AP%Invoice%Report 

- Now we can able to see the registered report.


Step14: Select Parameter & Submit


Step15: You can see the ESS job Status is Succeeded.


Step16: You can download Report output from the output tab.




Monday, 2 November 2020

Oracle Fusion BIP Report : How to design RTF Template & Assign Template to Data Model & Design a Report

Purpose: In this session, we learn how to design new RTF template and how to attach to the data model 


Step1: Export Data XML file. You can see the XML output as per below image.




Step2: Open Word file and must check that you have pre-installed BI Publisher Add-Ins. If not installed then please install it first.


Step3: Now you have import downloaded XMl file Word. Navigate to BI Publisher Tab > Click on Sample XML > Select downloaded XML file > Click on Open Button.


Step4: Data Imported successfully.


Step5: Now click on Table Wizard > Select Table > Click on Next


Step6: Slect Data Set G_1 > Click on Next


Step7: Select column which you need to display in report layout and darg to right-hand side window using '>' button. Click on Next.


Step8: Click on Finish.


Step9: Now table column automatically populated.


Step10: For the testing report output need to select right-hand side Bi Publisher provided default layout panel and select excel type.


Step11: Testing output generated.


Step12: Now we need to prepare a report by using the Data Model and designed the RTF template. Click on left-hand side graph button and click on Report.


Step13: Click on cancel button which is populated on the screen.


Step14: Select on the search button for the select newly created data model.


Step15: Select the newly created Data Model from browser window. Click on OK.


Step16: Now click on upload button to upload RTF template.


Step17: Enter Template Name, Select RTF template from the browser, Select Layout Type as 'RTF', Select Language as 'English'.


Step18: Click on view list.


Step19: From this window, we can provide multiple types of the output type. like excel, pdf, RTF and HTML etc.


Step20: Save the report and click on view report button to test report.


Step21: Select parameter from LOV and click on Apply. You can able to see the output.



Oracle Fusion BIP Report: How to Create Data Model - LOV - Parameter - Assign LOV to Parameter


Step1: Creating a Data Model

-          Navigate to > BI Catalog > Click on Left-hand side top chart icon > Select Data Model

 

 

-          Click on ‘+’ icon  > SQL Query

 


-          Enter Name  = Data Set Name (i.e TEST)

-          Select Date Source = ApplicationDB_FSCM

-          Select Type of SQL = Standard SQL

 


 

-          Enter SQL Query.

-          For demo purpose, I have added ‘AP Invoice query’ in data set query.

-          Click on OK 


 

-          Navigate to : Data > Click  on View > Select Tree view (Default)

 


 

-          We can see the data in table format as well by selecting Table view

 

 

-          We can export data by click on the Export button (Data will be exported In XML format only)

-          We can save the view data as sample data by click on Save As  Sample Data button. It will help you to design an iterative layout.


 

 

Step2: Create LOV

-          For Create LOV:

-          Navigate to List of Value Tab > Click on ‘+’ icon

-          Enter Name, Select Type as ‘SQL Query’, Select Data Source as ‘ApplicationDB_FSCM’

-          Enter the SQL query based on what you need to prepare LOV.

-          We have added invoice number as LOV so we added Invoice Number query.

-          Save the data.

 


 

Step3: Create Parameter

-          For creating parameter we added bind invoice number variable (i.e p_invoice_number) in where clause of dataset SQL query.

-          Click on OK

 

 

-          Once you click on OK.

-          A System popup message will ask you to add newly added bind variable as a parameter.

-          Click on OK.

 


 

-          Once bind variable selected for creating as a parameter. The system will automatically create a parameter in the parameter tab.


Step4: Assign Newly Created LOV to Parameter

-          Once Parameter and LOV created we need to assign LOV in Parameter for run-time selection on value.

-          Select Parameter Type: Menu

-          Enter Display Label: Which we what to show the parameter label at the time of report execution.

-          List of Values: Select newly created LOV from the list.

-          Number of value to Display in List: Default value is 100

-          Can Select All: By Default Selected (If user not select value from LOV than it will pass all value or Null value as per radio button selection)

-          Null Value passed: By Default Selected Radio Button.


-          Once Parameter level LOv assigned. Navigate to Data tab.

-          Now we can able to see the Invoice Number parameter and attached LOV values.


 

-          Once you pass a specific parameter value and view the data, the system will fetch only specific data with respect to the passed parameter value.