Thursday, February 18, 2010

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.
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 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. 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 ‘’ 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.


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.




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 !


  1. Hi ,

    Can you pls provide the process for using the multi sources to merge into single report in BI Publisher with an example would be of great help.


  2. Sure, I'll write about the topic after I come back from my vacation!

  3. Hi, when i click in the link, it show me the following message Invalid parameters requested.

  4. Hi,

    I am trying to use charts in RTF templates and then run it as a concurrent program in EBS12.
    If I use the chart object in the RTF the report completes successfully but the chart is not displayed in the concurrent program output.
    If I use a image object to display the chart then the report completes with warning and the following message exists in the OPP log:
    Caused by: oracle.xdo.parser.v2.XPathException: Extension function error: Error invoking 'chart_svg':'java.lang.NoClassDefFoundError: Could not initialize class sun.awt.X11GraphicsEnvironment'

    If I run the RTF template standalone then RTF works correctly. I am using Word2007 and desktop BI Publisher. Any help would be great

  5. I don't know why you're getting such error, I'd recommend you to raise your question at a BI Publisher discussion forum.

  6. Nishida,
    Can you please provide the xml and respective rtf's and that will really help us to understand this particular implementation

  7. When I put ISO, I am getting Error. prepare query failed[nQSError: 27005] Unresolved column: "ISO".

    Please help

  8. When I paste the url in the hyperlink property of word, the url gets cut off after certain lenght. Is there any other way i can paste my entire url in the word dpcument?

  9. How can I achieve that when a user clicks on the Hyperlink for the detail report it opens in the same window but in another tab where the master report is?

  10. hi everyone ,

    Am new 2 BIP , wanted 2 have a Drill Down report/Hyperlink to other Report.
    have 2 Qry One display the Summary , thru the Drilldown/hyperlink it will display the Summary report.
    Should i Create 2 data model & how 2 link these 2

    Please help me out !

  11. Can we print link value in reports?

  12. Nishida,

    Could you provide an example of a combination of table and matrix in one report

  13. Thq for the info man.. it is very much helpful to me

  14. Thanks for the information. This is very much helpful.

    I am facing a issue, while creating the hyperlink i was getting error as the url has more than 255 chars in its, even after replacing with the CURRENT_SERVER_URL. Is there any work around for this ?

  15. When i click on the link it prompts to give an input value, instead i want the report to be displayed on clicking the link. could you please assist.

  16. When i click on the link it prompts to give an input value, instead i want the report to be displayed on clicking the link. could you please assist.

  17. I want to accept user parameters in drill down report

  18. you can use 'xdo_user_name' which would return the current user name.

  19. Hello Kan,

    Could you please let me know how to do this in 11g BI Publisher. We are using Oracle Layout Builder and for data columns there is a URL property. Can this be used. More over how do you pass other filter values. Like in my first report there are some parameter and after they are selected the reports are generated thus those information should pass to the child report to produce proper data.


  20. Somnath,

    Did you find a way to accomplish this in BIP 11g? We need to do the very same thing and cannot get the URL to property to pass properly.


  21. In 11g, you need to use something like the below.


    where '/DATA/G_BUGDB/BUGNO' is an XML path that you can find in Data Model editor.

    1. Hi

      In 11g, i have given url like the following:


      i have passed parameter like '%7bBALANCE_DATE%7d' . but value is not passing.


  22. this is working fine but the problem is data contains '&' symbol it is not working as it is taking the value before '&' and it is leaving the data after '&' so report is not showing results

    for example:
    area = newyork&CO

    it is taking just newyork and it is not taking &CO
    because of this it is not showing result in report

  23. Hi Kan,

    I have an issue with a similar functionality.

    The Master report Hyperlink links to the detail report correctl But I am not able to get this to open in a new window even though the option to open in New Window is checked.

    Your help will be very useful.. I have other reports in the same environment which work perfectly fine and open in a new window.

    What am I missing here..