Selecting a range with MDX

Question

I have an MDX statement with the following members on the row :

NON EMPTY { [Date].[Wcy Week Starting Date].&[2013-11-04T00:00:00]: [Date].[Wcy Week Starting Date].lastchild}

The : in MDX gives me the range. The issue in this case is that the rows being returned include the latest week which is only partially complete. I only need data up to the full previous week.

I'm executing this query in SSRS.

I tried

NON EMPTY { [Date].[Wcy Week Starting Date].&[2013-10-07T00:00:00]: [Date].[Wcy Week Starting Date].lastchild.prevmember}

But this returned the same result.

Any ideas on selecting the week commencing from 07th Oct to 4th Nov (as of today). In the past I've used SSRS expressions to manage this. I wondered if there's a more direct way to do it with MDX.

Can you pass in the end date? For instance NON EMPTY { [Date].[Wcy Week Starting Date].&[2013-10-07T00:00:00]:[Date].[Wcy Week Starting Date].&[2013-11-04T00:00:00] ? Of course use parameters for the dates... Of course MDX will always grab the entire week so if your end date is today, then it will grab the entire week, including the days in the future. — BClaydon
In the past I added an expression and passed this in through a parameter but we need Strtomember() to do this which is not great. I'm sure there is a more direct way to do what I'm trying. I'll try lastchild.prevmember(1) Lee — Leehbi

FrankPl FrankPl · Accepted Answer · 2013-11-15T17:59:23

Add an attribute to you cube, let's assume it is called [Date].[week complete] and has members e. g. "Yes" and "No". This can be made invisible in order not to confuse users. Then you can use the following MDX:

Exists( { [Date].[week complete].[Yes] }, 
        [Date].[Wcy Week Starting Date].&[2013-11-04T00:00:00] : [Date].[Wcy Week Starting Date].lastchild
      )

Selecting a range with MDX

1 Answers