Reporting Services Drillthrough MDX queries

Reporting Services Drillthrough MDX queries by Darren Gosbell

Closed

as By Design Help for as By Design

Type:	Bug
ID:	126175
Opened:	11/20/2005 4:06:35 PM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

When attempting to set up a Report using a DRILLTHROUGH MDX Query the following error occurs

TITLE: Microsoft Visual Studio
------------------------------
Query preparation failed.
Failed to parse the query to detect if it is MDX or DMX.
(MDXQueryGenerator)

Details (expand)

English

SQL Server 2005 - Developer Edition (32)

Reporting Services

Windows XP Professional

English

1. Open up the Adventure Works Sample Reports project
2. Right click on the project and choose add a new item
3. Add a report (do not use the report wizard - this just gives a generic error when you paste in the query)
4. Create a new dataset based on the AdventureWorksAS datasource (leaving the query text blank)
5. Right click on the design mode button on the right hand side of the MDX query builder
6. Paste in the following MDX and to run the query using the Exclamation(!) button.

DRILLTHROUGH
SELECT
{[Product].[Product Model Lines].[Product Line].&[M]} ON ROWS,
{[Measures].[Sales Amount]} ON COLUMNS
FROM [Adventure Works]

Query not run, column list not populated and the following Error message:

TITLE: Microsoft Visual Studio
------------------------------
Query preparation failed.
Failed to parse the query to detect if it is MDX or DMX.
(MDXQueryGenerator)

That the columns list gets populated and that the query results are returned.

0 attachments

Posted by Microsoft on 11/28/2005 at 12:52 PM

Drillthru queries are not supported in the AS integration with Reporting Services. Any drillthru query against AS 2005 could equally well be written as an MDX query, to return the same data. For this reason, investment was not made in supporting drillthru queries (which would require special handling to ‘prepare’ the query, to determine the fields that will be returned).

Posted by Microsoft on 11/28/2005 at 1:40 PM

Posted by furmangg on 6/30/2006 at 2:19 PM

I don't agree with that statement that a drillthrough could be written in MDX. What if a fact table has more than one row for a particular slice. If you write an "equivalent" MDX statement, won't you get only one row for that slice showing the total, not the individual fact table rows?