You are here: BI Publisher Designing BI Publisher Drill down Report using RTF Template

Designing BI Publisher Drill down Report using RTF Template

Written by  Sridhar Sunchu
Rate this item
(5 votes)

In this blog I am going to explain how we can use links in BIP report to open another BIP report with dynamic values.

For example:

Report1: In this report we have purchase details of goods in all states in a country.

Report2: Displays City level purchase details for a state and this will be the target report.

By using Report1 we are going to generate Report2 dynamically by passing parameters.


 Let’s create a report to display country regions in the first report and countries within country region in the second report. Here the second report is generated based upon the link we click for the country region.

In the below table we have country regions and their respective country names.

Country Region

Country Name

Africa

South Africa

Asia

China

Asia

India

Asia

Japan

Asia

Malaysia

Asia

Singapore

Australia

Australia

Australia

New Zealand

Eastern Europe

Poland

Middle East

Saudi Arabia

Northern America

Canada

Northern America

United States of America

Southern America

Argentina

Southern America

Brazil

Western Europe

Denmark

Western Europe

France

Western Europe

Germany

Western Europe

Ireland

Western Europe

Italy

Western Europe

Spain

Western Europe

The Netherlands

Western Europe

Turkey

Western Europe

United Kingdom

Building Report1: Country_Region

In my case I am using SH schema for getting all the details required.

SELECT DISTINCT COUNTRY_SUBREGION FROM COUNTRIES ORDER BY 1;

Now generate XML(DATA) for this report.

Build the RTF using the XML.

We completed Simple RTF Template.

 Output

To create link we need to have Target Report as ‘Country Name’

We create target report ‘Country Name’ following the steps similar to report1 and add a parameter. Let’s just call the paramater as “P_COUNTRY_REGION”.

Now generate the XML

Create RTF for Report2: Country_Name. Once the RTF is completed upload into BIP Template Section.

Run the report and check if it works fine by passing the parameter:

Now, we need to generate link to the report or document. In the report on right top corner you can find “Link to this report” Click on it. Depending upon your requirement, select the link. Here I am using Document Only option.

Here is the generated Link

http://localhost:9704/xmlpserver/Country_Name/Country_Name.xdo?
_xpf=&_xpt=0&_xdo=%2FCountry_Name%2FCountry_Name.xdo
&P_COUNTRY_REGION=Asia_xt=Country_Name&_xf=html&_xmode=4

In the above link you can find P_COUNTRY_REGION=Asia which is the parameter we used previously as “Asia”. Now take this link and use this in Report1.

Before using this we need to make few modifications to the url which is as follows:

For using Dynamic Parameters:

We want to dynamically click on a value in report 1 and get the corresponding details in report 2. For this we take the name of the parameter “P_COUNTRY_REGION” in the link and use these brackets ‘{‘, ‘}’ instead of the BI Publisher’s tag ‘<??>’. Hence, the link will look as follows from my local:

http://localhost:9704/xmlpserver/Country_Name/Country_Name.xdo?
_xpf=&_xpt=0&_xdo=%2FCountry_Name%2FCountry_Name.xdo
&P_COUNTRY_REGION={COUNTRY_SUBREGION}
&_xt=Country_Name&_xf=html&_xmode=4

Get the hostname, port number, and the root path dynamically

When we run our report we will not have a constant host name always. There might be different server instances like ‘Development’, ‘Testing’, ‘Production’, etc. There is a pre-defined BI Publisher’s variable called ‘CURRENT_SERVER_URL’, which returns an URL that contains hostname, port number, and the root path. So it would be “http://localhost:9704/xmlpserver” for the above example.

So now you can replace the URL to be something like:

{$CURRENT_SERVER_URL}Country_Name/Country_Name.xdo?
_xpf=&_xpt=0&_xdo=%2FCountry_Name%2FCountry_Name.xdo
&P_COUNTRY_REGION={COUNTRY_SUBREGION}
&_xt=Country_Name&_xf=html&_xmode=4

In order to use the ‘CURRENT_SERVER_URL’ variable you need to declare it in the RTF template. So, you need to just type the following syntax at the beginning.

<?param@begin:CURRENT_SERVER_URL?>

Now add the url in RTF

Upload the RTF into BIP Template Section and run the report.

Output.

When you click the link the output would be.

Africa

 

Asia

"If you found this article useful, please rate the same"

If you need any assistance in BI Publisher implementation or RTF Template design, we can help you out. please send your query to  This e-mail address is being protected from spambots. You need JavaScript enabled to view it

Read 14382 times

1 comment

Leave a comment

Make sure you enter the (*) required information where indicated.
Basic HTML code is allowed.