Getting MDX to Work in Reporting Services

You could be forgiven for thinking that SQL Server Reporting Services (SSRS) and SQL Server Analysis Services (SSAS), two pillars of Microsoft’s BI toolset, would work well together. After all, if you’ve gone to all the trouble of building a cube, you would expect to be able to use it as the source for your SSRS reports and not just for ad-hoc querying, wouldn’t you? Unfortunately, anyone who has tried to create anything more than the most basic SSRS reports on top of SSAS will tell you that it can be an extremely frustrating experience.

The most irritating issue (which—to make matters worse—is the result of a conscious design decision rather than a bug), is that SSRS restricts the structure of the MDX queries you’re allowed to write: You can put the Measures dimension only on the Columns axis in a SELECT statement, and you must put all other dimensions either on the Rows axis or in the Where clause.

True, you can subsequently use a matrix control to pivot your data within a report, but this is a poor substitute for being able to write the query the way you wanted in the first place. To use a SQL Server analogy, it’s as if SSRS prevented you from using a Group By clause in your queries and made you do any aggregation with built-in SSRS functionality.

One example of the kind of problem this restriction causes occurs when you want to parameterize a report by the Measures dimension. When you create a parameter in a report, you usually want to base the list of available values for that parameter on the contents of a dataset. That dataset should be on the result of a query that returns these available values, one per row—but SSRS doesn’t let you write an MDX query with members of the Measures dimensions on the Rows axis. There is a workaround (see Web Listing 1) but it isn’t elegant and shouldn’t really be necessary at all.

Another problem occurs when you want to display specific combinations of members from the Measures dimension and members from other dimensions on the Columns axis in a report. For instance, you might want to show three columns: the measure Sales for the years 2006 and 2007, and the calculated measure % Sales Growth for 2007 alone.

In SSRS, you would have to write a query that had Sales and % Sales Growth on Columns and the years 2006 and 2007 (cross joined with anything else you wanted to display in the report) on rows. This means that you’re returning values in your query that you don’t actually want (in this case, the values for % Sales Growth for the year 2006), which might have a negative impact on query performance if the unwanted values are returned by calculated members. Again, there is a workaround (see Web Listings 2 and 3) but performance of the rewritten query can be worse than that of the original.

The problems I’ve described occur only when you use the built-in “Microsoft SQL Server Analysis Services” data source type. You can alternatively connect to SSAS using an OLE DB data source, but doing so means that you can’t use the MDX query builder or use MDX parameters in your queries. The latter is a big loss because, to be able to parameterize a query, you have to dynamically generate your query string as an SSRS expression, and long expressions (it’s rare that the query string is short) can quickly become unreadable and unmaintainable.

I know of many projects where people started out using SSRS as a reporting tool and then were forced to find an alternative that worked better with SSAS. It’s not clear whether the situation will be any better with SSRS 2008, but the initial signs are that nothing much will change.

All that’s needed is for Reporting Services and Analysis Services to be integrated properly so that a person can write whatever MDX query he or she likes and bind it to a control that will display the results. Is that too much to ask?