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 9

By William Pearson

Perform Conditional Tests and Comparisons

We will conclude our tutorial with a review of an expression that contains conditional testing, coupled with a comparison test, to illustrate these important concepts.

  1. "Swap" the Product dimension with the Store dimension, so that the Product dimension returns to the row axis. The Store dimension once again returns to the top portion of the Cube Editor.
  2. We once more select the Value property of the MyCalcMem Calculated Member.
  3. We click the ellipsis button, and, at the Value Expression box, we modify our most recent expression to the one below: Ancestor([Product].CurrentMember,[Product].[Product Category]).Name = "Bread"
  4. We accept the modification by clicking OK. The data returned should appear as partially shown below, after drilling down past the "Bread" Product Category. (The drill down path is Food -> Baked Goods -> Bread -> ... and so forth)

Illustration 27: The Results of the Application of a Comparison

We see that the MyCalcMem member contains a 1, equivalent to True, or a 0, meaning False with regard to the respective Product's membership in the Bread category.

This presents an opportunity to introduce a conditional test within the IIF To begin this, we will establish three arguments, the first of which is used by the IIF function as a conditional test (a test whose result is either 1 or zero, denoting True or False, respectively). The next argument provides the value assigned by the function if the conditional test results in a 1/True scenario. The value assigned can be a number or string; we will assign it the term "Restrict," as we wish, for the purposes of our example, to create a list of bread-laden foods to provide to customers who want to know which products that are not suggested for gluten-intolerant individuals.

Finally, the third argument assigns a value for any data that evaluates as False/0 in the conditional test of the first argument. In our example, these products will be classified as unrestricted with regard to consumption by gluten-intolerant individuals.

In this simplified example, we will take as a general rule any product that belongs to the Bread category to be a "Gluten-Watch" item. We will assume, for purposes of our comparisons and conditional expression, that any product for which "Bread" is not an assigned category name, the food is Unrestricted. To summarize, we are 1) performing a conditional test (via the argument found in the IIF function) to ascertain whether the Product Category equals "Bread," and 2) if the Product Category is "Bread," are placing the term "Gluten-Watch" in the MyCalcMem column; if the word "Bread" does not appear as the name of the Product Category, we want to assign the member under examination an "Unrestricted" status.

To achieve our objectives, we perform the following steps:

  1. Clear the Value Expression box for the MyCalcMem Calculated Member.
  2. Type the following into the Value Expression box:

IIF(Ancestor([Product].CurrentMember,[Product].[Product Category]).Name = "Bread","Gluten-Watch","Unrestricted")

  1. Click OK. Review the values that are returned, a part of which is depicted below.

Illustration 28: Assigning Products to Reporting Classes based upon Conditional Tests

Now, say want to increase the scope of our nutritional list to further include the classification of products containing eggs, which might be bad news for people who suffer adverse reactions from various components found in eggs. The use of the OR statement results in the return of a 1 /True if either of the two statements joined by the "OR" is true (conversely, AND requires both to be true to return a 1 /True). We want to build in flexibility here as well and simply label the potentially restricted products as being under "Intolerance Watch," so as to allow us the capability to add other items as more such food groups are inevitably deemed risky for various individuals. Let's continue our example in practical form.

  1. Select the Value property, clicking the ellipses button.
  2. Navigate to the Value Expression box, and clear the expression. Type in the following expression:

IIF(Ancestor([Product].CurrentMember,[Product].[Product Category]).Name = "Bread" OR Ancestor([Product].CurrentMember,
[Product].[Product Category]).Name = "Eggs","Intolerance Watch", "Unrestricted")

  1. Click OK. Review the values that are returned, a part of which is depicted below.

Illustration 29: Extending our Expression with the addition of OR to the Conditional Statement

As we have seen, OR (as well as AND) can add even more power to our MDX expressions.

Page 10: Coming Up in Our Series...

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