MDX Essentials: MDX Member Functions: "Relative" Member Functions - Page 2

Calculated Members

Calculated members, in short, allow us to define new members, based upon dimensions or measures that exist within the cube in which we create the calculated member. They are members whose values depend on an expression rather than the value of a cell within a cube. The potential uses of calculated members are limited only by the imagination and experience of the developer. An example might be the somewhat common business concept of a variance measure. We could create this measure using, say, an actual sales measure and a budgeted sales measure that exist in a given cube.

Discussion:

To create a calculated measure, at least within the context of our simple overview, we will use the WITH clause, the syntax for which might resemble the following:

WITH MEMBER dimension.name AS 'Expression'

Dimension represents the dimension into which we are creating the new member. Name is my shorthand for the name of the member we are creating, and expression is the expression from which it is created.

Syntax

Let's look at a simple example to gain a feel for the syntax.

	WITH 
	   MEMBER [Measures].[Warehouse Margin] AS
	   '[Measures].[Warehouse Sales]-[Measures].[Warehouse Cost]'
	
	SELECT
	
	   {([Time].[Quarter].Members)} ON COLUMNS,
	   {([Warehouse].[Country].USA)} ON ROWS
	
	FROM Warehouse
	
	WHERE ([Measures].[Warehouse Margin])

The result dataset returned would appear as shown in Illustration 1 below:

As we can see above, the WITH section contains the definition of the calculated member Warehouse Margin. The newly created Warehouse Margin is a member of the Measures dimension, and is composed of the total of Warehouse Sales less Warehouse Cost, intersecting all members of the remaining dimensions in the cube, but displaying the combined intersects with the USA warehouses for purposes of our query. The returned dataset, therefore, represents the Warehouse Profit (which we called Warehouse Margin in our query) for the USA warehouses, by quarter (for the years 1997 and 1998, as stored in the Warehouse sample cube).

The SELECT keyword comes at the end of the WITH section, where it begins the specification of the axes / cells to which we have become accustomed in previous lessons. The SELECT statement allows us to place the members we wish to display upon the respective axes of the display, including enumeration of the quarters through our use of the .Members function. The WHERE clause, as we know from previous lessons, acts as the slicer dimension, which, in the immediate example, allows us to hold the new Warehouse Margin member constant for purposes of our query results.

We will explore many aspects of calculated members as we proceed through the series. This lesson will use them in the practice examples for purposes of illustrating the use of the "relative" functions that we have targeted for the article; in this way, we will continue to stay on track with our examination of the member functions while becoming comfortable with the rudiments of calculated member usage and behavior. Calculated members will be with us for a long time to come, and will provide many uses and topics of discussion.