Guitar_player (9/15/2014)
Hi,Yes i think i am nearer but i am struck somewhere like " .Parent.Children " was used on a Member.
As in my scenario the Members can be anybosy in the specified level so how can i use this when i have got
Week -> Date ?
If i use the formula as Week.Parent.Children , i am getting an error message that "The PARENT function expects a member expression for the 1 argument. A level expression was used."
So any ideas ?
You're almost there. So in a (typical) dimension with a hierarchy it goes [Dimension].[Hierarchy].[Level].&[Member] so in your case Week is the level. In order for the .Parent.Children thing to work in the way you wish it needs to appear against a date member - that is, the actual date (in whatever format it appears). So in my previous example, you have the dimension name ([Date]), then the hierarchy ([Calendar]), then the level ([Date]) and finally the member ([20070902]) giving [Date].[Calendar].[Date].&[20070922].
I know it seems simpler to do it in SQL but it's actually simpler in MDX as all of the values are already there in your dimension, you just need to point the code in the right location to find them!
Give me a shout if that doesn't make sense. 🙂