Display all the Dates of a Week of the current date selected

Question

Display all the Dates of a Week of the current date selected

Guitar_player

SSCrazy

Points: 2991
More actions
September 15, 2014 at 3:51 am

#145226

Hi ,
Can anyone tell me a MDX expression that helps me to display the dates of a week . Let me explain it clearly .
I have a hierarchy with Week Level-> Date level . Now when i select a particular date say (10th Sep 2014 ) , all the other dates of a week should also get displayed so my o/p will be
7th, 8th , 9th , 10th , 11th , 12th , 13th of Sep 2014 on X axis and Measures on Y axis ....
Let me know if i m unclear .
Thanks,

Viewing 15 posts - 1 through 14 (of 14 total)

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

PB_BI SSCoach Points: 17461 More actions · Answer 1

You could do this using .Parent.Children. So you are saying "for this date, show me the parent week and then show me the children of that parent week". For example, using a Calendar hierarchy in adventureworks:

SELECT

[Date].[Calendar].[Date].&[20070922].Parent.Children ON 0,

[Measures].[Internet Sales Amount] ON 1

FROM

[Adventure Works]

This would give you all of the other week items for that date (20070922). Note this won't work with adventuresworks out of the box - you have to add week to the calendar hierarchy.

I'm on LinkedIn

Guitar_player SSCrazy Points: 2991 More actions · Answer 2

Hi Bro,

Thanks for your reply .. i hope it can guide me but when i have checked this on the AdventureWorks i am getting the output for the entire month

like from Sept 1 to sept 30th 2007 , but what i need is only for that week like to say for your given example i need the output to be from

16th Sep 2007 to 22nd Sep 2007

If he selects 10th Sep 2014 then i need to get the output from 7th Sep 2014 to 13th Sep 2014

Just check these dates with the calendar then you may get the idea of what my exact requirement is

Thanks.

PB_BI SSCoach Points: 17461 More actions · Answer 3

Yeah, in out of the box AdventureWorks the Calendar hierarchy is Year>Quarter>Month>Date. For this example I added in week to make it Year>Quarter>Month>Week>Date.

The fact that you get the entire month for the same query in AdventureWorks proves it's correct. Try it against your data as you mentioned you have week in your hierarchy.

I'm on LinkedIn

Guitar_player SSCrazy Points: 2991 More actions · Answer 4

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 ?

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

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 ?

With the understanding that I don't even know how to spell "SSAS", there is a T-SQL solution... but I need to know what the first day of the week is for you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Guitar_player SSCrazy Points: 2991 More actions · Answer 6

Guitar_player

SSCrazy

Points: 2991

September 15, 2014 at 11:22 pm

#1745660

its Sunday

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

DECLARE @SomeDate DATETIME;

SELECT @SomeDate = '10 Sep 2014';

--===== This uses a "Goldilocks" (just the right size) Tally table to build

-- a week's worth of dates for and date after 1899-12-31 except the last week

-- of 9999 for almost all versions of SQL Server.

-- This one returns dates for weeks that start on Sunday.

SELECT DatesOfWeek = DATEADD(dd,DATEDIFF(dd,-1,@SomeDate)/7*7,-1) + t.N

FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL

SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6)t(N)

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Guitar_player SSCrazy Points: 2991 More actions · Answer 8

Hi Jeff Moden,

I am really gladful that the query was useful but i need an MDX expression as it is the one which i can use on my one of the BI tools and it doesnt support SQL .

Thanks & Regards,

Guitar_Player

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

Thanks. I'm sure that what I wrote could be adapted for MDX but I've never used MDX before. Sorry.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PB_BI SSCoach Points: 17461 More actions · Answer 10

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. 🙂

I'm on LinkedIn

Guitar_player SSCrazy Points: 2991 More actions · Answer 11

Yes, i thought the same and i have implemented that with Date only..but the problem is with Members ...The member selection of Date is dynamic here so i have added the .Parent.chidlren to that but it wasnt helping

[Date].[Calendar].[Date].[Dynamic Member Selection].Parent.children.

Heres where i got struck when i have implemented the above expression ..anyway what you have provided is correct but i need to check here how can i achieve this by dynamic selection of Date memebers

PB_BI SSCoach Points: 17461 More actions · Answer 12

Guitar_player (9/16/2014)
Yes, i thought the same and i have implemented that with Date only..but the problem is with Members ...The member selection of Date is dynamic here so i have added the .Parent.chidlren to that but it wasnt helping
[Date].[Calendar].[Date].[Dynamic Member Selection].Parent.children.
Heres where i got struck when i have implemented the above expression ..anyway what you have provided is correct but i need to check here how can i achieve this by dynamic selection of Date memebers

When you say it's dynamic, is it consuming a parameter? If so you can use the StrToMember function to make that work, like so:

STRTOMEMBER("[Date].[Calendar].[Date].&["+your parameter+"]").Parent.Children

There's quite a good post on using this technique here: http://blog.datainspirations.com/2010/10/07/using-dynamic-mdx-in-reporting-services-part-1/

I'm on LinkedIn

Greg Edwards-268690 SSC-Insane Points: 20676 More actions · Answer 13

We used dimensions for CurrentDay, CurrentWeek, CurrentMonth, etc. to make this easier for the user to display the Children.

This worked very well for Production and Sales Reports.

For example, YTD, MTD, along with the Current Week in daily buckets to see the progression throughout the week.

A little extra work on the backend can simplify things quite a bit sometimes.

We also had a Future Time dimension, to allow for Production Plan (or other strategic plans) to measure how you are tracking to expectations.

Guitar_player SSCrazy Points: 2991 More actions · Answer 14

When you say it's dynamic, is it consuming a parameter? If so you can use the StrToMember function to make that work, like so:
STRTOMEMBER("[Date].[Calendar].[Date].&["+your parameter+"]").Parent.Children

That did the Trick Sorry for the delay 🙂 it was working fine ..Thanks for the info bro