BI Publisher Drill Down Report| How to create a hyperlink in BIP Report

BI Publisher Drill Down Report: Sometimes you may need to create a hyperlink from one Oracle BIP report to another. This feature is also called as Drill down reports in BI Publisher.

For example you have a report which list out all the vendors and other details , and you have another report which shows the products sold by these vendors. Now you may have a requirement that when you click on the vendor name on first report , the second report should be executed and it should list out all the products sold by that particular supplier.

This feature can be achieved by creating a drill down report in BIP.

How to create a Drill-down report in BI publisher

Lets take our example of drilling down from Vendors report to Vendor items report

There are 3 steps to the process

1- Create the vendor name as a hyperlink

2- create the second report and parameterized the report.

3. In the hyperlink of the first report call the parameterized report passing a parameter as the vendor name

1. Create the vendor name as a hyperlink

In the RTF of the Oracle BI Report modify the vendor name form field

This is the code to be added in RTF Form element (party name) which will be hyperlink and passing parameter. Put this link in Add help text advanced tab.

<fo:basic-link color="blue" external-destination="https://yourdomain.com/xmlpserver/~biadmin/Vendor Items Report.xdo?supplier_name={PARTY_NAME}"> <?PARTY_NAME?>
</fo:basic-link>

2- Create the second report and parameterized the report.

The Target report should have a parameter to accept the parameter from the calling report (party name)

Data model query from Item list report ( target report)

select distinct
psl.VENDOR_ID,
hzp.PARTY_NUMBER,
hzp.PARTY_NAME,
egb.item_number,
etl.description,
egb.INVENTORY_ITEM_STATUS_CODE
from 
PO_APPROVED_SUPPLIER_LIST psl,
POZ_SUPPLIERS ps,
HZ_PARTIES hzp,
egp_system_items_b egb,
EGP_SYSTEM_ITEMS_TL etl
where
 psl.vendor_id=ps.vendor_id
  and ps.party_id=hzp.party_id
AND psl.item_id=egb.inventory_item_id
and egb.inventory_item_id=etl.inventory_item_id
and etl.language='US'
 and psl.prc_bu_id=300000046987012
and hzp.PARTY_NAME =nvl(:Supplier_name,hzp.PARTY_NAME)
order by hzp.PARTY_NUMBER asc

3. In the hyperlink of the first report call the parameterized report passing a parameter as the vendor name

Note the Calling syntax

https://yourdomain.com/xmlpserver/~biadmin/Vendor Items Report.xdo?supplier_name={PARTY_NAME}

The parameter to be passed is to be in flower brackets and in CAPITALS ( as in RTF)

Reference Oracle MOS Doc (BI Publisher – How To Create An Active Dynamic URL In RTF Template (Doc ID 2232195.1)

FAQs on BI Publisher Drill Down Reports and Hyperlinks

You may like Bursting in BIP

More fusion apps tech