Drill Down to Detail or Another Report

I want to click some values in the report and see the detail report for the particular data. For example, when I see a Sales report and realize some regions sale revenue is way small and now I want to find something more detail to answer why, by opening up another report that has detail data for the particular regions.

Well, this is sometimes called as ‘Drill Down’, sometimes ‘Report Link’, or ‘Reports Navigation’. Whatever the name is, what you want is so called ‘Master-Detail’ reports relationship with which a master report opens a detail report and passes a value to filter the data in the detail report.

You can develop such reports very easily with BI Publisher. All you need to do is to develop two reports, one regular report with a hyper link and one with a parameter which will filter the data.

As I covered yesterday you can receive the parameter value and use it within the RTF template to filter the data. But I’m going to pass the parameter value from the first report to a SQL query for the second report and have the SQL query (Database) to filter the data for the second report.

So here are the steps we need to follow.

Create Detail Report with Parameter

Since we need to know the target report name and parameter value in order to create the hyperlink in the master report, let’s start with the detail report.

First you create a report as normal, nothing special. And you add a parameter. Let’s just call it as ‘p_country’.

And put that parameter into a query for the report.

My example is using a LOV (List of Values) to get the ‘p_country’ parameter value list. And the report looks like the below.

Now you can select a value from the country parameter drop down list and the report data will be updated based on your selection.

Create Report with Hyperlink

Now the master report. Again, nothing special, you just create a report as normal. Only additional thing is to add a hyperlink. In order to do this you need to know the two things.

• Target (Detail) Report Path
• Target (Detail) Report Parameter

For that, we need to go back to the detail report. And select ‘Current Page’ from ‘Link to this report’ menu at the right hand side top.

Copy the link into a notepad or something. Mine is something like below.

http://knishida-lap.us.oracle.com:9704/bip/Data/Trend by Country/Trend by Country.xdo?_xpf=&_xpt=0&_xdo=%2FData%2FTrend%20by%20Country%2FTrend%20by%20Country.xdo&p_country=USA&_xt=Trend%20Chart&_xf=html&_xmode=0

You can find the detail of the each parameter in the above link in BI Publisher’s Designer’s guide. But for now what we need to know at least is the report path and the report parameter. And those are ‘Report Path’ and ‘Report Parameter’. You can find those as below from the above link.

Report Path	http://knishida-lap.us.oracle.com:9704/bip/Data/Trend by Country/Trend by Country.xdo
Report Parameter	p_country

Now you go back to the first report, open the RTF template and add the hyperlink on the place you want your users to click on. 

And use the MS Word’s menu to insert a link.

And you want to copy the URL link that you copied above. But before dump it into the hyper link box, you want to modify the link a little bit.

There is a ‘p_country’ parameter in the link. Instead of hard coding its value you want to dynamically generate the the value by taking an actual value from the data when the report is generated. It’s basically the same thing as you do when you print or use the data in the RTF template, like <?ISO?>. Only the difference is you need to use these brackets ‘{‘, ‘}’ in stead of the BI Publisher’s tag ‘<??>’. With my report I can get the value as ‘ISO’ from the xml data, so it would be something like below.

http://knishida-lap.us.oracle.com:9704/bip/Data/Trend by Country/Trend by Country.xdo?_xpf=&_xpt=0&_xdo=%2FData%2FTrend%20by%20Country%2FTrend%20by%20Country.xdo&p_country={ISO}&_xt=Trend%20Chart&_xf=html&_xmode=0

Now one more thing. You never know what would be the host name when the report will be deployed, right? You might have 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://knishida-lap.us.oracle.com:9704/bip/’ for the above example.

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

{$CURRENT_SERVER_URL}Data/Trend by Country/Trend by Country.xdo?_xpf=&_xpt=0&_xdo=%2FData%2FTrend%20by%20Country%2FTrend%20by%20Country.xdo&p_country={ISO}&_xt=Trend%20Chart&_xf=html&_xmode=0

And now you can copy this and paste it into the hyper link box.

And here is one thing. 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?>

And that’s all you need to do. Once you upload the RTF template you should be able to see the hyper link in the report. And the link should contain the report path and report parameter value, both of which are dynamically generated.

As you see in the above picture, each link has the ‘CURRENT_SERVER_URL’ and ‘ISO’ values dynamically generated. With my example, when I click the link for Japan it will open another report and show me the detail data about Japan.

Or,

 

So that’s pretty much it, though one last thing I should add. If you have tried this you must have already noticed. When you click the link it will open a new window. But you might want to keep in the same window to display the detail data. You can do this by un-checking ‘Open Links in New Window’ property.

Enjoy Drill Down/linking !