Friday, March 23, 2012

Grouping by day of the week?

I am trying to create a report that will help with some trending documentation that I have to put together. What I am trying to do is get a report that would show me for every Monday, Tuesday, etc a count of the number calls taken per day for the entire year. I can get my result set easily enough, it is displaying it that I am having problems with.

The that returns is a typical MSSQL date/time field. I have tried using Weekday, weekdayname etc but it keeps saying that the value returned is out of range. How might I go about setting up my grouping to get a return set approximatly like the following example?

Monday
Jan 2 = 200
Jan 9 = 188

Tuesday
Jan 3 = 203
Jan 10 = 220

Wednesday.....

Something along those lines..

Any help or tips are GREATLY appreciated.

Thank you.What I have always done is created a formula called @.day then created a group on the @.day formula, supressed that formula and created another formula called @.dayofweek and layed it over the top of the day formula to show the name of the day of the week.

@.Day
dayofweek({yourdatabase.POST_DATE})
//create a group on this formula.

@.DayOfWeek
weekdayname({@.Day})
//place this formula on the groupheader of the @.Day group.

the field being used with dayofweek needs to be a date or date time field.

No comments:

Post a Comment