Excel doesn't display action option when action expression is not a string

Topics: Developer Forum, User Forum
Jun 8, 2011 at 8:46 PM

I created a standard action for drill through using action expression like ASSP.GetCustomDrillthroughMDX("[Firm and Fund Contributions-Non Confidential].[Disbursement Id]"). Looks like Excel doesn't like it and refuse to show it when I right-click a measure. If I put quotation marks in the front and back, Excel will show it but returns error, obviously.

Somehow, i can run this action in Visual Stuido's browser. In debugging, i found VS and Excel acts differently. In VS, when i select a measure, there is an event goes back to SSAS and this stored procedure is called to generate a MDX string. If i click a measure in Excel, no event happens, which means Excel won't get the MDX string.

what can I do? Thanks.

Jun 10, 2011 at 4:46 AM

It does not look like the definition you are using for the return columns would be valid for a drillthrough command

If you change the cube name to whatever your cube is called and then run the following command it will output the drillthrough command that SSAS is trying to execute as part of the action

WITH Member Measures.DrillTest as ASSP.GetCustomDrillthroughMDX("[Firm and Fund Contributions-Non Confidential].[Disbursement Id]")

SELECT Measures.DrillTest ON 0

FROM <myCube>

You can then copy and paste the text into a query window and you can test it to see if it is a valid drillthrough query.

I'm not sure exactly what you are trying to achieve, but something like the following would get the name property of the [Disbursement Id] attribute:

ASSP.GetCustomDrillthroughMDX("NAME[$Firm and Fund Contributions-Non Confidential].[Disbursement Id])")

Jun 10, 2011 at 2:53 PM

Thank you, dgosbell.

I tried the "with member..." method and it didn't work. Generated MDX is invalid.

In this standard action, I have followed samples in the project (Target Type:Cells, All Cells). A single statement in action expression as ASSP.GetCustomDrillthroughMDX("[Firm and Fund Contributions-Non Confidential].[Disbursement Id]") works in Visual Studio's browser. It does the drillthrough and the result is what i want. Somehow, as mentioned in the post, Excel doesn't show this action if I write the action expression like this.

One of the method in ASSP.GetCustomDrillthroughMDX accepts return columns as below. I can tell it gets called and generated correct MDX in VS.

public static string GetCustomDrillthroughMDX(string sReturnColumns)
      return GetDrillthroughMDXInternal(null, sReturnColumns, null);



Jun 13, 2011 at 11:58 PM

Actually I may have oversimplified the "WITH Member..." test too much.

However I just had another thought. Do you have any multi-select filters in your Excel pivot table? Excel does not support any form of drillthrough when there is more than one member selected in the filter for a given attribute and it will not even issue the MDSCHEMA_ACTIONS request which sounds like the behaviour you are seeing.

Jun 14, 2011 at 3:47 AM
Edited Jun 14, 2011 at 3:48 AM

no, i didn't select more than one member when i was doing test with this drillthrough. i knew this is the limit. So, generally speaking, express like ASSP.GetCustomDrillthroughMDX("...") will work and my case is an exception? I am using Windows 7 64bit, SQL 2008 R2 and Excel 2007 with SP2.

i put a stop in GetCustomDrillthroughtMDX() method in VS and saw different behaviour. In VS cube browser, when i move the mouse the cell where i want to drillthrough and clicked it, the stop will be hit and i am in debug mode. i guess as this point, GetCustomDrillthroughtMDX() will run and generate a valid MDX string. In Excel, it is a different thing, when i clicked the drillthrough cell, nothing happened so no MDX string generated.


Jun 16, 2011 at 2:05 AM

Are you able to reproduce this behaviour against the adventure works database? In my testing, if the action is valid it works in both, if I do something invalid it does not work in either.