Cascade Skyline - with Microsoft Logo and Project Support header - author Brian Smith

Project Server 2007: Would you rather start your Data Analysis Week on a Monday?

Project Server 2007: Would you rather start your Data Analysis Week on a Monday?

  • Comments 1

Thanks go to more of my colleagues over in EMEA for this posting - Dragos Diamandescu from our Project support team in Romania and Orsolya Gal assisting from SQL team in Germany. (***Update*** - I should also have thanked Marc Biarnes for validating the workaround and bringing it to my attention for a blog topic)

The issue was with the first day of the week in Data Analysis reports.  The customer was seeing Sunday as the first day, but really wanted Monday (For some language versions the default in PWA is Monday – so this is a reasonable request).  As an example, the following project has 8h work on Sunday 14th and 8h on Monday 15th – and in this first view they are showing for the same week – Week 51.  What I really want is for the 8h on the 14th to show as the previous week (50).

image

The dates for the time dimension are held in the reporting database in the MSP_TimeByDay table – and if I retrieve the details for Week 51 using the following query:

Select * from MSP_TimeByDay where TimeYear=2008 and timeweekoftheyear=51

then I see:

image

showing that the 14th is the first day of the week.

If I modify this table using the following TSQL query:

select @@datefirst 
SET DATEFIRST 1 
UPDATE [MSP_TimeByDay] 
SET [TimeDayOfTheWeek] = DATEPART(weekday,timebyday),[TimeWeekOfTheYear] = DATEPART(week,timebyday)

Then my first query returns:

image

I have the 15th as the first “TimeDayOfTheWeek”!  In the T-SQL the DATEFIRST is setting Monday (1) as the first day of the week, rather than Sunday (7) which is returned by the initial Select @@datefirst. After rebuilding my cube I then can see exactly the weekly breakdown I want – withy Sunday 14th showing in Week 50.

image

As usual with database changes we would consider this “unsupported” but it is easy enough to reverse and get back to a “supported” state. I leave it up to you to consider the risks if you need this type of change.  To reverse simply make the datefirst the 7th day of the week.

Select @@datefirst 
SET DATEFIRST 7 
UPDATE [MSP_TimeByDay] 
SET [TimeDayOfTheWeek] = DATEPART(weekday,timebyday),[TimeWeekOfTheYear] = DATEPART(week,timebyday)
 
Technorati Tags:
Leave a Comment
  • Please add 6 and 8 and type the answer here:
  • Post
  • Ok, this is correct but the week number is not correct.

    For example if you go to 04/01/2010 (4 jenuary 2010) the number of the week on TimeWeekOfTheYear is 2 and I would like to change it to 1.

    Could you help me?

    thank you

Page 1 of 1 (1 items)