Speed up MDX query preparation in Reporting Services

We often need to build some advanced functionality into our reports and since the Reporting Services query designer does not provide a very advanced way to create MDX statements, our only option is to get our hands dirty and write some code ourselves. One of the things we must consider is the time that the query designer needs to validate our syntax, execute the query and determine what fields to be created based on the data set it receives from Analysis Services.

When we specify a number of query parameters, we have the option to give them default values:


If we do not do this, the query will be executed with the default dimension member instead of the a specific value as a parameter, which can be slow and unnecessary in the usual case where the default member is the (All) member. Instead, we should always specify a value which is to be used for limiting the sample data set. Ideally, is should be small but not empty. If the parameter is too limiting and the MDX query does not return any values the data fields will not be populated.

Specifying default values speeds up development time, especially when using complex cubes, where Reporting Services queries can be slow as its dimension cross joins can be very big.

Chris Webb also blogged in regards to this issue. I strongly recommend you read his post.

Reporting Designer in BIDS 2008 Column Visibility Bug

A small issue with BIDS I just found out about – if you want to modify the visibility expression of a column in Business Intelligence Development Studio 2008 you may find that the Fields list is empty and that Report Designer cannot see a Dataset binding for the column. Regardless of that, if you type in a Fields!<field_name> expression and you get a red curvy line indicating something is wrong with it, you will be able to deploy the report and the visibility of the column will get toggled by the expression. So it actually works but also could be annoying.

Note (2009-02-07): This appears if you have a tablix within a tablix and the outer tablix does not have a dataset associated with it.