While doing my bit for the SQL Server community and answering MDX forum questions, I many times see a common pattern where the OP (original poster) gets the dimensionality (or dimensional scope) of calculated members mixed-up. And I’ll be the first to admit that I also get it wrong more than I would like to, but getting our minds trained around multi-dimensional sets take a little effort…ok, a lot of effort!!
Through my hours of struggling with MDX queries, I found a few very useful methods to help test my queries. In this blog post I want to focus on one of these, the SetToStr() function. This function essentially takes a set, and converts that set into a flattened string value…which proves very helpful if you need to see which dimension members are included in a calculation.
To set the scene, look at the following MDX query on the Adventure Works cube:
What we’re attempting to do here, is to get the total sales (across all sales channels) as a calculated measure. While it seems logical at first (especially if you are not used to the MDX syntax) that [Sales Channel].[Sales Channel].members is the correct syntax to use for our calculation, you can see that our total is almost twice what we’re expecting to see.
To illustrate how we can troubleshoot (and resolve) the error, let’s add another calculated member and use the SetToStr() function to show us all the elements contained within [Sales Channel].[Sales Channel].members:
A closer look at the results reveal that the [All Sales Channels] element is also included in our calculation, as the image below confirms:
Ahh…that would explain the inflated number for the total sales!! And after a bit more tweaking we’ll notice that stepping down a level and using [Sales Channel].[Sales Channel].[Sales Channel].members will resolve our issue.
