Tabular Action Type Rowset

Jun 20, 2014 at 8:25 PM
Great Tool! I have been using the Display Folders for months now...

I can add Tabular Actions of Type Drill Through at will. No problem.

I cannot add a Tabular Actions of Type Rowset. I've tried all kinds of combinations. I don't receive an error, but the Action is not recognized on the Excel Additional Actions menu, or as a result of an XMLA Query like this: ...<RequestType>MDSCHEMA_ACTIONS</RequestType>... (The Drill Through actions do appear in that XMLA query result).

My working hypothesis is that the settings I'm entering are wrong, and the database is rejecting the Action. Is there some log file that would tell me the error(s)?

Here are my Settings:

Action: MyRowset
Name: MyRowset
Caption: MyRowset
Description: MyRowset
Action Type: Rowset
Target Type: Cells
Target: [Measures].[IP Rate] (where I have a measure called [IP Rate])
Condition: (blank)
Expression: Evaluate Calculatetable('event') (where I have a table named event.)
Invocation: Interactive
Perspectives: (I have one, and it is checked.)

I understand that my Expression won't return something useful (yet). I will worry about the right Expression once I get the Action to be accepted on the Excel menu.

Thank you in advance!
Jun 20, 2014 at 8:53 PM
If you change the Expression to the following, does it work?

"Evaluate Calculatetable('event')"

All I did was add double quotes since Expression is supposed to return a string.
Marked as answer by furmangg on 6/24/2014 at 11:28 AM
Jun 20, 2014 at 10:24 PM
Brilliant!! That did it.

I saw the example as a string concatenation formula... didn't think a non-formula needed the quotes.

Thanks you!!!!!
Jun 24, 2014 at 2:56 PM
Are there limits on the Expression?

This simple expression works: "Evaluate Calculatetable('event')"

I have a fairly robust DAX expression that works fine in SSMS...I then wrap it in "", and paste it into BIDS Helper...but it is not added to the model.

Is there an error log?

Any suggestions appreciated.

Thank you in advance!
Jun 24, 2014 at 6:27 PM
Do you mean that when you browse the cube in Excel and try to launch that action it doesn't show up? Or do you mean that when you open the Tabular Actions Editor dialog it isn't there?

Can you save the DAX query expression to notepad then tell me the file size? I can try to reproduce this problem.
Jun 24, 2014 at 8:00 PM
Thank you for the quick reply.

Do you mean that when you browse the cube in Excel and try to launch that action it doesn't show up? Yes
Or do you mean that when you open the Tabular Actions Editor dialog it isn't there? No

The text of the expression is in the Tabular Actions Editor. When I query*** the Model or use the Excel Additional Actions menu, the Action is not there.

I have been trimming the expression down further and further. I no longer suspect that it is a size issue. I have just a few lines and it’s failing as described above. I copy this text out of the Tabular Actions Editor (minus the “”) and run the DAX expression in SSMS just fine. But the Tabular Actions Editor does not get it into the Model.

For example:

                     , All('event')                                                
                     , LevelOfCare[ProfilerLOCCategory] = "Inpatient"                 
                     , values('Date'[YearQuarterName])

Thank you again!

*** The following query returns the list of Actions deployed in a Model associated with a particular Measure:
<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
    <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="">

Jun 24, 2014 at 8:33 PM
It starts to work when I remove the filter line with the string literal with Quotation marks.

, LevelOfCare[ProfilerLOCCategory] = "Inpatient"

Trying different Escape characters.... /" as in SSIS does not seem to work....

Thanks -
Jun 24, 2014 at 8:56 PM
Edited Jun 24, 2014 at 8:59 PM
To include embedded quote characters you need to double up the quotes


                 , All('event')                                                
                 , LevelOfCare[ProfilerLOCCategory] = ""Inpatient""              
                 , values('Date'[YearQuarterName])
` Note that these escaped quotes will not work in SSMS - you'll need to edit them out if you want to test things there
Marked as answer by furmangg on 6/25/2014 at 1:17 AM
Jun 24, 2014 at 9:02 PM

You guys rock!! Thanks!!!!

PS: I did not include the very last " on the very last line. I think that is extra.