Here's the good news. The "rowset action" in SSAS returns a string with the command that the client tool should execute to get the rowset. So if you want the rowset action to execute an MDX query, then just have that rowset action build a string defining the MDX query.

Here's an example rowset action that runs an MDX query. Let's say you've got your PivotTable filtered to one month and you see the products sold. Right click on a particular product and choose the "History for Product XYZ" action from the Additional Actions menu. Then this rowset action runs an MDX query which shows you history (more than just the month you're looking at) for that particular product:

Name: Product History
Target Type: Attribute members
Target Object: Product.Product
Condition: <empty>
Type: Rowset
Action expression:
"
select {[Measures].[Internet Sales Amount], [Measures].[Reseller Sales Amount]} on 0,
NON EMPTY [Date].[Calendar Year].[Calendar Year].Members on 1
from [Adventure Works]
where
"

• [Product].[Product].CurrentMember.UniqueName
  

Invocation: Interactive
Caption: "History for Product " + [Product].[Product].CurrentMember.Name
Caption is MDX: True

That works from Excel against Adventure Works.

So the only question is whether you can live with the column names it generates when it opens the new sheet and displays the results. If not, then we could probably put together a function that fixed columns names similar to the drillthrough one, but which was specifically coded to fix column names from an MDX query. You'll note the drillthrough sproc that fixes column names messes up MDX column names in many instances:

call ASSP.ExecuteDrillthroughAndFixColumns("
select {[Measures].[Internet Sales Amount], [Measures].[Reseller Sales Amount]} on 0,
NON EMPTY [Date].[Calendar Year].[Calendar Year].Members on 1
from [Adventure Works]
where [Product].[Product Categories].[Product].&[355]
")

So should I close this request? Or do you need fixed column names?