locked
MDX - putting a list in a where clause that includes a dimension already mention in a crossjoin RRS feed

  • Question

  • SO I have a need to to limit the members of a dimension that get included with a query to just a few.  So that means specifying which ones.  How do I limit the main set based on a defined list of members in one of the dimensions im using.  Im looking at various examples, but dont see anything that helps.

    In the query below, I need to limit the number of dimension members being included for
    CommType and MetricType

    I get errors if I use a list of specific members in a where clause that includes these same dimensions.

    Help?

    WITH 
    MEMBER [Measures].[YTD Actual]
    AS
      SUM({[DimCalendar].[Month Year].&[2015]&[1]  : STRTOMEMBER('[DimCalendar].[Month Year].&[' + CSTR(YEAR(NOW())) + ']&[5]') }
         , [Measures].[Actual]
      )
    MEMBER [Measures].[YTD Goal]
    AS
      SUM({[DimCalendar].[Month Year].&[2015]&[1]  : STRTOMEMBER('[DimCalendar].[Month Year].&[' + CSTR(YEAR(NOW())) + ']&[5]') }
         , [Measures].[MTarget]
      )
    MEMBER [Measures].[YE Projected]
    AS
      SUM({[DimCalendar].[Month Year].&[2015]&[1]  : STRTOMEMBER('[DimCalendar].[Month Year].&[' + CSTR(YEAR(NOW())) + ']&[12]') }
         , [Measures].[Projected]
      )
    MEMBER [Measures].[YE Goal]
    AS
      SUM({[DimCalendar].[Month Year].&[2015]&[1]  : STRTOMEMBER('[DimCalendar].[Month Year].&[' + CSTR(YEAR(NOW())) + ']&[12]') }
         , [Measures].[MTarget]
      )
    
    MEMBER [Measures].[Next Year Goal]
    AS
      SUM({[DimCalendar].[Month Year].&[2016]&[1]  : STRTOMEMBER('[DimCalendar].[Month Year].&[' + CSTR(YEAR(NOW()) + 1) + ']&[12]') }
         , [Measures].[MTarget]
      )
    
    -- Now select our calculated members
    SELECT { [Measures].[YTD Actual], [Measures].[YTD Goal], [Measures].[YE Projected], [Measures].[YE Goal], [Measures].[Next Year Goal]} ON COLUMNS,
    {[BusinessLine].[BusinessLine].[BusinessLine] * [CommType].[CommType].[CommType] *  ([MetricType].[MetricType].[MetricType] ) } ON ROWS
    FROM [MySalesCube];
    





    • Edited by shiftbit Tuesday, September 22, 2015 1:47 PM fghdfgfgh
    Tuesday, September 22, 2015 1:11 PM

Answers

  • Actually, after reading a bit on using crossjoin and where etc, I managed to rewrite the above as this:

    Each member in the cross join is a set expression, I just needed to limit the set to some specific members.

     SELECT {[Measures].[MTarget]} ON COLUMNS,
    {[BusinessLine].[BusinessLine].[BusinessLine] * {[MetricType].[MetricType].&[2] , [MetricType].[MetricType].&[4]} } ON ROWS
     FROM [MySalesCube]


    • Marked as answer by shiftbit Tuesday, September 22, 2015 2:15 PM
    Tuesday, September 22, 2015 2:14 PM

All replies

  • You could try the sub-query method and that should help you restrict the members of a dimension.
    Something like this :

    select measures.sales on 0,
    [Date].[Calendar].[Year].members on rows
    FROM (
    select 
    [Date].[Calendar].&[20013]:
    [Date].[Calendar].&[2015]
     on 0
    from  testcube
    )

    Hope it helps.
    Tuesday, September 22, 2015 1:36 PM
  • Thanks,

    How about a simpler example like this

     SELECT {[Measures].[MTarget]} ON COLUMNS,
    {[BusinessLine].[BusinessLine].[BusinessLine] * [MetricType].[MetricType].[MetricType] } ON ROWS
     FROM [MySalesCube]
     WHERE ( {[MetricType].[MetricType].&[2] , [MetricType].[MetricType].&[4]} )

    I get

    "The MetricType hierarchy already appears in the Axis1 axis."

    I need the dimension in the main result, but i need to limit its members as well

    Also, there will probably be another dimension that needs filtered as well


    • Edited by shiftbit Tuesday, September 22, 2015 2:00 PM dfndfdfn
    Tuesday, September 22, 2015 1:59 PM
  • Update your SELECT Query as :

    SELECT { [Measures].[YTD Actual], [Measures].[YTD Goal], [Measures].[YE Projected], [Measures].[YE Goal], [Measures].[Next Year Goal]} ON COLUMNS,
    {[BusinessLine].[BusinessLine].[BusinessLine] * [CommType].[CommType].[CommType] *  ([MetricType].[MetricType].[MetricType] ) } ON ROWS
    FROM
    (
    SELECT  

    { [CommType].[CommType].[CommType] } \\ restrict the members list for commtype here by specifying only the members required
    *  
    {[MetricType].[MetricType].[MetricType] }  \\ same goes for Metrictype
    on 0

    FROM [MySalesCube]
    )

    Try it.

    Tuesday, September 22, 2015 2:10 PM
  • Actually, after reading a bit on using crossjoin and where etc, I managed to rewrite the above as this:

    Each member in the cross join is a set expression, I just needed to limit the set to some specific members.

     SELECT {[Measures].[MTarget]} ON COLUMNS,
    {[BusinessLine].[BusinessLine].[BusinessLine] * {[MetricType].[MetricType].&[2] , [MetricType].[MetricType].&[4]} } ON ROWS
     FROM [MySalesCube]


    • Marked as answer by shiftbit Tuesday, September 22, 2015 2:15 PM
    Tuesday, September 22, 2015 2:14 PM