CODEWITHSUNDEEP
ssasmdxdimensionsolap-cubessas-2008
uh_big_mike_boi
uh_big_mike_boi

Reputation: 3470

MDX Filter by Members

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]

Upvotes: 3

Views: 7258

Answers (2)

Marc Polizzi
Marc Polizzi

Reputation: 9375

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

Upvotes: 4

findango
findango

Reputation: 1483

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

Upvotes: 2

Related Questions