Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 2, 2003

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

By William Pearson

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.


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.


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

	   MEMBER [Measures].[Warehouse Margin] AS
	   '[Measures].[Warehouse Sales]-[Measures].[Warehouse Cost]'
	   {([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:

Illustration 1: Example Result Dataset Using WITH to Create a Calculated Member

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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM