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 6, 2004

MDX Essentials: String / Numeric Functions: Introducing the IIF() Function - Page 3

By William Pearson


To reinforce our understanding of the basics we have covered so far, we will use the IIF() function in a manner that illustrates its operation within a couple of practice examples. We will first create a query that attempts to meet a business need as stated by a hypothetical group of information consumers. We will then create a second query that illustrates the use of the function in supporting a different business need.

We will call upon the MDX Sample Application again, as our tool to construct and execute the MDX we examine, and to view the results datasets we obtain.

1. Start the MDX Sample Application.

2. Clear the top area (the Query pane) of any queries or remnants that might appear.

3. Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

4. Select the Warehouse cube in the Cube drop-down list box.

Let's assume for our practice example that we have received a request from a group of information consumers in the Finance Department of the FoodMart organization. Their request is for support in the presentation of some performance indicators surrounding Warehouse Profit Margins for the various Product Categories that comprise Foodmart operations. The consumers, who will be the intended audience for the requested data, inform us that the data from which we can derive the requested information is housed within the Warehouse sample cube.

The consumers wish to see the 1998 Warehouse Profit Margin for each of the Product Categories (computed by simply dividing the corresponding figures for Warehouse Profit by Warehouse Sales), and, alongside each margin, they wish to see a Margin Performance Indicator that flags the Profit Margin for each as High or Low Margin, with a fixed threshold of fifty-five percent (55.00%) acting as the basis for the classification. They will use the groupings that they can create from the Margin Performance Indicator for various analytical purposes after they receive the data.

We will begin by composing a simple query to meet this business requirement, which will illustrate the use of the IIF() function, through which we will leverage the string option to generate the Margin Performance Indicator flags. My objective is thus to illustrate use of the function to meet the hypothetical business requirement we have outlined, as a means of reinforcing the concepts in our minds. Once we have activated the concepts, they can be triggered upon meeting a similar situation in our respective business environments.

Let's set about meeting this relatively basic requirement with the following steps:

5. Select File ` New, to create a new query.

6. Type the following query into a new, blank Query pane:

-- MDX26-1:  IIF() Margin Performance Indicator
     [Measures].[Warehouse Margin]
     '([Measures].[Warehouse Profit]/[Measures].[Warehouse Sales])',
          FORMAT_STRING = '#.00%'
     [Measures].[Margin Perf Indicator]
    'IIF(([Measures].[Warehouse Margin] > .55), "High Margin", "Low Margin")'

     {[Measures].[Warehouse Sales], [Measures].[Warehouse Cost], 
        [Measures].[Warehouse Profit], [Measures].[Warehouse Margin],    
        [Measures].[Margin Perf Indicator]} ON COLUMNS,
     CROSSJOIN( {[Warehouse].Children}, 
         {[Product].[Product Category].Members}) ON ROWS

The purpose of the first WITH MEMBER section of the query is to create a calculated member to provide the Warehouse Profit Margin, or "Warehouse Margin," (that is, Warehouse Profit divided by Warehouse Sales) for each of the Product Category members. We use the cell property FORMAT_STRING to format the VALUE cell property for the calculated member, simply to make the information it presents more understandable, with clear indication that it is a percentage value.

The second WITH MEMBER section (calculated member Margin Perf Indicator) contains the focus of our lesson, the IIF() function, which we are using to apply the ">" operator to compare the Profit Margin for each Product Category to our fixed threshold of fifty-five percent (".55" in the function above). We are making this comparison as a means of assigning one of two string values, "High Margin" or "Low Margin."

The SELECT statement simply requests the calculated members, alongside the Warehouse Sales, Warehouse Cost, and Warehouse Profit measures, for each respective Product Category's activity for 1998.

NOTE: For a detailed introduction to calculated members, see the following Database Journal articles: Calculated Members: Introduction and Calculated Members: Further Considerations and Perspectives. In addition, for an introduction to the CrossJoin() function, see Basic Set Functions: The CrossJoin() Function.

7. Execute the query by clicking the Run Query button in the toolbar.

The Results pane is populated by Analysis Services, and the dataset partially shown in Illustration 2 appears.

Illustration 2: Initial Results Dataset (Partial View)

And so we see that the IIF() function is effective in creating the textual flags that the information consumers have requested. In like manner, IIF() can be used to generate many flags of this sort, either string or numeric in type, which can be useful in grouping, conditional formatting and other activities in numerous reporting and analysis contexts. (I have even used the IIF() function in certain client situations to support cell-level security, underlying permission rules, as well as other "less-than-intuitive" uses.) The next logical step within these pursuits is parameterization of the function to achieve ad hoc capabilities, and, although we do not explore that avenue in this article, my Database Journal article Black Belt Components: Ad Hoc Conditional Formatting for OLAP Reports delves into the mechanics of parameterized, conditional formatting, based upon IIF(), from the perspective of MSSQL Server Reporting Services.

Having accomplished our objectives within the example above, let's take a look at another example. We will perform a similar flagging operation, but will use a different approach, whereby we will make use of additional MDX functions within our IIF() statement. First, we will outline the requirement, once again, as put to us by our hypothetical group of information consumers.

Within the Product dimension of the Warehouse cube, once again, the Finance consumers wish to be able to perform another sort of flagging, as a means of supporting a grouping mechanism that is not directly available as part of the Warehouse cube metadata, but that can be generated using a logical rule that is supported. Because of a specialized analysis requirement, the consumers want to be able to generate a report of all Products, by Product Name, that FoodMart handles, but they wish to have in place a flag that classifies each as either "Dairy" or "Non-Dairy." While the "Dairy" classification does, indeed, exist in the metadata, there is no corresponding "Non-Dairy" classification (the "reciprocal" of the Dairy group, as it were).

Because the immediate need to analyze all products based on their "dairy" or "non-dairy" nature is a specialized requirement, which will not be frequent enough to justify the permanent alteration of our cube structure, we deduce that this is an ideal scenario for an IIF() function. The consumers corroborate our opinion further when they tell us that all dairy products contain a "Dairy" member at the Product Category level of the dimensional hierarchy. This is a "rule we can use," through leveraging another handy MDX function, .Name, together with the Ancestor() function, as we shall see in the steps that follow.

8. Select File ` Save As..., name the file MDX26-1, and place it in a meaningful location.

