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

Procedure: Use the IsGeneration() Function to Perform Conditional Logic within a Calculation

Per the request of our client colleagues, we will first construct a simple query to provide an illustration of the use of the IsGeneration() function within a common context, the definition of a calculation based upon conditional logic. Our initial example will serve as an introduction to a means of ascertaining the presence of a group of members of the Date dimension / Calendar hierarchy (in the immediate case, the calendar years present within the cube), as requested by the analysts. This will serve as a basis for meeting the business requirement to present the simple Internet Order Counts at the desired levels.

-- MDX055-001 ISGENERATION() Function: Conditional Logic in 
--   the Definition of a Calculation
WITH 
MEMBER
   [Measures].[InternetSelectCount]
AS
   IIF(
      ISGENERATION([Date].[Calendar].CURRENTMEMBER,4),
         [Measures].[Internet Order Count],
      NULL)
SELECT
   {[Measures].[InternetSelectCount]}ON AXIS(0),
   NON EMPTY{[Date].[Calendar].MEMBERS} ON AXIS(1)
FROM 
   [Adventure Works]

The Query pane appears, with our input, as depicted in Illustration 3.

The above query returns the Internet Order Count for each member of the Date dimension (Calendar hierarchy) in the cube; we use Non Empty to physically screen the results to show our “focus” Calendar Years. (Any row axis member existing outside generation 4 of the Date.Calendar hierarchy would evaluate to null with respect to the measure we specified in the column axis). Obviously, had we not inserted the Non Empty keyword, we would get all members of the Date dimension, Calendar hierarchy, with those non-generation-number-4 members simply indicating null as a measure value.

Recall that we have said that we might accomplish our ends through alternative methods. The approach we are taking here allows us to parameterize either or both of the Member Expression and Numerical Expression, to accomplish the extended ends of our client colleagues. In doing so, we could set up a hierarchical picklist within Reporting Services, whereby information consumers might select a given date, month, quarter, and so forth, to drive the level whose members’ values are returned, among other possibilities. The obvious advantage is that consumers can dictate the generation number (and, in effect, the dimensional level) upon whose members the “focus” is enacted. In some circumstances, “double leverage” could be provided by modifying the query to do more within a single parameter – which might be seen as a desirable efficiency within the realm of simulated dynamic drilldown effects and so forth.

In the Internet Select Count calculation, we put the IsGeneration() function to work in applying conditional logic to generate the Internet Order Count value: if the current member of the Date dimension / Calendar hierarchy (the Member Expression of our function) resides within the specified generation number (the Numeric Expression of “4,” which we have supplied within the function), then the corresponding Internet Order Count value is presented. Alternatively, we have directed (via the conditional logic of the IIF() function), that if the Member Expression (the current member of the Date.Calendar hierarchy) does not reside within the specified generation number (“4” ), then the Internet Order Count value is returned as null. (Moreover, as we have noted, while we might have displayed all values, including nulls, we eliminated nulls in our present exercise by preceding the rows specification with the NON EMPTY keyword.)

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

In the partial view of the returned dataset, we see that the calculation accomplishes the intended purpose - generating the Internet Order Count for the individual Date.Calendar hierarchy members that belong to generation number 4 (the Calendar Year level). Again, the conditional test of generation membership is applied via a calculated member within which we have leveraged the IsGeneration() function.

Our client colleagues express satisfaction with the contextual backdrop we have established for introducing the IsGeneration() function. We will next use a similar query within another such example, to confirm understanding of the concepts. This query will provide an illustration of the use of the IsGeneration() function within the context we have already seen, the definition of a calculated member based upon a comparison. As before, we will base our example upon a local, albeit slightly more sophisticated, scenario posed by the client representatives. Our client colleagues initially posed this scenario in a previous session: In working with the IsLeaf() function, we addressed a need to understand a means, within MDX, of distinguishing leaf-level members within the Date dimension of the Adventure Works cube, from the general membership of the dimension.

