Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Speeding up the Query Parameters Dialog in the SSRS Query Designer

with 7 comments

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:

Written by Chris Webb

January 22, 2009 at 1:28 pm

Posted in Reporting Services

7 Responses

Subscribe to comments with RSS.

  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\’.


    March 27, 2009 at 12:09 pm

  2. You have to add the MDX to the MDX Script of your cube, ie on the Calculations tab of the Cube Editor in BIDS


    March 27, 2009 at 6:19 pm

  3. how can i use the parametre ProductCategory in my MDX query?


    February 24, 2010 at 11:01 am

  4. 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.



    March 13, 2015 at 6:01 pm

    • Hi Tonia, no this isn’t possible. Can you tell me why you want to do this?

      Chris Webb

      March 13, 2015 at 6:43 pm

      • 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.



        March 13, 2015 at 8:13 pm

      • It sounds like your only option is to redesign this large dimension to avoid whatever memory problem it is you’re having, unfortunately.

        Chris Webb

        March 15, 2015 at 9:24 pm

Leave a Reply

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

You are commenting using your 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 )

Google+ photo

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

Connecting to %s


Get every new post delivered to your Inbox.

Join 3,962 other followers

%d bloggers like this: