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:

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.

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.

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.

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.