none
Problem with MDX query date range

    Question

  • I have a query which works for fixed starting data. I am using this for SSRS report. But when I try to make the starting date dynamic it doesn't work. 

    The working query is:

    with MEMBER [Work Item].[System_CreatedDate__HierarchyByMonth].[Month] AS format(DATEADD("YYYY",-4,cdate(NOW())),"yyyy-MM-dd")   SELECT NON EMPTY { [Measures].[Work Item Count] } ON COLUMNS, NON EMPTY { ([Work Item].[System_CreatedDate__HierarchyByMonth].[Month].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( -{ [Work Item].[System_State].&[Removed] } ) ON COLUMNS FROM ( SELECT( [Work Item].[System_CreatedDate__HierarchyByMonth].[Month].&[2013-06-01T00:00:00] : StrToMember("[Work Item].[System_CreatedDate__HierarchyByMonth].[Month].&["+Format(now(), "yyyy-MM-dd")+"T00:00:00"+"]") ) ON COLUMNS FROM ( SELECT ( { [Work Item].[System_WorkItemType].&[Bug] } ) ON COLUMNS FROM ( SELECT ( { [Work Item].[Area Hierarchy].[Area0].&[7538706000617605705]&[-6966572840155938178] } ) ON COLUMNS FROM [Team System])))) WHERE ( [Work Item].[Area Hierarchy].[Area0].&[7538706000617605705]&[-6966572840155938178], [Work Item].[System_WorkItemType].&[Bug] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

    When I replace
    SELECT( [Work Item].[System_CreatedDate__HierarchyByMonth].[Month].&[2013-06-01T00:00:00] : StrToMember("[Work Item].[System_CreatedDate__HierarchyByMonth].[Month].&["+Format(now(), "yyyy-MM-dd")+"T00:00:00"+"]")

    this part with

    SELECT( StrToMember("[Work Item].[System_CreatedDate__HierarchyByMonth].[Month].&["+Format(DATEADD("M",-8,NOW()),"yyyy-MM-ddT00:00:00")+"]") : StrToMember("[Work Item].[System_CreatedDate__HierarchyByMonth].[Month].&["+Format(now(), "yyyy-MM-dd")+"T00:00:00"+"]")

    It doesn't return any row.

    Thanks in advance.

    Friday, March 07, 2014 9:29 AM

All replies

  • I used this query. Hope this is helpful.

    with member CurrentMonth as
      "[Date].[Month].&[" + Format(Now(), "yyyyMM") + "]"
     
      select {[Measures].[Sales TY]} on 0,

      Non Empty
       {StrToMember([Measures].[CurrentMonth]).Lag(@NumberMonths):StrToMember([Measures].[CurrentMonth]).PrevMember}
     
    DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME on  1

       from [transactions]


    prajwal kumar potula

    Tuesday, April 08, 2014 5:21 PM