MDX WHERE Clause

Question

MDX WHERE Clause

mweissx5

Ten Centuries

Points: 1147
More actions
December 7, 2004 at 11:29 pm

#163264

I have a data cube in Analysis Services 2000 with a calendar dimension named Call Date with levels for Year, Quarter, Month and Day. I need to construct an MDX WHERE clause to pull data between two dates, let's say 11/01/2003 and 02/15/2004. I know that to pull data for a particular date I would use:
WHERE [Call Date].[Year] = 2003 AND [Call Date].[Quarter] = 1 AND [Call Date].[Month] = 11 AND [Call Date].[Day] = 15
For the life of me though I can't remember how to write a WHERE clause to pull data between two dates. Any help would be greatly appreciated.
Thank you,
Michael

mhweiss

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

From memory, the where clause entry has to be a tuple, so you can't throw a set in there (which would make life easier), so I think if you create a calculated member you can then reference this in the WHERE clause. I'm pretty sure this is how Excel generates it's MDX when you use the filters area in a pivottable. This of course might produce some problems if you create the calc member as '[mem1] + [mem2] + .... + [memn]' , because doing a simple addition of members may not be valid for the measure rollup. I'm hoping you are using 'sum'

Steve.

mweissx5 Ten Centuries Points: 1147 More actions · Answer 2

Thanks Steve and how is everything down under these days? Seems I remember you could do something like:

WHERE ([Call Date].[Year].[2003].[Month].[11].[Day].[1]:[Call Date].[Year].[2004].[Month].[1].[Day].[30])

Does that ring any bells? I haven't tested this yet and I can't find the original source of that example. However it is always possible that I could just be confused.

Thank you,

Michael

mhweiss

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

Hi Michael, all is well here, have a new job which always brings change and excitement

I think you're right that the member -full colon-member will work, from BOL it would appear that it does require the set to evaluate to a single tuple, so it will use the 'aggregate' fn on it, so again, with standard summing measures, this should work a treat. re: your sample code, it looks like you've got a mix of level and member names in there? i.e. wouldn't [Call Date].[2003].[11].[1]:[Call Date].[2004].[1].[30] be closer to what you want?

Steve.

mweissx5 Ten Centuries Points: 1147 More actions · Answer 4

That's it! That is what I remember now that you mentioned the correct syntax. Thanks Steve! I will test this tomorrow morning...

Thanks, Steve!

Michael

mhweiss