SQLServerCentral / Discuss Content Posted by Vincent Rainardi / Article Discussions / Article Discussions by Author / Date Dimensions Part 2 / Latest Posts

RE: Date Dimensions Part 2

VincentRainardi — Fri, 20 Feb 2009 03:57:01 GMT

Hi Rick,1. If the week attribute is in yyyy-ww format, we can use datepart() for [This Week]:with member [Transaction Date].[Week].[This Week] asStrToMember("[Transaction Date].[Week].&[" + cstr(datepart("yyyy",now())) + "-" + right("0"+cstr(datepart("ww",now())),2) + "]") select [Transaction Date].[Week].[This Week] on rows, [Measures].[Amount] on columnsfrom [Cube];2. It's member not set. The member definition is between "as" and "select".Ref: 1. http://msdn.microsoft.com/en-us/library/20ee97hz.aspx2. http://technet.microsoft.com/en-us/library/ms146084.aspx

RE: Date Dimensions Part 2

Rick Todd — Thu, 19 Feb 2009 09:06:23 GMT

It looks like to build this into the cube, I have to build them as Sets. This is nice from a presentation perspective, in that they show up in the Sets section in Excel 2007 for their respective dimensions, but means I can't use more than one at the same time.Am I missing something, and I don't have to build them as Sets?

RE: Date Dimensions Part 2

Rick Todd — Thu, 19 Feb 2009 07:46:39 GMT

Anything simple like that for This Week? Not having the "ww" type functionality in the FORMAT(NOW()) makes it a lot more complicated, and googling doesn't seem to bring up anything useful.

RE: Date Dimensions Part 2

VincentRainardi — Wed, 18 Feb 2009 08:47:27 GMT

Thanks Rick. We can create a calculated member such as Yesterday and Last Month like this:with member [Transaction Date].[Date].[Yesterday] asmembers("[Transaction Date].[Date].&[" + Format(DateAdd("d",-1,now()),"yyyy-MM-dd") + "]")select [Transaction Date].[Date].[Yesterday] on rows, [Measures].[Amount] on columnsfrom [Cube];with member [Transaction Date].[Month].[Last Month] asmembers("[Transaction Date].[Month].&[" + Format(DateAdd("m",-1,now()),"MMMM yyyy") + "]")select [Transaction Date].[Month].[Last Month] on rows, [Measures].[Amount] on columnsfrom [Cube];

RE: Date Dimensions Part 2

Rick Todd — Wed, 18 Feb 2009 07:04:27 GMT

Thanks for the great series (if 2 = series) on Date Dimensions. It certainly relates to me, as to just about any Data Warehouse developer. I definitely learned some great tricks that I use regularly now, in both articles.One question: have you found a good solution for moving dates that you can use in Excel 2007 Pivot Tables? Basically a way to tag "yesterday", "this month", "last week", etc., that a user can then create and publish a report with one of those dates as a filter, and when they reload and refresh the report it will maintain the correct date range?Thanks again,

Date Dimensions Part 2

VincentRainardi — Wed, 18 Feb 2009 00:27:24 GMT

Comments posted to this topic are about the item [B]Date Dimensions Part 2[/B]