[Office 2013] New OLAP Tools Part 2: Calculated Member

The first part has been published here and discussed the Calculated Measure case.

This second part is about Calculated Members.

2) MDX Calculated Member

Calculated Members should also be familiar to developers but if you’re not, just know that they can be seen as some kind of placeholder for sets of dimensions members.

A simple example should speak more than a thousand words :

In this example, I created a new member named [Australia & Canada], attached to the [Customer] dimension  [Customer Geography] hierarchy [All Customer] member.

In the Excel user interface it’s configured like this :

As a result, the displayed numbers (here the Sales Amount Measures) is the aggregation (here the sum) of those numbers affected to the respective members referenced in the formula.

Here Australia ($9 061 000) + Canada ($1 977 844) = Australia & Canada ($11 038 845)

The beauty of the thing is that it works whatever (Calculated) measure you may display in the Pivot Table.

Please note also that Calculated Members do not duplicate members but rather reference them. As such you will notice that the [Australia & Canada] sales amount does not impact the Grand Total. The first reflex of the few users I’ve shown this feature was to think that something was wrong as in their mind a grand total should be the sum of everything and not the sum of everything except this to that … At least, I would have like to have some way to visually distinguish calculated members from other members and/or and option in the Pivot Table options to have Visual Totals that would include the calculated members in the aggregation.

Now that we have presented the calculated members, lets play a little bit and see what we can do with them.

First, we used the + sign but we can also use any other arithmetical signs : minus sign (-), division sign (/), multiplication sign (*) and have the resulting numbers behaving accordingly.

Then, an alternative syntax to our first formula could have been the following :

AGGREGATE({[Customer].[Customer Geography].[Country].&[Australia],[Customer].[Customer Geography].[Country].&[Canada]})

In this formula, we use a set and aggregate it to define our calculated member. Starting from that, we see that we can use any formula or function that return a Set, wrap it in an aggregate function and display the result.

Doing so, we can build some kind of Dynamic Calculated Members. We can for example add a calculated member that will be used to display measures aggregations for our top 50 customers in term of sales :

Those of you familiar with the Adventure Works database sample may have noticed that Top 50 Customers is also the name of a named set in our Customer dimension : since named sets are … sets, we are allowed to use them in our calculated members definition :

To be exhaustive, I have to mention that it will work the same way if you define your name set in excel through the “Field, Items & Sets” menu.

We can add properties to our Calculated Members definition exactly the same way we do it for Calculated Measures. Though I have to say most of them are of limited use or no use at all : Properties like DISPLAY_FOLDER and ASSOCIATED_MEASURE_GROUP have no effect and even no sense for the second one, even if the syntax is valid.

Properties like FORMAT_STRING and FORE_COLOR have limited interest as they affect all measures displayed. Here’s an illustration of what to expect from those properties :

One last word on accessibility : As for Calculated Measures, a Calculated Member definition is stored in the context of the pivot table connection. So :

– what’s done in your Excel workbook cannot be seen  by your colleagues

– you can share Calculated Members definitions between different pivot tables/charts providing the fact they share the same Excel Connection

You can refer to the last paragraph of this post to understand the implications.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: