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 Dec 2, 2002

Introducing the SQL Server 'MDX in Analysis Services' Series - Page 4

By William Pearson

A "Platform for Exploration:" Simple Calculated Members

Let's create a simple calculated member to get a look at its properties in a general way. We will also use our newly created calculated member to explore other facets of MDX as we progress through the tutorial. From the Analysis Manager console, and at the Cubes folder within the FoodMart 2000 database sample,

  1. Expand the Cubes folder (seen in the illustration below) by clicking the "+" sign to its left.

Illustration 3: Sample Cubes provided with the Analysis Services Installation

  1. Right click the Warehouse cube, then click Edit from the flyout menu.
  2. When the Cube Editor screen appears, click the Data tab at the lower left (on the right half of the screen).

The preview pane is activated.

  1. If "Product Family" does not appear in the lower pane area, drag the Product dimension to the rows area (the left column of the lower pane section).

The Cube Editor preview pane should appear as shown below in Illustration 4.

Illustration 4: The Cube Editor Preview Pane

  1. Select Insert Calculated Member (top drop down menu) under Insert. A picture of the associated toolbar button, an alternative means of initializing the Calculated Member Builder, appears below.

Illustration 5: The Insert Calculated Member Toolbar Button

The Calculated Member Builder appears.

  1. Type MyCalcMem into the Member Name box. In the Value Expression box, input the phrase "Check for Updates" (quotation marks required). The Value Expression box should appear as shown below.

Illustration 6: The Value Expression Dialog Box with Inserted String

  1. Click OK.
  2. Review the newly created MyCalcMem column, comparing it to the illustration below.

Illustration 7: The New MyCalcMem Column

To change the expression for MyCalcMem, go to the Calculated Members folder in the Cube tree in the left pane of the Cube Editor, as partially shown below.

Illustration 8: MyCalcMem now appears in the Calculated Member Folder

  1. Click MyCalcMem within the Calculated Members folder.
  2. Right click and select Edit.
  3. Type 11+2.
  4. Click OK.

Standard mathematical operations apply here. For details, see the Online Books for Analysis Services/MSSQL Server 2000.

  1. The number 13 fills the MyCalcMem column cells, replacing the "Check for Updates" string.

To perform a text concatenation,

  1. Select the Properties pane at the bottom of the left side of the Cube Editor, ensuring that MyCalcMem (in the Calculated Members folder of the Cube tree) remains highlighted.
  2. Select the Value property for MyCalcMem to edit its properties.
  3. Click the ellipsis (...) button, and, when the Calculated Member Builder appears, clear the Value Expression box.
  4. Type in the following:

"MyCalcMem" + "-" + "REVISION1"

The contents of the Value Expression box should resemble those depicted in Illustration 9 below.

Illustration 9: Text Concatenation in the Value Expression Box

  1. Click OK. Compare the results to the partial illustration shown.

Illustration 10: Text Concatenation Results for MyCalcMem in the Preview Pane

As we have seen, the result of placing "+" between two numbers in an MDX expression is the sum of the numbers, while the "+" sign used between two strings returns a concatenated string. (Remember that strings need to be enclosed in quotation marks.)

Page 5: "Getting Familiar with the Membership" with MDX Expressions

See All Articles by Columnist William E. Pearson, III

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