• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 578
  • Last Modified:

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}.
0
czaz
Asked:
czaz
  • 9
  • 5
  • 2
2 Solutions
 
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
 
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:
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
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 9
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now