MDX - last 7 days

[Andrea Worley]

There is probably an easier way to do this, but I am using
the following snippet:

SELECT DESCENDANTS([OrderDate].[2003].[Quarter 3].
[August],month, SELF_AND_AFTER) on rows ....

which gives:
August
1 $23
2 $35
3 $456
4
.
.
.

until the last OrderDate entry which is today. How can I
just show the last 7 days day-by-day results?

Thank you.

[Jim Kaiser]

From FoodMart for monthly data:

select { time.[1997].[Q3].[8].lag(6) : [Time].[1997].[Q3].[8] }
on columns
from sales
where [measures].[store sales]

gives month 8 and the prior 6 for a total of seven months.

You must assure that both members exist in the cube or an error will be
generated. You could also do this with CurrentMember on your orderdate
dimension if you want to control it from a slicer. There you may want to
test to see that you are drilled to the day level before displaying any
data.

Jim

Post by Andrea Worley
There is probably an easier way to do this, but I am using
SELECT DESCENDANTS([OrderDate].[2003].[Quarter 3].
[August],month, SELF_AND_AFTER) on rows ....
August
1 $23
2 $35
3 $456
4
.
.
.
until the last OrderDate entry which is today. How can I
just show the last 7 days day-by-day results?
Thank you.

[Phil Austin]

You can use VBA functions (such as DateAdd, and Now())
together with the 'StrToMember' MDX function to create a
variety of dynamic date queries and calculated measures.

e.g. for your example:

SELECT { StrToMember ("[OrderDate].[Day].&[" + VBA!Format
(Now(), "yyyymmdd") + "]" )--today
:StrToMember ("[OrderDate].[Day].&[" + VBA!Format (VBA!
DateAdd("d", -7, Now()), "yyyymmdd") + "]" --yesterday
)} ON COLUMNS...

would always give you the last seven days, from today.

It also useful to use similar syntax to create calculated
members, which can provide dynamic totals (though not day-
to-day slices) for various time periods (ie month-to-date,
yesterday) it helps make your MDX query look a lot tidier!

-----Original Message-----
There is probably an easier way to do this, but I am

using

SELECT DESCENDANTS([OrderDate].[2003].[Quarter 3].
[August],month, SELF_AND_AFTER) on rows ....
August
1 $23
2 $35
3 $456
4
..
..
..
until the last OrderDate entry which is today. How can I
just show the last 7 days day-by-day results?
Thank you.
.

[Brian Altmann]

Yo can take advantage of the lastperiods functions:

lastperiods(7,[Time dimension member])

To specify the Time member, you can create a dynamic query
using VBA as Phil suggests, or you can create a Named Set
called Current Day that points to this member. In that
case you need a procedure to update this set. You can find
interesting ideas on this in the SQL Server BI Accelerator
docs. You don't have to use the accelerator, you may just
borrow some ideas.

HTH,
Brian
www.geocities.com/brianaltmann/olap.html

-----Original Message-----
There is probably an easier way to do this, but I am

using

SELECT DESCENDANTS([OrderDate].[2003].[Quarter 3].
[August],month, SELF_AND_AFTER) on rows ....
August
1 $23
2 $35
3 $456
4
..
..
..
until the last OrderDate entry which is today. How can I
just show the last 7 days day-by-day results?
Thank you.
.