Limiting Results from MDX query

Is there any way to specify that I only
want the last 16 rows of an MDX query?

Thanks.

Depends on the query, and depends what exactly you want to limit. In the simplest case when there are no NON EMPTY clauses, you can modify the select statement from

SELECT set0 on COLUMNS, set1 on ROWS FROM cube

to

SELECT set0 on COLUMNS, Head(set1, 16) on ROWS FROM cube

|||

What I actually need is to have my results
only return values that fall within a certain
date range.

It's kinda complicated.

First I need a cumulative running total of
hours charged, then after that I need only
the last 16 weeks of data. I was thinking
of something like this, but it's not working.

WITH MEMBER [Measures].[CumulativeTimeCharged] AS

'SUM({NULL:[Date].[Week].CurrentMember},[Time Charged])'

SET [SixteenWeeks] AS

'Filter( [Date].[Day].Members,

[Date].[Day] >= STRTOMEMBER(@.graphStartDate, CONSTRAINED) AND

[Date].[Day] <= STRTOMEMBER(@.ToDate, CONSTRAINED))'

SELECT NON EMPTY

{

[Measures].[CumulativeTimeCharged]

} ON COLUMNS,

NON EMPTY

{

(

[Project].[Project Number].[Project Number].ALLMEMBERS *

[SixteenWeeks]

)

} ON ROWS

FROM

(

SELECT

(

Null:STRTOMEMBER(@.ToDate, CONSTRAINED)

) ON COLUMNS

FROM

(

SELECT

(

STRTOSET(@.Project, CONSTRAINED)

) ON COLUMNS

FROM [Timesheet_Cube]

)

)

Any suggestions?|||

Let me explain a little more.
Using the following query:

WITH MEMBER [Measures].[CumulativeTimeCharged] AS 
'SUM({NULL:[Header].[Week Ending].CurrentMember},[Time Charged])'
SELECT NON EMPTY 
 { 
 [Measures].[CumulativeTimeCharged] 
 } ON COLUMNS,
NON EMPTY 
{ 
 (
 [Project].[Project Number].[Project Number].ALLMEMBERS * 
 [Header].[Week Ending].[Week Ending].ALLMEMBERS
 ) 
} ON ROWS 
FROM
(
 SELECT 
 ( 
 STRTOSET(@.Project, CONSTRAINED) 
 ) ON COLUMNS 
 FROM [Timesheet_Cube]
)

I get the results on the screenshot at the following link:

http://picasaweb.google.com/fbalas/ScreenShots/photo#5000287345760862226

As you can see from the above results, I am getting
results from weeks before my desired range and results
after my desired range (which also are pretty useless
because there was no additional time after the cutoff so
the cumulative number is always the same).

Is my problem understandable the way I've presented it?

|||Sorry - I have hard time understanding your requirements. Do you want the running sum to run only from 16 weeks back, or do you want it to run from the beginning, but restrict to the subset of Weeks Ending in the report ?|||

Re Mosha:

Thanks for the reply.
What I'm looking for is more like using Tail() than head.
But if you look in the rest of the thread, my problem now
also includes filtering out records at the end as well.

|||

The second understanding is correct...

I want the cumulative sum to run from the beginning of
time, but I only want the last 16 weeks from the cutoff
time (which will be specified by the user using SSRS).

I will be graphing this data, that's why the user wants
to restrict it to only 16 weeks, so that they can see
the dates on the X-axis. If I were to include all the data,
the dates on the X-axis begin to get to small to read.

|||

I should also mention that I am a relative beginner
with this MDX stuff so if what I am asking is so blatantly
easy that you can't fathom why I didn't try something
completely different... please forgive me ;-)

|||

OK - if you need to get 16 weeks from the current week - you can use

StrToMember(@.CurrentWeek, CONSTRAINED) : StrToMember(@.CurrentWeek, CONSTRAINED).Lead(16)

If you need 16 weeks before the current week - you can use

StrToMember(@.CurrentWeek, CONSTRAINED).Lag(16) : StrToMember(@.CurrentWeek, CONSTRAINED)

HTH

Mosha

|||

Thank you so much Mosha! You just saved me
hours of frustration. I have 3 books now that I will
be going over to hopefully edumacate me on this topic ;-)

For those interested my final solution looked like:

WITH MEMBER [Measures].[CumulativeTimeCharged] AS

'SUM({NULL:[Header].[Week Ending].CurrentMember},[Time Charged])'

SELECT NON EMPTY

{

[Measures].[CumulativeTimeCharged]

} ON COLUMNS,

NON EMPTY

{

(

[Project].[Project Number].[Project Number].ALLMEMBERS *

{

StrToMember(@.ToDate, CONSTRAINED).Lag(15) : StrToMember(@.ToDate, CONSTRAINED)

}

)

} ON ROWS

FROM

(

SELECT

(

STRTOSET(@.Project, CONSTRAINED)

) ON COLUMNS

FROM [Timesheet_Cube]

)

|||

I have 3 books now

Is one of those books mine ? (Fast Track to MDX)

|||Actually yes, one of them is ;-)