Business Intelligence – Oracle

Oracle BI EE – Custom Drill Path Navigation

Posted by Venkatakrishnan J on October 24, 2007

One of the common questions that we typically get in the forums is how do we enable multiple custom drill paths. For example, check the forum entry here wherein the user wanted to have multiple drill paths. The user himself came up with an answer which i actually had forgotten about. Its a very interesting way of creating multiple drill paths. I thought i would blog about both the methods(including the one that i had suggested) as this might prove useful for others who want to have custom drill paths.

Lets first take an example. I will take the same example as was asked in the forum entry above. The rephrased question for the sake of simplicity is “How do we enable users to traverse the entire drill hierarchy? For example, if we have Year->Quarter->Month->Day, how can we make the users jump from Year to Month or even Year to Day directly”. Well, this is a pretty straightforward request but this capability is not available out of the box using OBI EE So, lets take the first approach of using column selector views. Lets start with creating a sample report with Brand, Region and Sales. We will include the Period dimension once this report has been created.


Lets pull in the Year column into the report and go into the Column Selector view. In this view add both Month and Week columns as shown below. We are basically creating a column selector view with Year, Month and Week columns so that we have the flexibility of selecting any one of them as they are part of the same hierarchy.


Include this column selector view into the compound layout. Now we have achieved the flexibility have both the custom drills and choosing any drill levels in a hierarchy. Its basically a column selector view implementation but used for select hierarchical attributes.


Lets look at the second method wherein for each drill path we will be creating a new report. Then we will be adding these reports as navigation targets for the Year column. So, when clicking on year we would basicaly have all the reports displayed as drill paths that we can choose to achieve the necessary drills. This method is not that flexible in the sense that one would have to keep on adding new reports for each and every drill path. For our example, in addition to the base report having Year, Region, Brand and Sales as columns, we would be creating 2 more reports. First report would have Year, Month, Region, Brand and Sales as columns to achieve Year->Month drill. Second report would have Year, Month, Week, Region, Brand and Sales as columns to achieve the Year->Month->Week drill. For both these reports create a criteria on Year with is prompted clause.


Once this is done. Go to the Year Column properties of the base report and add these 2 reports as the navigation targets.


So basically when we click on Year now we would get 2 drill paths having the report names that we just created.


Of course, there are also other methods depending on specific requirements. But the above should give a basic idea if you have a similar requirement.

8 Responses to “Oracle BI EE – Custom Drill Path Navigation”

  1. Remc0 said

    Hi J,

    Thanks for blogging out this subject (I was the one from the forum ;))

    I see the second option is more beautiful to see for the end user, but this is not an option for us because of the amount of reports we have to build.
    Thanks anyway!

    Best Regards,


  2. Vikram Takkar said

    Hi Venkatakrishnan,

    Custom Drill down can also be implemented in Repository level by specifying the “Preferred Drill Path”.

    It can be done by following steps:

    1: Right Click the Level from which you want to drill down to other dimension.
    2: Click Properties.
    3: Select “Preffered Drill Path” tab.
    4: Click Add and select the level you want to drill down.

    Vikram Takkar

  3. Venkatakrishnan J said

    Good point Vikram. Navigation drills give more flexibility for end users as they are not aware of the actuall drill hierarchies. And again they would be bound by what is set in the hierarchy of the RPD. Sometimes Preferred Drill paths would more than be sufficient depending on the requirements

  4. Vikram Takkar said

    yes Venkatakrishnan, But dont you think it is very difficult to make no. of reports for navigation drill down.

    anyhow everything depends on the client requirements… thanks 🙂

  5. Vikram Takkar said

    Hi I have one question… If we use second method and place the report in the dashboard then the “Drill in Place” feature given by the Dashboard can not be used . Even if you click that Drill in place reports does not drill in place as it is navigating.

    My client want to make it look like drill in place.

    I know Iframe can be one of the methods but with iframe problem is that if report size increases it gives scroll bars and client doesnot want it.

    Can you suggest me other method.

    Thanks & Regards
    Vikram Takkar

  6. Alex said

    If you have a composite key in a level (ex: COD_CITY, DESC_CITY), after drill down to this level, Dashboard shows the 2 fields in an unpredictable order. In the repository, both on logical layer and presentation layer, the order of the fields is the same: first the code and then the description , in all levels of all dimensions. But on dashboard, after drill down, for some levels a report shows first code and then description, for others first description and then code.
    Do you know hoh to force the order?

  7. Ultrecht said

    Hi, you can do this in two ways. First if this fields are in logical table then you can create columns in reverse order of placement in report. So if you want to have column1, column2 when you dril then create in order column2, column1. Antoher way is to use nqudmlgen and nqudmlexec. All you need is to convert repository into txt format, then search for table with columns you want to sort. Column are sort with order that you create them you need to change this order. The first declared column is the last in report, so if in repository column are sorted column1, column2 then on report will be column2, column1. If you change order of columns then you need to change the number attribute of this column to. They are sorted from lower to higher “AD_3900:29611239353351”, “AD_3900:29621239353351”. If you change order of columns you need to change this attributes to. Sorry for my bad english i hope i can help you.

  8. […] Oracle BI EE – Custom Drill Path Navigation page_revision: 5, last_edited: 1241680862|%e %b %Y, %H:%M %Z (%O ago) edittags history files print site tools+ options edit sections append backlinks view source parent block rename delete help | terms of service | privacy | report a bug | flag as objectionable Hosted by — get your free wiki now! Unless stated otherwise Content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License Click here to edit contents of this page. Click here to toggle editing of individual sections of the page (if possible). Watch headings for an “edit” link when available. Append content without editing the whole page source. Check out how this page has evolved in the past. If you want to discuss contents of this page – this is the easiest way to do it. View and manage file attachments for this page. A few useful tools to manage this Site. See pages that link to and include this page. Change the name (also URL address, possibly the category) of the page. View wiki source for this page without editing. View/set parent page (used for creating breadcrumbs and structured layout). Notify administrators if there is objectionable content in this page. Something does not work as expected? Find out what you can do. General documentation and help section. Terms of Service – what you can, what you should not etc. Privacy Policy. _uff = false; _uacct = “UA-68540-5″; _udn=””; urchinTracker(); _qoptions={ qacct:”p-edL3gsnUjJzw-” }; […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: