MDX Essentials: String / Numeric Functions: Introducing the IIF() Function - Page 3December 6, 2004 PracticeTo 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 WITH MEMBER [Measures].[Warehouse Margin] AS '([Measures].[Warehouse Profit]/[Measures].[Warehouse Sales])', FORMAT_STRING = '#.00%' MEMBER [Measures].[Margin Perf Indicator] AS 'IIF(([Measures].[Warehouse Margin] > .55), "High Margin", "Low Margin")' SELECT {[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 FROM WAREHOUSE WHERE ([Time].[Year].[1998]) 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. 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 WITH MEMBER [Measures].[Dairy List] AS 'IIF(Ancestor([Product].CurrentMember, [Product].[Product Category]).Name="Dairy", "Dairy", "Non-Dairy")' SELECT {[Measures].[Dairy List]} ON COLUMNS, {[Product].[Product Name].Members} ON ROWS FROM WAREHOUSE 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. 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. Summary...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. MDX Essentials Series
The LEVEL_NUMBER Member Property
The LEVEL_UNIQUE_NAME Intrinsic Member Property Intrinsic Member Properties: The HIERARCHY_UNIQUE_NAME Property Intrinsic Member Properties: The DIMENSION_UNIQUE_NAME Property Further Combination of BottomCount() with Other MDX Functions Combine BottomCount() with Other MDX Functions to Add Sophistication Basic Set Functions: The BottomCount() Function, Part I Intrinsic Member Properties: The MEMBER_VALUE Property Intrinsic Member Properties: The MEMBER_UNIQUE_NAME Property Intrinsic Member Properties: The MEMBER_NAME Property Intrinsic Member Properties: The MEMBER_KEY Property Intrinsic Member Properties: The MEMBER_CAPTION Property Set Functions: The StripCalculatedMembers() Function Set Functions: The AddCalculatedMembers() Function MDX Numeric Functions: The Min() Function MDX Numeric Functions: The Max() Function Set Functions: The .AllMembers Function MDX Essentials: Set Functions: The MeasureGroupMeasures() Function String Functions: The .Properties Function, Part II String Functions: The .Properties Function Logical Functions: IsGeneration(): Conditional Logic within Filter Expressions MDX Scripting Statements: Introducing the Simple CASE Statement Logical Functions: IsGeneration(): Conditional Logic within Calculations Logical Functions: IsAncestor(): Conditional Logic within Filter Expressions MDX Clauses and Keywords: Use HAVING to Filter an Axis Logical Functions: IsAncestor(): Conditional Logic within Calculations Logical Functions: IsSibling(): Conditional Logic within Filter Expressions Logical Functions: IsSibling(): Conditional Logic within Calculations MDX Operators: The IsLeaf() Operator: Conditional Logic within Filter Expressions MDX Operators: The IsLeaf() Operator: Conditional Logic within Calculations MDX Numeric Functions: The .Ordinal Function Other MDX Entities: Perspectives MDX Operators: The IS Operator MDX Set Functions: The Distinct() Function MDX Set Functions: The ToggleDrillState() Function Set Functions: The DrillUpLevel() Function Set Functions: The DrillDownLevelTop() and DrillDownLevelBottom() Functions MDX Set Functions: DrillDownLevel() MDX Set Functions: The DRILLUPMEMBER() Function MDX Essentials: Set Functions: The DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM() Functions MDX Essentials : Set Functions: The DRILLDOWNMEMBER() Function MDX Essentials: Drilling Through with MDX: The DRILLTHROUGH Statement MDX Essentials: String Functions: The .UniqueName Function MDX Essentials: String Functions: The .Name Function MDX Essentials: String / Numeric Functions: The CoalesceEmpty() Function MDX Essentials: Basic Set Functions: The TopCount() Function, Part II MDX Essentials: Basic Set Functions: The TopCount() Function, Part I MDX Essentials: Enhancing CROSSJOIN() with Calculated Members MDX Essentials: Set and String Functions: The GENERATE() Function MDX Essentials: The CROSSJOIN() Function: Breaking Bottlenecks MDX Essentials: String / Numeric Functions: More on the IIF() Function MDX Essentials: String / Numeric Functions: Introducing the IIF() Function MDX Essentials: Logical Functions: The IsEmpty() Function MDX Essentials: Basic Set Functions: The EXTRACT() Function MDX Essentials: Numeric Functions: Introduction to the AVG() Function MDX Essentials: Basic Member Functions: The .Item() Function MDX Essentials: Basic Set Functions: Subset Functions: The Subset() Function MDX Essentials: Basic Set Functions: Subset Functions: The Tail() Function MDX Essentials: Basic Set Functions: Subset Functions: The Head() Function MDX Essentials: Basic Set Functions: The CrossJoin() Function MDX Essentials: Basic Numeric Functions: The Count() Function MDX Essentials: Basic Set Functions: The Filter() Function MDX Essentials: Basic Set Functions: The EXCEPT() Function MDX Essentials: Basic Set Functions: The Intersect() Function MDX Essentials: Basic Set Functions: The Union() Function MDX Essentials: Basic Set Functions: The Order() Function MDX Essentials - MDX Time Series Functions, Part III: The LastPeriods() and ParallelPeriod() Functions MDX Time Series Functions, Part II: The OpeningPeriod () and ClosingPeriod() Functions MDX Essentials - MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions MDX Essentials: MDX Member Functions: "Relative" Member Functions MDX Member Functions: The Cousin () Function MDX Essentials: Member Functions: More "Family" Functions MDX Member Functions: The "Family" Functions MDX Essentials: MDX Members: Introducing Members and Member MDX Essentials : MDX Operators: The Basics MDX Essentials: Structure of the MDX Data Model MDX at First Glance: Introduction to SQL Server MDX Essentials |