SSRS - MDX Drillthrough gotcha
"Drillthrough" is an almost overused used term in BI. In SSRS it is often used when providing Jump To functionality to send the user to another report, a URL or a bookmark within the current report. We also have Infinite Clickthrough in Report Builder (Enterprise Edition only). The MDX DRILLTHROUGH statement also provides (surprise, surprise) granular drillthrough functionality and presents a small but easily-solved gotcha when using SSRS as opposed to using the same statement in RS2000.
The new SSAS functionality in SSRS both improved & changed the playing field quite a bit when it comes to accessing OLAP data for reports. In 2005 when we want to use the new SSAS interface to build queries (either hand-coded or drag & drop) we create a data source using the Microsoft SQL Server Analysis Services provider type which is ADOMD .NET. This is fine for the master report data but trying to execute an MDX SELECT DRILLTHROUGH query against the same data source will result in an error like this:
Failed to parse the query to detect if it is MDX or DMX. (MDXQueryGenerator)
For the drillthrough report and its query we now (unfortunately) need to create another data source, this time using the OLE DB provider for Analysis Services 9.0. When you create your new data source select OLE DB in the Type dropdown then select Microsoft OLE DB Provider for Analysis Services 9.0 in the OLE DB Provider dropdown.
Using this new data source we are now able to create a new dataset and run our drillthrough MDX query. Unfortunately we don't get to use the nice colour-coded UI and object browser, we're back to the old-style view.
An SSRS solution with an example of reports using DRILLTHROUGH can be found here.
UPDATE: As usual fellow Aussie blogger Darren Gosbell is right on top of things. He actually posted this as a bug to MS a while back. MS gave him a workaround which allows us to continue to use the original Data Source but must enter the MDX Drillthrough statement as if it was a DMX query - apparently this was by design. Oh well... Either way we now have a couple of methods on how to get past this problem, niether particularly elegant, but both work. Thanks to Darren for keeping me on my toes.
3 comments:
Hey Nick, I posted this issue into LadyBug a while ago and got back a "By Design" response.
Their response was that any Drillthrough query could be written in MDX just as easily in SQL 2005.
You can see the bug here:
http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=7fc3caba-13ef-415f-9b39-4032c30b5b2d
And while there are a couple of reasonable workarounds for this issue, the "Failed to parse..." error is not very informative.
We have lots of project each one consist of lots of task ,each task have been assigned some No of CPU for the individual task
Let say we have 3 tasks for 1 project
Task name task start date task End date No-of-CPU
Task 1 1-jan 07 1 feb 07 100
Task 2 7 jan 07 28 jan 07 200
Task 3 16 jan 07 28 jan 07 150
So our requirement is that data should looks like this
At year level Total no of cpu for project1 = 450(year level)
At month level no of cpu should looks like
Month No of CPu
Jan 450 ( all tasks contain jan)
Feb 100( task1 contain month feb)
At week level we should get tha data
Week No of CPU
Wk1 100+200=300
Wk2 100+200=300
Wk3 100+200+150=450
Wk4 100+200+150=450
Wk5 100=100
Now we are successful to populate the data at lower level(at week level)
But when we collapse the hierarchy at month level it add all the week data of
That particular month e.g
If we collapse the pivot table at month level (jan) total no of CPU for jan it will show 300+300+450+450=1500
But we are desired only 450 cpu
And same for year level.
How can we come up with this problem??
GP,
you may want to have a look at the aggregation settings of your CPU measure.
cheers,
NB
Post a Comment