Post reply

Issue regarding MDX Queries

  • 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

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply