Weekly grouping gets split at year end

[pdbowling]

Hello everyone.

I am using BIDS 2008. And SQL Server 2008.

I have an SSRS report that runs great the rest of the year, but has trouble rolling over to the new year.

I am grouping by week. Sunday thru Saturday get grouped together and the next week is separated by the grouping.

12/23/2012 thru 12/29/2012 shows fine but 12/30/2012 thru 12/31/2012 get lumped up by themselves instead of being included in the week of 12/30/2012 thru 01/05/2013. 01/01/2013 thru 01/05/2013 group togther as well.

Is this something that SSRS is just doing on its own? I can find no code telling it to break the week at the year end.
Curious if anyone has run in to this before?

The attached image shows the report (with appropriately blacked out personal information) and the report builder screeen with the groups and group expression.

If you need more information, let me know. I will supply what I can as long as it is not personal information.

Thanks
Patrick

 

[xlbo]

Likely SSRS is doing this on its own. sorry I can;t see your link due to restrictions at work
What are you using to create the "week" you are grouping on? Generally this creates a weeknumber which is by defualt within the year hierarchy. ie. it rolls over at year end from week 53 to week 1

You could look at creating your own WeekNum field using DateDiff

=datediff("W",someStartDate,DateInReport)
and group on that instead - it should be independant of year

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[pdbowling]

This particular report is not using WeekNumber.

It is using the text of the day of the week from a function/expression.

=WeekdayName(Weekday(Fields!schedule_date.Value))

The group is grouped on this expession.

Below is a fairly accurate rendition of how the report prints if that helps. (except the excellent and weird notes. Those were from me.)

Doe, John         John's Specialty
John's location   Full-Time           12/23/12    Sunday     3     -------------                        
                                      -------------------------			|
                                      12/26/12    Monday     5			|
                                      -------------------------			|
                                      12/26/12    Tuesday    5			|
                                      -------------------------			|
                                      12/26/12    Wednesday  3			|
                                      -------------------------			|------Excellent
                                      12/27/12    Thursday   2			|
                                      -------------------------			|
                                      12/28/12    Friday     5			|
                                      -------------------------			|
                                      12/29/12    Saturday   6			|
                                      -------------------------			|
                                      Wk. Totals             29    -------------
---------------------------------------------------------------    -------------
John's location   Full-Time           12/30/12    Sunday     5			|
                                      -------------------------			|
                                      12/31/12    Monday     9			|
                                      -------------------------			|
                                      Wk. Totals             14			|
---------------------------------------------------------------			|
John's location   Full-Time           01/01/12    Tuesday    5			|
                                      -------------------------			|------Weird
                                      01/02/12    Wednesday  3			|
                                      -------------------------			|
                                      01/03/12    Thursday   2			|
                                      -------------------------			|
                                      01/04/12    Friday     2			|
                                      -------------------------			|
                                      01/05/12    Saturday   3			|
                                      -------------------------			|
                                      Wk. Totals             15			|
---------------------------------------------------------------    -------------
_______________________________________________________________
Next person's data

Any further thoughts about how SSRS handles the year cross over are welcome. Works great the rest of the year.

Thank you.
Patrick

 

[xlbo]

I think the issue is because you have a date in your list. SSRS is obviously doing some default grouping based on date hierarchies. Whether or not it should is not a debate for these forums. Can you look at the grouping on teh list / table and let me know what groups are set up and their definition? Also, can you check the "break" options for each group and let me know what they are defined as?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlbo]

basically, what are you physically grouping on to create your weeks? You are not grouping on your expression as that would not create weekly groups - it would create daily groups...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlbo]

I'm going to hazard a guess you are using datepart("ww",Fields!schedule_date.Value) to create your week grouping. If that is the case then that is the problem as it will roll over from 53 to 1 at year end

I guess the question here is how do you want your grouping to work? If you want full weeks starting on a sunday independant of year then you should be able to group on the following:

=iif(DatePart("ww",Fields!dt.Value)=53,1,DatePart("ww",Fields!dt.Value))

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[pdbowling]

