Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Implementing Analysis Services Drillthrough in Reporting Services

with 4 comments

For some reason I’ve never needed to implement Analysis Services drillthough (note: not the same thing as Reporting Services drillthrough; why can’t they use consistent terminology?) in a Reporting Services report. Of course, Reporting Services support for Analysis Services being what it is, it’s not a straightforward task and since I’ve recently come across a few good blog posts that discuss the different ways you can do it I thought I’d link to them.

The main problem is that you can’t execute an MDX Drillthrough statement using the MDX query designer and the Analysis Services data source. You have four options then:

  1. You can execute the Drillthrough statement through an OLEDB data source instead. Gurvan Guyader shows how to do this in the following blog entry (in French, but with lots of screenshots):
    http://gurvang.blogspot.com/2009/05/drillthrough-ssas-dans-ssrs.html
    The problem with using an OLEDB data source is that you lose the ability to use parameters and have to use Reporting Services expressions to dynamically built your Drillthrough statement instead.
  2. It turns out you can also execute a Drillthrough statement by pretending it’s DMX, and so use regular MDX parameters, as Francois Jehl describes here (also in French):
    http://fjehl.blogspot.com/2009/06/drillthrough-ssas-dans-ssrs-ajout-au.html
  3. If you buy Intelligencia Query (which, as always, I need to state that I have a financial interest in) then Drillthrough statements now work with no tricks necessary:
    http://andrewwiles.spaces.live.com/Blog/cns!43141EE7B38A8A7A!562.entry
  4. Last of all, you can try not using a Drillthrough statement at all and use an MDX query instead to get the same data. You will lose some functionality though by doing this, however, most notably the MAXROWS option.

Written by Chris Webb

June 16, 2009 at 1:13 pm

Posted in Reporting Services

4 Responses

Subscribe to comments with RSS.

  1. I like your blog entries. I have recently added you to my blogroll. You can see it at: http://robertlambrecht.spaces.live.com

    Robert

    June 16, 2009 at 7:36 pm

  2. SSAS and SSRS isnt a happy mariage :) Chris, I have a question and hope you won\’t mind.. if you do well you can simply ignore this comment :)I am trying to sell Report Builder 2.0 as the best expert end user tool.. but the client has complex filter requirements which I fear are out of RS 2.0 league:The customer would need to be able to answer the following question using the Query Designer (example):- (All customers who have bought a car in 2006 for the first time)1 and (have bought a second car at least one year later)2Using TSQL you would be able to answer the question. There is an dependancy between the year from result set 1 (2006).. and resultset 2 (at least one year later) which is 12 months from the buy date.So basicly resultset 2 is dependent on result 1 and the client wants to be able to use the UI and not have to write TSQL.. do you have an idea how? Well I hope you decide to answer.. thank you.Kind regards Ian SmithThanks in advance

    Ian

    July 10, 2009 at 9:47 pm

  3. Is Intelligencia Query still available? None of the links work.

    Jay

    June 22, 2012 at 8:26 pm

    • Hmm, I don’t know… It’s been a while since I’ve talked to them.

      Chris Webb

      June 23, 2012 at 7:19 pm


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,220 other followers

%d bloggers like this: