We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Grouping by Date field using parameters

czaz
czaz asked
on
Medium Priority
600 Views
Last Modified: 2012-05-11
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}.
Comment
Watch Question

Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
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
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
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
czazDirector of Systems Administration

Author

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.
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
 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
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

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

mlmcc
czazDirector of Systems Administration

Author

Commented:
I tired it. It gives an error of "A Constant expression is required here" & highlights {elmwood_.Start Time (mm/dd/yyyy)}
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Is  {elmwood_.Start Time (mm/dd/yyyy)} the name of the field? or should you be using
 {elmwood_.Start Time}

mlmcc
czazDirector of Systems Administration

Author

Commented:
The column in the XLS is called  "Start Time (mm/dd/yyyy)" so it is {elmwood_.Start Time (mm/dd/yyyy)}
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
I just tried using something similar and it is accepted.

Can you post the formula you entered?

Where are you using the formula?

mlmcc
czazDirector of Systems Administration

Author

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
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
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
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
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
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Suggest you award

James0628   http:#a35438981
mlmcc  http:#a35469470

mlmcc
czazDirector of Systems Administration

Author

Commented:
Worked great once grouped by formula
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.