CODEWITHSUNDEEP
sql-serverssasmdx
Adrian
Adrian

Reputation: 31

MDX DRILLTHROUGH Fails but select can be successfully executed

I have the following MDX query which succesfully returns the measure when executed -

SELECT  
{[Measures].[Closed Quote OE Retail]} ON COLUMNS 
FROM Sales
WHERE
(
[Posting Date].[Date YQMD].[Month].&[11]&[2012]
,[Work Provider].[Code].[LV]
,EXCEPT([Item].[by Item Category by Product Group].[Item Category], [Item].[by Item Category by Product Group].[Item Category].&[OEM])
,EXCEPT([Lost Sale Reason Code].[Code].[Code], [Lost Sale Reason Code].[Code].[All Lost Sale Reason Code].UNKNOWNMEMBER)
,EXCEPT([Lost Sale Reason Code].[by MI Type].[MI Type], { [Lost Sale Reason Code].[by MI Type].[MI Type].&[Not Justified] })
)

But if I add 'DRILLTHROUGH' to the start of the query the following error is returned -

Drillthrough failed because the coordinate identified by the SELECT clause is out of range.

Can anyone help?

Upvotes: 3

Views: 1904

Answers (1)

Bill
Bill

Reputation: 4585

Looks like MDX doesn't like DRILLTHROUGH when you have multiple members of the same dimension in the Select - in this case, on your slicer dimension. It also appears you can trick it by doing a sub-select, but I would verify the totals pretty carefully before relying on this solution.

SELECT  
{[Measures].[Closed Quote OE Retail]} ON COLUMNS 
FROM 
(
  Select 
  (
    [Posting Date].[Date YQMD].[Month].&[11]&[2012]
    ,[Work Provider].[Code].[LV]
    ,EXCEPT([Item].[by Item Category by Product Group].[Item Category], 
      [Item].[by Item Category by Product Group].[Item Category].&[OEM])
    ,EXCEPT([Lost Sale Reason Code].[Code].[Code], 
      [Lost Sale Reason Code].[Code].[All Lost Sale Reason Code].UNKNOWNMEMBER)
    ,EXCEPT([Lost Sale Reason Code].[by MI Type].[MI Type], 
      { [Lost Sale Reason Code].[by MI Type].[MI Type].&[Not Justified] }
  ) on 0
From Sales)

Upvotes: 1

Related Questions