Issue regarding MDX Queries

• Ciber2010

  Mr or Mrs. 500

  Points: 550

  More actions

  January 28, 2011 at 4:38 am

  #148322

  SELECT NON EMPTY { [Measures].[Record Count - Dim Employee AS] } ON COLUMNS, NON EMPTY { ([Date].[H_CalendarWeekHierarchy].[Calender Week].ALLMEMBERS * [Date].[Week ID].[Week ID].ALLMEMBERS * [Date].[Month ID].[Month ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Cube_Employee] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

  The Sample Results of the above query are shown below with 2 rows but the requirement is to pick only one row which has the Maximum Week_ID irrespective of number of Week_ID’s available

  CalenderYear CalenderWeek Week_ID Month_ID Record Count

  2011 4 1621 373 2332

  2011 5 1622 373 2332

  Please Give any MDX Query to get the result based on the max Week_ID

  Thanks in advance

• Kees Beimans

  SSC Eights!

  Points: 978

  More actions

  February 13, 2011 at 5:02 pm

  #1286219

  Hi

  Try this

  WITH MEMBER

  [Measures].[WeekIDNo] as [Date].[Week ID].CURRENTMEMBER.PROPERTIES('Member_Value' )

  --Assuming WEEK ID increases..

  SELECT NON EMPTY { [Measures].[Record Count - Dim Employee AS] } ON COLUMNS,

  TOPCOUNT( { ([Date].[H_CalendarWeekHierarchy].[Calender Week].ALLMEMBERS * [Date].[Week ID].[Week ID].MEMBERS * [Date].[Month ID].[Month ID].ALLMEMBERS ) }, 1,[Measures].[WeekIDNo]) DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Cube_Employee] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

  Returns 1 row with the highest week_number if 2 rows are returned with same week_ID

  perform

  Head ( Topcount ( set, 1, numeric value), 1)

  Regards Kees