Hi all
Earlier, I posted about an MDX query between two dates.

Please note that my Time dimension is not populated for every possible
member of time. The reason for this is that my time dimension goes down to
minute level, so populating it would be expensive.

Can I query on a dimension to say something like

"gimme all values between x and y". BUT x or y may not be in the dimension.

To make it a little clearer.

Lets say I have time dimension with some values

I have
1997, 1999, 2001, 2002, 2003, 2004

For some reason I dont have 1998 or 2000.
Can I do a query to say

"gimme all values between 1998 and 2003" ?

Remembering that 1998 is not in the database is important.

The reason I need this is because dates are selected by a user, using a
calendar control, and the user will have no way of knowing that a Time value
is present or not.

Thanks in advance

Brian

You can - it's a bit of pain in AS2000, though. Since you can't guarantee
that your upper and lower limits are actual members, use filter. Something
like:

filter(time.year.members, cdate(time.currentmember.name) > cdate("1997") and
cdate(time.currentmember.name) < cdate("2004"))

For example, this works in foodmart:

select filter(time.year.members,
cdate(time.currentmember.name)>cdate("1997")) on 0 from sales

BTW, this will be more elegant and much more performant in AS2005 with the
membervalue function which will return the member in its original data type.

Cheers,
Richard

--
_______________
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.


"Brian Dennehy" <denno88_NOSPAM_ (AT) yahoo (DOT) com> wrote

Thanks
Im using Mondrian ... I guess Mondrian cares that an element is missing.

Anybody know how to do this with Mondrian then ?

"Jörg Narr" <joerg_narr (AT) thedomain (DOT) below> wrote

Hi
Thanks for the help.

What is AS2000 ? (and its lesser known cousin AS2005 ?)

What is CDATE ? Is it an AS200(0/5) specific function ?

I have tried to use filter, but I dont want the time.x.members to appear in
the results, rather I want

SELECT {[Measures].[Average]} ON COLUMNS,
{Filter([MyDimension].Members], [time].[currentmember].[value] > X)} ON ROWS
From [Cube]


Does that look ok ? Can you filter the values of one dimension based on a
<search criteria> for another dimension ?

Thanks in advance


"Richard Tkachuk [MSFT]" <richtk (AT) microsoft (DOT) com> wrote

Sorry for the acronyms.

AS2000 = Analysis Services 2000 and AS2005 = Analysis Services 2005
cdate is one of the vba functions available in MDX that converts values to a
date type (input has to be translatable to a date type - so this is not
generally usable. Your dates need to be formatted properly)

If you don't want time members to appear, then it's not clear to me the
semantics of your query. In Foodmart 2000, if you want products purchased
after a date that may or may not appear, you can do this:

select measures.allmembers on 0,
filter( [Product].[Product Department].members,
tail(nonemptycrossjoin( [Time].[Year].members, {[Product].currentmember},
1), 1).item(0).item(0).name > "1997")
on 1 from sales

Hope this helps,
Richard
--
_______________
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.

"Brian Dennehy" <denno88_NOSPAM_ (AT) yahoo (DOT) com> wrote

If I wanted to get all the products purchased between 1997 and 1999 would I
have to do :

select measures.allmembers on 0,
filter( [Product].[Product Department].members,
(tail(nonemptycrossjoin( [Time].[Year].members, {[Product].currentmember},
1), 1).item(0).item(0).name > "1997") and tail(nonemptycrossjoin(
[Time].[Year].members, {[Product].currentmember},
1), 1).item(0).item(0).name <= "1999")
on 1 from sales

Is that horrible on performance ?

Thanks for your help, it has been very valuable.

What if I inspected the "Time" dimension and found dates that I wanted.

How would I say "give me all products between date A and date B" ?

Where would I put the : operator ?
In the slicer ?

e.g.
Select measures.allmembers on 0
{[Product].[Product Department].members} on 1
Where ([Time].[1997]:[Time].[1999])

Would that be right ?

Thanks in advance
Brian


"Richard Tkachuk [MSFT]" <richtk (AT) microsoft (DOT) com> wrote