9. Select File ` New, to create a new query.

10.  Type the following query into the Query pane:

-- MDX26-2:  IIF() Used for Flagging Groups
     [Measures].[Dairy List]
          [Product].[Product Category]).Name="Dairy", 
               "Dairy", "Non-Dairy")'
     {[Measures].[Dairy List]} ON COLUMNS,
     {[Product].[Product Name].Members} ON ROWS

The WITH MEMBER section of the query again creates a calculated member, containing the IIF() function, with which we are applying the "equals" ("=") conditional operator, and stating that, if an ancestor of the Product current member, residing at the Product Category level, contains the Name "Dairy," then the calculated member will assume the value "Dairy;" if the current member of the Product dimension has a Product Category level ancestor whose Name does not evaluate to "Dairy," then assign the value "Non-Dairy."

The SELECT statement requests the calculated member, to be juxtaposed against each of the Product Names that appear on the rows.

NOTE: For an introduction to the .CurrentMember and Ancestor() functions, see MDX Member Functions: "Relative" Member Functions and MDX Member Functions: The "Family" Functions, respectively, within my Database Journal MDX Essentials series.

11.  Execute the query by clicking the Run Query button in the toolbar.

The Results pane is populated by Analysis Services, and the dataset partially depicted in Illustration 3 appears.

Illustration 3: Second Example Results Dataset (Partial View)

Once again, IIF() function proves effective in creating the string values that the information consumers have requested. Its combination with the Ancestor() and .Name functions has, in this example, provided us the capability to support the external grouping mechanism that the intended audience will find useful in the specialized reporting and analysis tasks that it has identified, basing the new classification upon a rule that is metadata based. The IIF() function, in combination with other functions such as those we have seen, can often be used in this manner to extend the cube structure to meet such specialized needs.

12.  Select File ` Save As..., name the file MDX26-1, and place it in a meaningful location.


In this article, we explored the IIF() function, discussing its primary purpose, as well as touching upon its far-reaching capabilities, particularly when used in conjunction with other MDX functions. We discussed generalities about the use of the function, and then began practice examples that focused on two basic uses of IIF() to meet hypothetical business requirements that mirrored needs we might find in the "real world."

In addition to introducing the IIF() function in an exercise where we might see its power in returning values based upon a fixed numeric condition we imposed, we showed its use in delivering specific values based upon a rule we defined in the cube metadata. In both examples, we observed that the IIF() function assigned values based upon a conditional expression, as part of its operation. We examined the syntax surrounding the IIF() function before beginning our practice exercises, discussing the syntax for each of the two available return value options. Finally, we discussed the results datasets we obtained in each of the examples, upon executing the respective queries we had constructed.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

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