How do you extract first tuple from the set?

How do you extract first tuple from the set?

Asked on November 13, 2018 in SQL Server.
Add Comment


  • 4 Answer(s)

    1. Using could usefunction Set.Item(0)

     Run a query that has Calendar year set on axis 0:

    SELECT {[Date].[Calendar].[Calendar Year].Members
    }
    ON 0
    FROM [Adventure Works]
    

    Results:

    CY 2001 CY 2002 CY 2003 CY 2004
    $8,065,435.31 $24,144,429.65 $32,202,669.43 $16,038,062.60
    

    Now take first member of the set:

    SELECT {{[Date].[Calendar].[Calendar Year].Members
    }.Item(0)}
    ON 0
    FROM [Adventure Works]
    

    Result:

    CY 2001
    $8,065,435.31
    

     

    Answered on November 13, 2018.
    Add Comment

    Using function Tuple.Item(0)

    MDX Query example:

    SELECT {([Date].[Calendar].[Calendar Year].&[2003]
    , [Customer].[Customer Geography].[Country].&[Canada]).Item(0)
    }
    ON 0
    FROM [Adventure Works]
    

    Result:

    CY 2003
    $32,202,669.43
    

    Different query:

    SELECT {([Date].[Calendar].[Calendar Year].&[2003]
    , [Customer].[Customer Geography].[Country].&[Canada]).Item(1)
    }
    ON 0
    FROM [Adventure Works]
    

    Result is different:

    Canada
    $80,450,596.98
    
    Answered on November 13, 2018.
    Add Comment

    I am new in Mdx queries and I have had a question about mdx, how can Extract first member of tuples in a set az a new set in mdx? I have a query like this:

    WITH
         SET [people in first date] AS   
       'Filter  
          (  
           {[VW Dim Customer Broker Branch].[Customer Title].[Customer Title]*[Vw Dim Date].[Shamsi Date].&[1388/06/01]:[Vw Dim Date].[Shamsi Date].&[1388/06/02]},
           [Measures].[Trade Cnt]>3
          ) ' 
    
           SET [people in second date] AS   
       'Filter  
          (  
           {[VW Dim Customer Broker Branch].[Customer Title].[Customer Title]*[Vw Dim Date].[Shamsi Date].&[1388/06/03]:[Vw Dim Date].[Shamsi Date].&[1388/06/04]},
           [Measures].[Trade Cnt]>0
          ) ' 
    
         /* SET [numberOfFirstItem] As
          [people in first date].item(0)*/
    
         member [measures].[numberOfPeopleInFirstDate] AS
        DistinctCount([people in first date])
    
        member [measures].[numberOfPeopleInFirstDate1] AS
        Count([people in first date])
    
         member [measures].[numberOfPeopleInSecondtDate] AS
         DistinctCount([people in second date])
    
         member [measures].[Ratio] AS 
         '(([measures].[numberOfPeopleInSecondtDate]-[measures].[numberOfPeopleInFirstDate])/IIF([measures].[numberOfPeopleInFirstDate]=0,1,[measures].[numberOfPeopleInFirstDate]))*100'
    
     select
           [Measures].[Trade Cnt] on 0,
           [people in first date] on 1 
           --{[measures].[numberOfPeopleInFirstDate],[measures].[numberOfPeopleInSecondtDate],[measures].[Ratio]} on 0 
          /* {[Measures].[Trade Cnt]} on 0,
           [people in first date] on 1*/
          -- {[numberOfFirstItem]} on 0
    
          from [DVPL]
    
    Answered on January 14, 2019.
    Add Comment

    WITH MEMBER Measures.x AS [Measures].[Internet Sales Amount] / ( [Measures].[Internet Sales Amount], Ancestors ( [Customer].[Customer Geography].CurrentMember, [Customer].[Customer Geography].[Country] ).Item (0) ), FORMAT_STRING = ‘0%’ SELECT {[Measures].[Internet Sales Amount], Measures.x} ON 0, { Descendants ( [Customer].[Customer Geography].[Country].&[Australia], [Customer].[Customer Geography].[State-Province], SELF ) } ON 1 FROM [Adventure Works]

     

    Answered on January 14, 2019.
    Add Comment


  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.