I have a pretty standard time dimension within my cubes(Year, Qtr,
Month, Day). I would like to create either a dimension or named set
that would allow me to use the existing time dimension to only show a
dynamic slice of the data depending on the day, month, etc.

For example I only want to see the last 3 months worth of data, so if
it's Sep, my time slider dimension/named set, would only show July,
August, & September instead of the entire time dimension. The reason
for this is to be able to set up a pivot table in excel and have it
update automatically to the slice of time you want based on the
current date. If I opened up the file next month and refreshed the
data, it would show August, September & October. I don't want to do
this with a view, because certain individuals may only want to use
this slider, whereas others are going to want to use the entire time
dimension, so I can't just limit it in a sql view for the time
dimension to this slice of data.

Does anyone have any idea as to how to create this type of slice using
the existing time dimension and having it dynamically find the date
and create the MDX for the slice as a named set or dimension?

Thanks!

Here's a 2003 post from Chris Webb that may help you:

http://groups.google.com/groups?hl=e...%23V1FezCHA.16
44%40TK2MSFTNGP12
Subject: Re: Time range
View: Complete Thread (5 articles)
Original Format
Newsgroups: microsoft.public.sqlserver.olap
Date: 2003-02-06 05:21:12 PST

Rather than let the user enter a date manually, you might want to do the
following instead: create two Time dimensions in your cube, one where
the
user can select the start of the time range and one where they select
the
end of the time range. You can then use calculated measures to return
the
aggregated values. Here's how you do this:

In your existing cube, call your Time dimension something like 'Start
Date'.
Then copy this dimension, and paste it into the same database; you will
get
a dialog asking you for a new name for this dimension, and you should
call
it something like 'End Date'. Next, create a virtual cube containing all
the
dimensions and measures from your regular cube. Since you can't add your
'End Date' dimension to this virtual cube in Analysis Manager (it isn't
connected with any cube), you need to use some DSO code to add it -
something like the following bit of VBScript is all you need:

Dim myserver
Dim db
Dim vcube
Dim fromdim
Set myserver=createobject("dso.server")
myserver.connect("MyServer")
Set db = myserver.mdstores("MyDatabase")
Set vcube = db.mdstores("MyVirtualCube")
Set fromdim = vcube.dimensions.addnew("End Date")
Vcube.update

You then have a virtual cube with two Time dimensions your users can use
to
set the start and end dates in their Time range. Finally you need to
create
some calculated measures that do the summing - the MDX would be
*something*
like:

SUM({
OPENINGPERIOD([START DATE].[LOWEST LEVEL], [START DATE].CURRENTMEMBER)
:
CLOSINGPERIOD([START DATE].[LOWEST LEVEL], LINKMEMBER([END
DATE].CURRENTMEMBER, [START DATE]))
}, VALIDMEASURE(MEASURES.[MY MEASURE]))

HTH,

Chris

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi,
you can use the Date()-function from VBA to get the current date. Based on
this information you have to build the member name of your time dimension.
With the [member].lag(...) you have the possibility to build the dynamic set
over the last 3 month:

with set [Last3Month] as
'
{ CStr("[TimeDim].[" +Format (CStr(VBA!DATE()), "dd-mm-yyyy") +"]".lag(2),
CStr("[TimeDim].[" +Format (CStr(VBA!DATE()), "dd-mm-yyyy") +"]".lag(1),
CStr("[TimeDim].[" +Format (CStr(VBA!DATE()), "dd-mm-yyyy") +"]"}
'
....

You have to be aware, that the date used for this calculation is always the
system-date of the client!

Regards,
Matthias
"Mitch" <mitchchristensen (AT) yahoo (DOT) com> schrieb im Newsbeitrag
news:97d13eec.0409232224.73af0ed1 (AT) posting (DOT) google.com...