Grouping by Date field using parameters

I am using Crystal Reports 2008.  I have a date time field of "{elmwood_.Start Time (mm/dd/yyyy)} "  that has dates calls were made.  For a report, they would like to see how many calls came in per Month, Week, Day and/or Hour.

I know how to group by that field & can set the selection for Month, Week, Day or Hour.  But since they want to be able to decide the frequency (Day, Week, etc) of the sort, I've set up a parmeter that lets them choose it.

I see in the grouping options, I can use code to decide how to Group Sort Order.  What would the code be to sort the Date field by my parameter of Month, Day, Week Hour?  My parameter  is {?Sort Based On}.
czazDirector of Systems AdministrationAsked:
Who is Participating?
 
James0628Commented:
FWIW, I think you could do this by creating a formula that produced different values depending on the parameter, and grouping on that formula.  But mlmcc's suggestion really seems simpler.

 The formula that I have in mind would be something like:

Select {?Sort Based On}
 Case "Month" :
  CStr ({elmwood_.Start Time (mm/dd/yyyy)} , "yyyy/MM")
 Case "Week" :
// You'd replace the next line with something to create a week number
  ""
 Case "Day" :
  CStr ({elmwood_.Start Time (mm/dd/yyyy)} , "yyyy/MM/dd")
 Case "Hour" :
  CStr ({elmwood_.Start Time (mm/dd/yyyy)} , "yyyy/MM/dd HH")


 For the week, I think you could use something like DateDiff to get a week number and combine that with the year (assuming that the report could include data from more than one year).  I didn't try to work out the details, because mlmcc's suggestion does seem simpler.  But I wanted to throw the idea out there, just in case.

 Another possibility would be a formula like the one above, but it produces a modified datetime value, and you'd group on that formula for each hour.  When grouping by the month, it would include the month and year from your field, and replace the actual day and time with a specific day and time, like the first of the month and 12 AM, so every record in that month would have the same day and time.  When grouping by day, it would include the actual year, month and day, and replace the time with 12 AM.  When grouping by the hour, it would include the actual year, month, day and hour, and replace the minutes with 0.  Again, the week grouping seems to be the tricky one, but it should be doable.  How you did it would depend partly on how you defined the week (Sunday to Saturday, or something else?).

 James
0
 
mlmccCommented:
No, the sort order is simply whether you sort ascending or descending.

The sort is always the same, it is the grouping that has to change.
You need to control the FOR EACH option.

I think the only way to do this is to use 4 groups and control which are viewed through the parameter.

Add 3 groups to the report all on the date field
You should now have groups 1-4
Change the groups so
Group 1 - Set to For Each MONTH
Group 2 - Set to For Each WEEK
Group 3 - Set to For Each DAY
Group 4 - Set to For Each HOUR

Right click group1 in the left margin
Click SECTION EXPERT
Click the FORMULA BUTTON to the right of SUPPRESS
   {?Sort Based On} <> "MONTH"

Similarly for each group change the value tested for

mlmcc
0
 
mlmccCommented:
In some ways I like James answer since it doesnt require the hiding/suppressing of ections.
One advantage of the multiple groups is if you simply hide them you could drill in to see further detail.

mlmcc
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
czazDirector of Systems AdministrationAuthor Commented:
I've tried them both.  mlmcc comes the closest.  However it has some issues when it is sorted by Hour because it is techically sorted by day first.  So 1pm might appear twice, one for each day.
0
 
mlmccCommented:
Select {?Sort Based On}
 Case "Month" :
  CStr ({elmwood_.Start Time (mm/dd/yyyy)} , "yyyy/MM")
 Case "Week" :
  CStr(DatePart('ww', {elmwood_.Start Time (mm/dd/yyyy)}),"0")
 Case "Day" :
  CStr ({elmwood_.Start Time (mm/dd/yyyy)} , "yyyy/MM/dd")
 Case "Hour" :
  CStr (Hour({elmwood_.Start Time (mm/dd/yyyy)}) , "0")

WIll the range cover multiple years?

mlmcc
0
 
James0628Commented:
 czaz,

 I was assuming that each hour on each day was supposed to be separate (and I imagine that mlmcc was thinking the same thing).  As in mlmcc's formula above, if you only want to look at the hour, just leave out the date when converting the datetime to a string.  FWIW, the hour conversion in my formula would be changed to:

 Case "Hour" :
  CStr ({elmwood_.Start Time (mm/dd/yyyy)} , "HH")


 mlmcc,

 Ah.  DatePart.  Thanks for that reminder.  I was thinking that there was a simple way to get the week from a date, but I was just looking at the CStr formatting options and there's no "week" option there.  I forgot about DatePart (I just never need it).

 James
0
 
mlmccCommented:
I don't use it either and saw it when checking something else for the formula.

mlmcc
0
 
czazDirector of Systems AdministrationAuthor Commented:
I tired it. It gives an error of "A Constant expression is required here" & highlights {elmwood_.Start Time (mm/dd/yyyy)}
0
 
mlmccCommented:
Is  {elmwood_.Start Time (mm/dd/yyyy)} the name of the field? or should you be using
 {elmwood_.Start Time}

mlmcc
0
 
czazDirector of Systems AdministrationAuthor Commented:
The column in the XLS is called  "Start Time (mm/dd/yyyy)" so it is {elmwood_.Start Time (mm/dd/yyyy)}
0
 
mlmccCommented:
I just tried using something similar and it is accepted.

Can you post the formula you entered?

Where are you using the formula?

mlmcc
0
 
czazDirector of Systems AdministrationAuthor Commented:
I used the above formula of

Select {?Sort Based On}
 Case "Month" :
  CStr ({elmwood_.Start Time (mm/dd/yyyy)} , "yyyy/MM")
 Case "Week" :
  CStr(DatePart('ww', {elmwood_.Start Time (mm/dd/yyyy)}),"0")
 Case "Day" :
  CStr ({elmwood_.Start Time (mm/dd/yyyy)} , "yyyy/MM/dd")
 Case "Hour" :
  CStr (Hour({elmwood_.Start Time (mm/dd/yyyy)}) , "0")


I am using when i right click on the group & select Change Group.  Then I check the box next to "Use a Formula as Group Sort Order" then click the formula box to enter.
 where formula is entered
0
 
mlmccCommented:
This is a regular formula.

The formula there is just to determine the order - ascending, descending not the field to sort on.

Create a normal formula and use it for the group.

mlmcc
0
 
mlmccCommented:
My comment that was selected as the answer was built on James first comment.  As such you should have split the points between the 2 comments.

I have requested this question be reopened so the point split can be made.

mlmcc
0
 
mlmccCommented:
Suggest you award

James0628   http:#a35438981
mlmcc  http:#a35469470

mlmcc
0
 
czazDirector of Systems AdministrationAuthor Commented:
Worked great once grouped by formula
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.