Thank you xlbo. I hope this ascii representation of the report designer answers your questions. I will check out your DatePart suggestion now.

Re: The formula above =WeekdayName(Weekday(Fields!schedule_date.Value))

In the dataset, when I right click the field DayOfWeek for field properties, that is the formula inside the <Expr> fx button.

                                      My Report Name
_________________________________________________________________________________________
|         |Employee Name     |Spec             |Hire Date   |Day of      | Total         |
|         |Office            |Status           |Sch.Date    |Week        | Items         |
_________________________________________________________________________________________
|   -     |[resource_group]  |                 |            |            |               |
|  | -    |<Expr1>           |                 |            |            |               |
|  || --- |[Employee_Name]   |[Specialty_Code] |[hire_date] |            |               |
|  ||||| -|[Location_Code]   |<Expr2>          |[sch_dt]    |[DayOfWeek] |[TotalItems]   |
|  |||||| |                  |                 |Wk.Totals   |            |Sum(TotalItems)| 
|  ||||| -|                  |                 |            |            |               |
|  || --- |______________________________________________________________________________|
|  | -    |__________________|_________________|____________|____________|_______________|
|   -     |__________________|_________________|____________|____________|_______________|
|         |                  |                 |Grand Totals|            |Sum(TotalItems)|
|________________________________________________________________________________________|

Expr1 =iif(Fields!Resource_Category_Code.Value="A","Field Staff",iif(Fields!Resource_Category_Code.Value="H","Case Managers and Office Staff",""))
Prints "Full Time Employee"

Expr2 =Fields!caregiver_status.Value &
iif(Fields!DOL_code.Value="",""," (" & trim(Fields!DOL_code.Value) & ")")
Prints "Full Time (FTB)

table1_resource_group     =Fields!resource_group.Value
|
|----->table1_group_resource_category     =Fields!Resource_Category_Code.Value
       |
       |----->table1_group_office     =Fields!Location_Code.Value
              |
              |----->group_specialty     =Fields!Specialty_Code.Value
                     |
                     |----->group_emp_name     =Fields!caregiver_name.Value
                            |
                            |----->group_week_sch_date     =Fields!week_sch_date.Value
                                   |
                                   |----->table1_Details_Group     =Fields!sch_date_sort.Value

sch_date_sort is and expression field in the dataset. =CDate(Fields!sch_dt.Value)
DayOfWeek in the dataset is an expression field as well as noted above. =WeekdayName(Weekday(Fields!schedule_date.Value))

 

[pdbowling]

Oh, and the page break tab has no box checked on any group.

 

[xlbo]

Right - so I think the problem must be with the Fields!week_sch_date.Value field. The ssue is with the group rather than any expression in the details. Can you check and see what that field returns for your dates that roll over year end?


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[pdbowling]

Wonderful insight! I inherited this report from another developer, so it never occured to me that the dataset was a stored procedure. Thank you so much for the guidance. Below is the solution just as you suspected if anyone finds it interesting reading or has a similar situation.

The week_sch_date field was being calculated in the sp, and was just showing as another column in the dataset view in SSRS.

Looking at the sp, I see that the query Was using the week number this way.

convert(varchar,datepart(year,t.sch_dt))+convert(varchar,datepart(wk,t.sch_dt)) week_sch_date,

It was returning a concatenation of the year and the week:
201252
201253
20131

This indeed makes a break point at the new year.

I changed it to the following forcing the week to 1 if it was 53 and adding 1 to the year.

CASE
   WHEN DatePart("ww",t.sch_dt)=53 
   THEN CONVERT(VARCHAR,datepart(year,t.sch_dt)+1)+CONVERT(VARCHAR,1)
   ELSE CONVERT(VARCHAR,datepart(year,t.sch_dt))+CONVERT(VARCHAR,DatePart("ww",t.sch_dt))
END  AS week_sch_date,

 

[xlbo]

No worries - thanks for sharing the solution

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question