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:
- 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. - 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 - 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 - 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.
I like your blog entries. I have recently added you to my blogroll. You can see it at: http://robertlambrecht.spaces.live.com
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
Is Intelligencia Query still available? None of the links work.
Hmm, I don’t know… It’s been a while since I’ve talked to them.