1

I have a got a line graph report in SSRS 2008 R2. when I do the grouping in graph I first group by year, then month, then week and I get Sum(Count) as data value.(see below)

Count   Date   Week   Month  Year
10      datex   26     6     2010
15      datey   26     7     2010

However, if I have a piece of data as above, week numbers are duplicated and graph finds dip in one of them. it is because dates belong to same week but different month.

Is there any way to get this around? I am sure it is a known problematic, but could not find much on the internet.

Edit: here is the graph I get enter image description here

Thanks for your help.

AnarchistGeek
  • 3,049
  • 6
  • 32
  • 47
  • How can Week 49 be in October (10) and November (11)? Shouldn't it always be in December? Or does week 49 not mean the 49th week of the year. If not, what does it mean? – Fillet Aug 16 '11 at 08:00
  • Fillet, it was a mistake, sorry for that. I edited the numbers. 26 is 26th of the week by the way. – AnarchistGeek Aug 16 '11 at 08:38
  • OK, now I see what you mean, Week 26 of the year starts in June and finishes in July. Could you post a picture of the graph? – Fillet Aug 16 '11 at 09:25
  • Fillet, screenshot added.. thanks. – AnarchistGeek Aug 16 '11 at 10:53

1 Answers1

2

Before you calculate week/month, adjust the date so that everything that has happened in a week is moved to a specific day in the week (say Wednesday).

For 2010, Jun 30 was a Wednesday, so everything between Jun 28 and Jul 4 would be moved to Jun 30. The week would then be 26, and the month June. The datapoint for week 26 and July will then disappear.

This link might help:

How to get last day of last week in sql?

Community
  • 1
  • 1
Fillet
  • 1,426
  • 12
  • 26