Custom DrillThrough Error - Truncating MDX Query?

Topics: Developer Forum
Jul 3, 2014 at 12:48 AM
I've tried implementing this in our cube and have run into an issue. The action expression is very simple:
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. Also, I don't really understand why it's trying to pull every dimension attribute when I've only listed one. Have you ran into this issue... do you see anything wrong with my syntax? I've tried quite a few variations and haven't had any luck.

BTW... I love your site... lot's of good info on here... keep in coming!

Jul 3, 2014 at 3:21 PM
I don't think we have received reports of this. Can you copy the entire MDX query into notepad and save it to disk and tell me the size on disk. I'm curious the number of bytes before it gets truncated, if that's what's happening.

Once I get that number, I will try to reproduce.

What version of SSAS exactly? If you connect in SSMS Object Explorer to SSAS and look at the version number next to the server name?
Jul 3, 2014 at 4:12 PM
The size on disk ends up as 32,768 bytes. We are running SSAS 2012, version 11.0.3373.0.
Thanks for looking into this.
Jul 3, 2014 at 6:04 PM
That is a very suspicious round number. My guess is that there's an Excel limit on a command length. But let's troubleshoot. Can you run this query from Management Studio:

member test as ASSP.GetCustomDrillthroughMDX("
NAME([$Paid Date].[Date]) as [PaidDate]
select test on 0
from [Your Cube Name]

Then click on the results and copy the string back into a query window. Please make sure it's not truncated. I suspect it won't be. Then put this in your clipboard.

Can you do a regular double click to perform a regular drillthrough without ASSP. Then right click on the resulting query table with the drillthrough results and edit the query. Then try to paste in the long query from your clipboard. My guess is that Excel won't accept a query that's longer.

What version of Excel do you have?

If my suspicions are true, we may need to find a way to reduce the query size that comes out of ASSP. We include the All members to make sure that attribute overwrite semantics and default members are properly accounted for. But we can probably be smarter about this.
Jul 3, 2014 at 11:33 PM
I'm able to follow these steps and paste the whole drillthrough query in excel. I do get an error when excel tries to refresh the query though:
Errors from teh Sql query module: the attribute 'Date' in the dimension 'Paid Date' is outsdie the granularity of the measure group "EAM Transactions' and cannot be returned as a column.

The error makes sense becuase that Measure Group does not contain a Paid Date... not sure what I need to do to resolve this.

The query does not get truncated though.

Also, I'm running Excel 2010 but have also tried this on Excel 2013 which results in the same error.

Jul 9, 2014 at 3:40 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Jul 9, 2014 at 3:42 PM
I can reproduce the issue of the query getting truncated. I think we will have to improve ASSP to return a more concise query. I've added a work item to that effect. Thanks for reporting.