In recalling the requirement, the analysts tell us that, among other averages under immediate consideration, one need involves Sales Order Counts. The desired end is a simple average, to be calculated by averaging the number of Sales Orders recorded on the first day of each operating month (typically a dramatically larger number of Sales Orders are taken on the first day), and the number of Orders taken on the last day of the same month. The analysts emphasize that they are aware that more precise averages can be generated. This simple average is adequate for the limited purpose for which it is designed, they tell us, much as similar “quick and dirty” calculations have heretofore been performed manually within the realms of headcount and inventory.

The developers / authors within the group cite the foregoing example as useful, once again, in the exploration of a function with which they are becoming familiar. They would like to extend their understanding of the IsGeneration() function to include its use in identifying leaf members within a selected hierarchy, much as we accomplished together with the IsLeaf() function in my Database Journal articles MDX Operators: The IsLeaf() Operator: Conditional Logic within Calculations and MDX Operators: The IsLeaf() Operator: Conditional Logic within Filter Expressions. Per the request of our client colleagues, we will construct a simple query to provide an illustration of the use of the IsGeneration() operator, once again within the definition of a calculation based upon conditional logic, to meet this end.

-- MDX055-002 IsGeneration() Operator:  Conditional Logic in Definition of a Calculation
WITH 
MEMBER
   [Measures].[BegCount]
AS
  
   IIF(ISGENERATION([Date].[Calendar].CURRENTMEMBER, 0),
       [Measures].[Order Count],
   ([Date].[Calendar].CURRENTMEMBER.FIRSTCHILD, [Measures].[BegCount]))
  SELECT
   {[Measures].[BegCount]}ON COLUMNS,
   {[Date].[Calendar].MEMBERS} ON ROWS
FROM 
   [Adventure Works]

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

The above query supplies the Order Count for each descendant of each Calendar Month in the cube, and, in effect, the basis for the simple average that our client colleagues have requested. Within the calculation involved, we put the IsGeneration() function to work – much as we did IsLeaf() in the first of the two articles we referenced earlier - to apply conditional logic to generate the Order Count value, if the Current Member of the Date dimension / Calendar hierarchy is a leaf-level member (meaning a day / date).

The expression IsGeneration([Date].[Calendar].CurrrentMember, 0), is equivalent to IsLeaf([Date].[Calendar].CurrrentMember). We have directed, via the conditional logic of the IIF() function, that if the Current Member is not a leaf-level member, then the Order Count for the First Child member of the Date dimension (Calendar hierarchy), at the level of the Current Member, applies.

We have specified that the Calendar Date members are to populate the rows axis. This provides, to some extent, a quick means of reasonability testing of the logic within the calculation that we have defined, as we shall see.

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

In the partial view of the returned dataset, we see that the calculation accomplishes the intended purpose – generating the Order Count for the individual dates (the leaf level - or generation number “0,” for the Date dimension / Calendar hierarchy), while returning the Order Count for the first child member of the Date dimension / Calendar hierarchy, at the level of each current member, for the members that are not at the leaf level.

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 a simple calculation, based upon conditional logic which does different things based upon the “generational membership” of the current member within the Date dimension – Calendar hierarchy. Specifically, for any Date.Calendar member that belongs to the leaf level (generation number “0”), the standard measure Order Count is returned, while for any non-leaf member, calculated measure Beg Count is returned.

Our calculation employs the IsGeneration() function, much in the same manner as we have employed and explained it in our first example: it supports conditional logic to determine the specified “focus” members of the Date dimension / Calendar hierarchy, and then selects the appropriate measure to retrieve, based upon the outcome of this test. We can see each of the Date.Calendar members in the rows axis, alongside the values returned within the measures column of 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 practice example has been met: the creation of a calculation which is dictated by the IsGeneration() 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 dimension / hierarchy member resides within a specified generation number.

Summary ...

In this article, we exposed another logical function contained within the MDX toolset, the IsGeneration() function, whose general purpose, we learned, is to return a value indicating whether or not a member that we specify resides within a generation number that we also specify. We noted that, similar to other logical functions, IsGeneration() can best be employed to apply conditional logic in a couple of ways: as a component within a calculation, or as a component within a filter expression. In this article, we concentrated upon IsGeneration() from the perspective of its use within a calculation. We discussed the straightforward purpose of the function, the manner in which IsGeneration() 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 IsGeneration(), we examined the syntax with which we employ the function. We then undertook illustrative examples whereby we put the IsGeneration() 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.