CurrentOrdinal (MDX)

APPLIES TO:yesSQL Server Analysis ServicesnoAzure Analysis Services

Returns the current iteration number within a set during iteration.

Syntax


Set_Expression.CurrentOrdinal  

Arguments

Set_Expression
A valid Multidimensional Expressions (MDX) expression that returns a set.

Remarks

When iterating through a set, such as with the Filter (MDX) or Generate (MDX) functions, the CurrentOrdinal function returns the iteration number.

Examples

The following simple example shows how CurrentOrdinal can be used with Generate to return a string containing the name of each item in a set along with its position in the set:

WITH SET MySet AS [Customer].[Customer Geography].[Country].MEMBERS

MEMBER MEASURES.CURRENTORDINALDEMO AS

GENERATE(MySet, CSTR(MySet.CURRENTORDINAL) + ") " + MySet.CURRENT.ITEM(0).NAME, ", ")

SELECT MEASURES.CURRENTORDINALDEMO ON 0

FROM [Adventure Works]

The practical use of CurrentOrdinal is limited to very complex calculations. The following example returns the number of products in the set that are unique, using the Order function to order the non-empty tuples before utilizing the Filter function. The CurrentOrdinal function is used to compare and eliminate ties.

WITH MEMBER [Measures].[PrdTies] AS Count  
   (Filter  
      (Order  
        (NonEmpty  
          ([Product].[Product].[Product].Members  
          , {[Measures].[Reseller Order Quantity]}  
          )  
       , [Measures].[Reseller Order Quantity]  
       , BDESC  
       ) AS OrdPrds  
    , NOT((OrdPrds.CurrentOrdinal < OrdPrds.Count   
       AND [Measures].[Reseller Order Quantity] =   
          ( [Measures].[Reseller Order Quantity]  
            , OrdPrds.Item  
               (OrdPrds.CurrentOrdinal  
               )  
            )  
         )  
         OR (OrdPrds.CurrentOrdinal > 1   
            AND [Measures].[Reseller Order Quantity] =   
               ([Measures].[Reseller Order Quantity]  
               , OrdPrds.Item  
                  (OrdPrds.CurrentOrdinal-2)  
                )  
             )  
          ))  
       )  
SELECT {[Measures].[PrdTies]} ON 0  
FROM [Adventure Works]  

See Also

MDX Function Reference (MDX)