Friday, December 31, 2010

.CurrentOrdinal (Iteration in MDX)

Today I was trying to understand one of the queries found in MSDN for currentordinal and was keen to share my understanding. It was good to see how iteration is happening here and how the current co-ordinate values can be checked against other values.


WITH SET [PrdTies] AS 
Filter
 (
       Order
       (
           NonEmpty
           (
              {
                      [Product].[Product Categories].[Product].&[471], --4079
                      [Product].[Product Categories].[Product].&[433], --787
                      [Product].[Product Categories].[Product].&[434], --648
                      [Product].[Product Categories].[Product].&[475]  --787
              } 
              ,[Measures].[Reseller Order Quantity]
       )
      ,[Measures].[Reseller Order Quantity]
      ,BDESC
 ) AS OrdPrds,
 --NOT --commented for now.
 (
       OrdPrds.CurrentOrdinal < OrdPrds.Count 
       AND 
       [Measures].[Reseller Order Quantity]= 
       ( [Measures].[Reseller Order Quantity],OrdPrds.Item(OrdPrds.CurrentOrdinal)) 
       --Compares whether currentmember measure value = measure value for any other member
       --The current member value is compared with value of members after the current coordinate
        --As the member value is compared with members after current coordinate, we do not need to consider  
          the last member
       --Now this gets only 433
 )
OR 
(
        OrdPrds.CurrentOrdinal > 1 
       AND [Measures].[Reseller Order Quantity] = 
       ([Measures].[Reseller Order Quantity], OrdPrds.Item(OrdPrds.CurrentOrdinal-2))
      --but the value of 475 is same as 433. It does not come in first part. but this also qualifies
      --so compare with previous member by doing -2 (-1 compares with self)
)
 )
 
SELECT {[Measures].[Reseller Order Quantity]} ON 0, [PrdTies] ON 1
FROM [Adventure Works]
Additionally you might want to look at this

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

LinkWithin

Related Posts with Thumbnails