Speeding up the Query Parameters Dialog in the SSRS Query Designer

Boyan Penev recently blogged about the virtues of setting default parameters and he’s right: it’s a good idea to set parameter defaults. However I was with a customer yesterday who were waiting for a really long time to even open the Query Parameters dialog in the SSRS query designer. I was a bit curious as to why this was, so I ran a Profiler trace. What I found was then when I opened the dialog when there was a default selection made, for example:


The query designer would run an MDX query like this:

Clearly it was running queries to check the parameters were valid but in this case (although not in other instances) it was forgetting to add an empty set on the opposing axis, as per Mosha’s recommendations here, and therefore bringing back measure values by accident. Now this doesn’t matter for most cubes but my customer had a very, very large cube, their default measure was on their biggest fact table, and hadn’t built any aggregations that this query could use. And so, not surprisingly, the query ran very slowly and editing the reports was a nightmare.

There are two possible workarounds here:

  1. Build an aggregation for the above query, or
  2. Create a new, hidden calculated measure that returns null and make that the default measure on the cube. Here’s what to add to the MDX Script to do this:


Interestingly the same dialog also seems to generate some errors from the queries it runs which I think reinforces the argument for someone to go back to this code and do some work for the next hotfix. The errors seem to happen when you have a parameter with no default value set – the MDX error message is:
Query (1, 9) Parser: The syntax for ‘on’ is incorrect.
Query Text:
SELECT  on 0 FROM [Adventure Works]

It doesn’t really matter from a performance point of view, but it’s not very tidy. Anyway, I’ve opened an item on Connect if you’d like to vote on this:

7 responses

  1. Where do you execute the MDX query? If I add it to my Dataset query in the Report Designer I get an error…\’failed to detect whether this is an MDX or DMX query\’…and if I try to execute it in SSMS while connected to an Analysis Services instance I get the error \’no cube specified\’.

  2. I am not sure if this is possible but I would like to know how to execute this script only when Specific dimension is selected


    Can I use a CASE Statement? If yes how or can I do this? I dont want this applied to all the dimensions in the cube. I will appreciate any help.

    Thank you.


      • I am doing this because after adding this scrip to the cube, when I try to apply filter to other smaller dimension fields, the filters dont work unless I select a measure. If I take off the alter script, the filters work for the dimension but then I have this memory issue with this one dimension.

        Thank you.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: