Tuesday, December 13, 2022

ERP - Create BI reports with Parameters and Dependent LOVs

Usecase: Here, we will create a BI report with parameters and also create dependent LOVs. We will fetch invoice_num,source and description from ap_invoices_all table using p_source and p_inv_num parameters and here, LOV of invoice num will be depended on the parameter p_source.

logic steps:

  • Create 2 parameters - p_source and p_inv_num which will be pointing to the following 2 LOVs
    • p_source => source_lov
    • p_inv_num => inv_num_lov
  • Create 2 LOVs where inv_num_lov depended on p_source
    • source_lov = select distinct source from ap_invoives_all
    • inv_num_lov = select invoice_num from ap_invoices_all where source =:p_source
  • Create data set using sql query
    • Select invoice_num,source, description from ap_invoices_all where source=nvl(:p_source,source) and invoice_num=nvl(:p_inv_num,invoice_num)
  • Create report based on this data model.

Detailed steps with screenshots:

Navigation >> Tools >> Reports and Analytics >> Browse Catalog >> create >> Data Model

Parameters >> Create 2 parameters(initially with text type)

List of Values >> create 2 LOVs,

Parameters >> update paramter type with Menu and select the respective LOVs.

Data sets >> Click + sign >> SQL Query

Provide Name, data source and SQL query.

Click data

Select the source and dependend invoice >> view
Note: if you want to create custom RTF template, export the data. 

Save as sample data >> save data model

Create report

Cancel >> select Data model >> generate RTF

Save the report name

Save Report >> View Report


We can also export data.

No comments:

Post a Comment

Featured Post

11g to 12c OSB projects migration points

1. Export 11g OSB code and import in 12c Jdeveloper. Steps to import OSB project in Jdeveloper:   File⇾Import⇾Service Bus Resources⇾ Se...