Hi,

I have a Date Dimension Hierarchy as follows:

Date
-Year
-Quarter
-Month
-Week
-Day

Basically i want all the dates (till Day level) between Week 1 and
Week 9 when i do not know in which month the weeks lie.

I am creating a Date Range as [2006].[Quarter 1].[January].[Week 1] :

[2006].[Quarter 1].[February].[Week 9] .

I am however not sure that Week 9 exists in February or March Month .
..So i cannot give a Absolute Month. I need add Filter to this.

How should i write my MDX .?

I know i have to write something like this :

Select {[Measures].[Amount]} on 0 ,

{Filter(Decendants([Date],3), [Date].Currentmember.Parent.Name = "Week
1") : Filter(Decendants([Date],3), [Date].Currentmember.Parent.Name =
"Week 9")} on 1

from AccountSummary

---This query gives a syntactical error . Please let me know how do i
solve this.

TIA..

If you're using AS 2005, you can create multiple hierarchies in a
dimension; so an easier solution will be to create an alternate date
hierarchy without month, like:

Date
-Year
-Quarter
-Week
-Day

More details of the Date hierarchy, level and member names would be
useful, but something like this might work:

{Head(Filter(Descendants([Date].[Year].[2006],
[Date].[Day]),
[Date].Parent.Name = "Week1")).Item(0).Item(0)
: Tail(Filter(Descendants([Date].[Year].[2006],
[Date].[Day]),
[Date].Parent.Name ="Week 9")).Item(0).Item(0)}


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks Deepak.. This solved my problem.