MDX Filter by Members

Question

This is more of a general MDX question. I want to filter those dates from the reporting date.fiscal hierarcy. I was previously using a calculated member but I just put it in the query, it doesnt return any results.

    SELECT 
               { 
                FILTER (
                         [Reporting Date].[Fiscal].MEMBERS
                        ,[Reporting Date].[Fiscal].currentmember > [Reporting Date].[Fiscal].[Date].&[2013-03-06]
                         AND
                         [Reporting Date].[Fiscal].currentmember < [Reporting Date].[Fiscal].[Date].&[2013-03-11]
                       )

               }   on 1
              , 
               {
                  Measures.Gross
               } on 0
    FROM [Revenue]

Answer 1

This expression is actually evaluating 2 tuples and then perform the comparison on the evaluated value:

[Reporting Date].[Fiscal].currentmember > [Reporting Date].[Fiscal].[Date].&[2013-03-06]

is :

( [Reporting Date].[Fiscal].currentmember ) > ( [Reporting Date].[Fiscal].[Date].&[2013-03-06] )

where the () represent a tuple (i.e., reference to a cell in the cube). So you're comparing the cell value and not the member dates. I guess what you want is someting like:

select ... {[Reporting Date].[Fiscal].[Date].&[2013-03-06] : [Reporting Date].[Fiscal].[Date].&[2013-03-11] } on 1

Answer 2

You can use the MDX range operator : to specify a literal member range

SELECT 
    { Measures.Gross } ON 0,
    {[Reporting Date].[Fiscal].[Date].&[2013-03-06] : [Reporting Date].[Fiscal].[Date].&[2013-03-11] } ON 1
FROM [Revenue]

See documentation here: http://msdn.microsoft.com/en-us/library/ms146001.aspx