Custom DrillThrough Error - Truncating MDX Query?


I've tried implementing this in our cube and have run into an issue. The action expression is very simple:

ASSP.GetCustomDrillthroughMDX(" NAME([$Paid Date].[Date]) as [PaidDate]")

When I try to run the drillthrough in Excel I get an error that states the following:
Parser: The following syntax error occured during parsing: Invalid token, line 703, Offset 2, [.

I ran a trace using SQL Profiler and saw that the mdx being executed was as follows:
drillthrough select ([Adjusted Date].[Date].[All]
,[Adjusted Date].[Day Name].[All]
,[Adjusted Date].[Day of Month].[All]
... ***all remaining dimension attributes get listed
,[Payee Provider].[Provider Primary City Name].[All]

It ends with the ",[". It appears that the mdx script gets truncated at 32,768 bytes

We need to improve the logic in ASSP to avoid listing every dimension attribute if not necessary so that the query length of the rowset action fits within the 32,768 byte limit.


dgosbell wrote Jul 9, 2014 at 11:40 PM

Should see if the code in FindCurrentTuple will help as that does not output members when they are the default member for that attribute.

wrote May 4 at 3:07 PM

muhammadali330 wrote Nov 12 at 9:31 AM

Hi All,

Any help on this. I am also facing this error.


beanricecheese wrote Nov 12 at 5:35 PM

I orignally reported the issue and haven't seen any updates. It really hampers some of the ability to do drill throughs effectively. I'd love to see a fix added to a release soon.



muhammadali330 wrote Nov 13 at 5:46 AM

Hi James,

Did you find any work around?

dgosbell wrote Nov 13 at 6:27 AM

I think I see a spot where this can be optimized, I'll see if I can find time over the weekend to test this.

muhammadali330 wrote Nov 13 at 7:02 AM

When I call GetCustomDrillthroughMDX in SSMS, it returns complete drilltrough MDX. When I use it in Action expression, I got above error. Interestingly, When I manually put drilltrough statement in Action Expression of any length, it works.

muhammadali330 wrote Nov 13 at 9:30 AM

Thanks Darren. Much Appreciated.

dgosbell wrote Nov 14 at 12:30 AM

I've just uploaded a beta release here which includes an optional skipDefaultMembers parameter

So if you alter your call like the following:
ASSP.GetCustomDrillthroughMDX(" NAME([$Paid Date].[Date]) as [PaidDate]", true)

Then is should make the drillthrough statement much shorter as it will exclude any member references that are the default member for a given attribute.

So give it a test and let me know how you get on.

muhammadali330 wrote Nov 16 at 5:38 AM

Hi Darren,

I gave it a test. It is working But we didn't set any attribute as default member is out cube. Could you please explain what is the criteria of drill through statement to work correctly? Should we select all members in select part of it or there is some logic behind for drill through to work.

Thanks in advance.


dgosbell wrote Nov 16 at 7:15 AM

If you don't set an explicit default member the default behaviour is that SSAS will use the ALL member as the default. So all my code change does is to exclude explicitly outputting any member which the server would have used by default anyway.

So this statement:
drillthrough maxrows 1000 select 
[Date].[Calendar Year].&[2008]
) on 0 
from [Adventure Works] return [Internet Sales].[Internet Sales Amount]
    ,[Internet Sales].[Internet Total Product Cost]
    ,NAME([$Source Currency].[Source Currency Code])
Will produce the identical output to this one:
drillthrough maxrows 1000 select 
[Date].[Calendar Year].&[2008]
,[Geography].[City].[All Geographies]
,[Geography].[Country].[All Geographies]
,[Geography].[Geography Key].[All Geographies]
,[Geography].[Postal Code].[All Geographies]
,[Geography].[State-Province].[All Geographies]
) on 0 
from [Adventure Works] return [Internet Sales].[Internet Sales Amount]
    ,[Internet Sales].[Internet Total Product Cost]
    ,NAME([$Source Currency].[Source Currency Code])
Both give you the drillthrough for Bikes in 2008, But we don't need to explicitly reference the ALL members in Geography as those members will be used anyway as they are the default values. By excluding explicitly referencing members which are the default for their attribute we can make the drillthrough statement significantly smaller without changing the output.

muhammadali330 wrote Nov 16 at 10:10 AM


When I am passing my default measures as tuple to GetCustomDrillthroughMDX function, it returns

drillthrough select () on 0 from [cube] return [return columns]

muhammadali330 wrote Nov 16 at 10:24 AM

I have changed the code as

if (sCurrMbr == null || (sCurrMbr == h.DefaultMember && skipDefaultMembers && sOverrideMeasure == null)) continue;

Is that ok?

muhammadali330 wrote Nov 16 at 12:47 PM

Sorry for such a number of messages.

No need of above fix. Everything is working fine with original resolution.

dgosbell wrote Nov 16 at 7:36 PM

No worries, I did think I had checked that, but then my first attempt at this logic was not quite right and I moved it's position in the loop so I could not remember if I'd double checked that... :)