Calculated Members

The member function is used to create a new member in a specified hierarchy. The initial argument of the member function is the expression used to create the member. The next two arguments are the business key and caption values for the new member. The final argument is the hierarchy in which your new member will reside.

A calculated member can serve almost any purpose. One example of using many calculated members is to create a Variance calculation. If you wish to calculate a variance for many different measures you wouldn't use the measures themselves. Instead, you would create a calculated member in another dimension that would form the basis of your variance calculation. Say you wanted to compare the current period values to the prior period values. This would use an expression something like:

([Current Period] - [Prior Period])/[Prior Period]

You could define this as a new member of your time hierarchy using the member function as:

member(([Current Period] - [Prior Period])/[Prior Period], 'CurPriorVar', 'Variance to Prior Period', [Cube].[Time Dimension].[Time Hierarchy])

Top 5 and ranking

Need to build a top 5 list into your dashboard report? In my simple example, I have a 2 column list with [Products] and [Sales]. I create a new data item with:

rank([Sales] within set [Products])

I called this new data item [Rank] then applied a filter:

[Rank] < 6

That does it. But not really, because the rank() function will potentially give more then 5 rows if there are multiple entries with the same value. For example, if we were ranking on the following list and filtering for top 3:

Products: Sales: Rank:
Product1 50,000 1
Product2 40,000 2
Product3 30,000 3
Product4 30,000 3
Product5 20,000 5

You will end up with 4 rows in your report.

To restrict this to a only 3 rows, you'll need to sort the query by [Sales], and then use "running-count(1 for report)" in your [Rank] function. Then you'll get just the first 3 rows. Note that whether you get Product3 or Product4 will depend on other sorting criteria or the natural order of the data in the database, so take care!

The tip I've explained above uses the rank() function which is part of the member summary functions and utilized on non-dimensional source. For dimensional source, you will want to use the topCount() function to define [Products].