Jul 07 2010

Want To Remove Drill Indicators? Use A Variable…

Categories: Report Techniques, Variables! Dave Rathbun @ 7:38 am

In some cases I have created reports that were in drill mode that were not really drillable. For example, I showed a technique a few years back that would let a report developer swap measures out on a chart using a drill filter. It has been working great for quite a few years now. However, as a side effect any dimension on the report blocks that participates in a hierarchy becomes drillable. Sometimes I don’t want that, and there is an easy fix.

Problem Definition

When building a universe there are three types of result objects that I can build: dimensions, details, and measures. All dimensions are by default drillable, and the order of the objects in the class determines the default hierarchy. (That’s one of many reasons why trying to order objects in alphabetical order just does not work.) I can eliminate the default hierarchies (and allow my objects to be ordered alphabetically if I really need to) by creating custom hierarchies. That’s a subject for another blog post.

What I want to talk about today is how to eliminate all drill opportunities on a report, no matter whether I have used default or custom hierarchies. I want to be able to include dimension objects, put my report into drill mode, but not be able to drill on every possible dimension. Here’s how to do that.

Demote the Dimension Using a Variable

When I use a dimension object in my block and that dimension participates in a drill hierarchy and my report is in drill mode I will see the drill indicators. In the Web Intelligence Rich Client they look like this:

Screen shot showing drillable block in Web Intelligence

The “up arrow” implies that I can drill up. The underline format on the resort names implies that I can drill down. Here is a very simple fix if I don’t want either of those interface elements to appear. Create a variable that references the dimension, and use the variable instead. For this example, I will create a variable called Resort Name that has the simple formula =[Resort]. Once I do that, and I replace the original Resort object with my new variable, here is what the block looks like when the report is in drill mode:

Screen shot showing non-drillable block in Web Intelligence

Problem solved. 8-) The reason this works is that variables – even simple ones like this – are not drillable. Only dimension objects that come from the universe can be drilled. By converting a dimension to a variable it will no longer show any drill indicators when a report block is in drill mode. Why would I want to do that? As mentioned in the first paragraph of this post, I showed a trick several years ago that allows me to switch measures on a block by changing a selection in a drill filter. In order to do that, the report has to be in drill mode. I will write up that one soon, or you can read about it in Tales of a Universe Ninja, Part II, found on my conference presentations page.

10 Responses to “Want To Remove Drill Indicators? Use A Variable…”

  1. Comment by Nitin

    Hi Dave,

    I have been using this technique and found it to be very useful.But I never really had any explanation for this behavior, so when any one asked me about the reason I would just say that “when you creat a variable, it breaks the Hierarchy created in the universe as the variable would not get any reference for the next object in the hierarchy”
    Whats your say on this?

    One more thing I would want to ask..you have mentioned

    swap measures out on a chart using a drill filter

    what does that mean?

    BTW Love your blog very informative …have recommended it to my fellow BOBJ Folks :-)

  2. Comment by Dave Rathbun

    Hi, Nitin, if you have a look at the presentation I mentioned (Ninja Tales Part II) you will see what I mean. :)

  3. Comment by Nitin

    Hi Dave,
    Gr8 presentation..
    This is something that i had implemented few months back…never knew it was called “Measure Swaping” #-0..but while going thru the presentaion..i thot i would be posting a query on selection of “All” values as aggregation in the prompt..and voila..there it was in the next slide!!have been breaking my head for a few weeks now..
    >:D< your a life saver Dave :) :)

  4. Comment by Yoav

    Hi Dave,

    that’s a nice solution.
    my drawback is that you have to doubel your objects in that specific report but atleast you have s solution.
    Is it possible to develop in SDK a drill manager ?



  5. Comment by Dave Rathbun

    Is it possible? Possibly, but I have no idea. One of the (many) things on my “to do” list is to investigate the extension points feature added earlier in Web Intelligence. Unfortunately I have never had an urgent need, and the net result is I have never worked in that area.

    To be honest, I would not want to try to rewrite the drill feature.

  6. Comment by Nathan


    I’m one of the regular readers of your blog post and your replies in BOB. I have one out of the box question with respect ot drill filters.Is it possible to apply Nofilters(;Drill) on dimensions..?

    Thanks for your time,

  7. Comment by Dave Rathbun

    Nathan, I don’t know that I understand what you’re trying to do.

  8. Comment by Grantie

    Hi Dave. I was just looking at your trick to use Drill Filters to switch measures in a report. I’ve been doing the same thing but I’m using Input Controls. A really nice feature and like you say, you can pull the data back in the DP and just use the filters to change the measure without re-executing the query. One thing I don’t understand though. Having added in my second DP to get the different measure choices, and using a variable to test for the choice I make in the input control, my calculations go hey-wire. (They are calculation context sensitive by the way). Conceptually I don’t understand what I need to add to the input/output context now that I have this new dimension from the 2nd DP. Great blog btw.

  9. Comment by Ram

    Hi Dave,

    When the dimension is replaced with the variable drill indicator is disappeared. You have said that the variables are not drillable. Do you mean that the variable Resort Name cannot be drilled to the next level up or down? If yes, then

    How to remove the drill indicator alone but the drill action has to be performed when the user clicks on the column values? How to do this?

    Ramanathan S

    Ramanathan S

  10. Comment by raj


    I have a small question, Let say I have 2 blocks with 2 simple objects [Quarter], [Sales revenue] in both blocks. I made by report drillable. Now when I drill down on Year both my blocks are being drilled down but my requirement is I want to drill one block and the other 2nd block remains constant (only Quarter and sales revenue).

    I tried your approach of demoting the Quarter object in second block to variable but it dint work. also I unchecked the option “synchronize drill on report block” but it doesnt help.

    WHat I want is one block should drill and the other block remains constant when drill mode is activated.

Leave a Reply

If you want to include formulas or code in your comment, please read my Tips for formatting comments first. Tags you can use are listed below.

XHTML: You can use these tags: <a href="" title=""> <acronym title=""> <blockquote cite=""> <code> <em> <strike> <strong> <sup> <sub> <u>

Confirm submission by clicking only the marked checkbox:


Please remember that comments that are not related to this blog post may be ignored or deleted without notice. If you're looking for help on a topic you have already posted on BOB then please do not repost your question here.