Logical Functions: IsAncestor(): Conditional Logic within Calculations - Page 3

February 5, 2007

Our client colleagues state that the value we derive from the foregoing formula would ideally appear in our presentation at all levels of the Product Categories hierarchy of the Product dimension. They would like the Product levels and their associated members to appear in the row axis, with the calculation, to be called Tentative Reseller Share, to appear in the column axis along with, and to the right of, the Reseller Sales Amount (the value that already exists in the cube, upon which our calculation is to be based). Formatting of the new calculated value is to appear the same as for Reseller Sales Amount, simple U. S. currency. Finally, our colleagues tell us that they prefer to suppress nulls within the returned data.

We confirm our understanding of the requirement with a quick sketch and then take the following actions:

4.  Select File --> New from the main menu.

5.  Select Query with Current Connection from the cascading menu that appears next, as depicted in Illustration 6.


Illustration 6: Create a New Query with the Current Connection ...

A new tab, with a connection to the Adventure Works cube (we can see it listed in the selector of the Metadata pane, once again) appears in the Query pane.


-- MDX052-002 ISANCESTOR()Function: Conditional Logic in 
--   the Definition of a Calculation
WITH 
MEMBER
   [Measures].[Tentative Reseller Share]
AS
   'SUM(DESCENDANTS([Product].[Product Categories].CURRENTMEMBER,,LEAVES),
   
      IIF(
   
          ISANCESTOR([Product].[Product Categories].[Bikes], 
             [Product].[Product Categories].CURRENTMEMBER),
                0.075 * [Measures].[Reseller Sales Amount],  
             0.0575 * [Measures].[Reseller Sales Amount]
   
          ))',  
      FORMAT_STRING='Currency' 
   
SELECT
   {[Measures].[Reseller Sales Amount], [Measures].[Tentative Reseller Share]}
      ON AXIS(0),
   NON EMPTY {[Product].[Product Categories].MEMBERS} ON AXIS(1)
FROM 
         [Adventure Works]
WHERE
   ([Date].[Calendar].[Calendar Year].[CY 2003])

The Query pane appears, with our input, as shown in Illustration 7.


Illustration 7: Our Initial Query in the Query Pane ...

Note that, in addition to using the IsAncestor() function within the IIF() function, to apply conditional logic in a manner similar to our first example, we employ the SUM() function to aggregate the computed values (simply the required percentages times the pre-existing Reseller Sales Amount measure) across the various Product dimensional levels and members (which we specify via the Descendants() function).

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

The Results pane is, once again, populated by Analysis Services. The dataset, including the 2003 Reseller Sales Amount and Tentative Reseller Share values, appears as partially depicted in Illustration 8.


Illustration 8: Results Dataset (Partial View) – IsAncestor() Function within a Calculation

In the returned dataset, we see that the query appears to meet the business requirements outlined by the client analysts and developers. We have delivered both a standard measure and a simple calculation, based upon conditional logic which applies different multiples, depending upon the dimensional “lineage” of the current member within the Products dimension. Specifically, any member that is a descendent of the Bikes category of the Product dimension is returned a Tentative Reseller Share value that is based upon a different percentage than the same value returned for a member that is not a hierarchical descendent of the Bikes category.

Our calculation employs the IsAncestor() function, much in the same manner as we have employed and explained it in our first example above: it supports conditional logic to determine the specified “focus” members of the Product dimension, and then applies the multiplier value to the Reseller Sales Amount, based upon the outcome of this test. We can see each of the Reseller Sales Amount values involved in the calculation of the respective Tentative Reseller Share within the returned data set, making it easy to verify that our calculations are performing as expected.

The client representatives confirm that the immediate goal of the Tentative Reseller Share calculation has been met: the creation of a calculation which is dictated by the IsAncestor() function in a manner that lends itself to the parameterization opportunities that are expected to arise at the reporting layer. Moreover, they state that the illustration we have provided will be easily extrapolated to other scenarios where they need to perform an action, or to present a value, based upon the outcome of a test as to whether or not a given dimensional member is the descendent of a specified member.

8.  Select File -> Save MDXQuery2.mdx As ..., name the file MDX052-002, and place it in a meaningful location.

9.  Select File -> Exit to leave the SQL Server Management Studio, when ready.

Summary ...

In this article, we exposed another logical function contained within the MDX toolset, the IsAncestor() function, whose general purpose, we learned, is to return a value indicating whether or not a member that we specify is the ancestor of another member we specify. We learned that a significant part of the utility of the IsAncestor() function lies in the fact that it can be used to test whether or not a given member lies within the same dimensional hierarchy as, somewhere between the top (or “all”) level and the level of, another dimensional member that we specify.

We noted that, similar to other logical functions, IsAncestor() can best be employed to apply conditional logic in a couple of primary ways: as a component within a calculation, or as a component within a filter expression. In this article, we concentrated upon IsAncestor() from the perspective of its use within a calculation. We discussed the straightforward purpose of the function, the manner in which IsAncestor() manages to accomplish its purpose, and ways we can leverage the function to support effective conditional logic to meet various business needs within our own environments.

After introducing IsAncestor(), we examined the syntax with which we employ the function. We then undertook illustrative examples whereby we put the IsAncestor() function to work, within a couple of simple illustrations, to meet the business needs of a hypothetical client. Throughout our practice session, we briefly discussed the results datasets we obtained from each of the queries we 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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers