MDX is made for producing pivot tables.  In SQL Server Management Studio, someone will write MDX queries all day that produce pivot tables naturally.  However, when using some of these MDX queries in a SQL Server Reporting Services report, they’ll often run across this error:

The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension.

SSRS can ONLY have measures in the first dimension.  That’s not how MDX queries are normally written.  Dimensions usually appear both in the rows and columns, and the measure is selected in the WHERE clause.  Previous methods have used cross joins in the rows or a pass-through query in SQL Server.  Both potentially degrade performance.  However, there is another method that can be used with SSRS, and it’s deceptively simple.

Use a third axis in your query.

While SSMS cannot display a third axis, SSRS can use a third axis in a dataset.  To select your typical pivot table data, simply select your desired measures on the first axis, your desired columns on the second and desired rows on the third.  If your original query looks like this:

Text Example

Then your revised query will look like this:

Text Example

Again, SSMS will not be able to display the results of the revised query, but it will work properly in Reporting Services.  

Walkthrough

To demonstrate the technique, we will recreate the output from the original query in a SSRS report using a 3-axis query. Here are the results of the original query in SSMS:

the original query in SSMS

 In SSRS, we will duplicate those results with the 3-axis query.  Select New Dataset and enter the 3-axis query in design mode.

Select New Dataset

The fields show up in the dataset.

Dataset Example

Place the fields in a simple matrix

Place the fields in a simple matrix

Here’s the final report, duplicating the original SSMS query results.

Here’s the final report

Performance

Whither performance?  Let’s compare execution times of the 3-axis query to the original 2-axis query and to a 2-axis query that is SSRS compatible.  First, the SSRS compatible 2-axis query:

Text Example

Here’s some execution times as captured by SQL Profiler:

execution times as captured by SQL Profiler

 
     

We can see  the execution of the 3-axis query retains the favorable performance of the original. However, this dataset is just too small to give meaningful information.  If we increase the size by swapping FiscalYear to DateKey, we’ll get a little bit more information.

increase the size by swapping FiscalYear to DateKey

When that translates to our report, we see the following.  Frankly, the query in this case is not the major limiting factor, and the average improvement was a mere half-second.  More demanding queries should show a larger delta.

When that translates to our report, we see the following

Conclusion

What can we conclude from all of this?  We can develop something more akin to “natural” MDX queries for use in SSRS using a third axis.  We can realize some performance benefits and better flexibility in our queries.  Finally, we can do that without any additional security or configuration hassles.  It should be a technique to keep in your mind when developing MDX based SSRS reports.