Using the Caption property with Calculated Members in AS2008

One of the many minor improvements in AS2008 MDX is the ability to specify a separate caption for calculated members. Vidas already did a good writeup of the other new properties that you can specify on calculated members, but for me the ability to specify captions is interesting because we should really be setting captions on calculated members as a matter of best practice.

First, how does it work? Well, it’s very straightforward – here’s an example query and output:

You can see that although we’ve declared a calculated measure whose unique name is measures.test, when the query is run the end user sees the caption "This is a test measure". Note you have to use a hard-coded string as a caption, you don’t seem to be able to use an MDX expression.

But why should we be doing this? Because calculated member captions are something that change quite often, especially during development, and if you don’t use the caption property then you’ll end up having to change the unique name of the calculated member all the time – and this of course will break any other calculations in the MDX Script that refer to this calculation, and worse it will also break any existing queries in reports that refer to this calculation. Also since calculated member captions can be quite long, the ability to write MDX expressions that refer to a much shorter unique name means your code will be much more concise and readable.

Of course with real members on non-measures dimensions, if you have specified different columns for the Key and Name properties of the attribute hierarchy you already get this separation between the unique name of the member and the caption that the user sees. The only missing bit of functionality now is a separate Caption property for real (as opposed to calculated) measures.