Calculated Measure, Member or a Query Calculation?

I posted previously about calculated members, but until recently, I never really understood when to use a calculated measure, a calculated member or a query calculation when building a report. By chance, I had the opportunity to bring this up to an IBM Cognos employee and was delighted to learn the following:

• Use Calculated Member or Calculated Measure for dimensional reports where the expression is a member or a tuple-based (as opposed to property-based) value expression.

• Use Calculate Measure if you intend it to behave like measure (or to replace a measure). In this case, the value expression must not depend on the current measure context.

• Use Calculate Member if you intend it to behave like measure (or to replace a measure). In this case, you must choose a hierarchy. The value expression must not depend on the current context in this hierarchy.

• Use Set Expression for set expressions. In this case, you must choose a hierarchy. The set expression must contain only members in this hierarchy.

Finally, use Query Calculation in all other cases.

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])