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}.
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}.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
One advantage of the multiple groups is if you simply hide them you could drill in to see further detail.
mlmcc
ASKER
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
I don't use it either and saw it when checking something else for the formula.
mlmcc
mlmcc
ASKER
I tired it. It gives an error of "A Constant expression is required here" & highlights {elmwood_.Start Time (mm/dd/yyyy)}
Is {elmwood_.Start Time (mm/dd/yyyy)} the name of the field? or should you be using
{elmwood_.Start Time}
mlmcc
{elmwood_.Start Time}
mlmcc
ASKER
The column in the XLS is called "Start Time (mm/dd/yyyy)" so it is {elmwood_.Start Time (mm/dd/yyyy)}
I just tried using something similar and it is accepted.
Can you post the formula you entered?
Where are you using the formula?
mlmcc
Can you post the formula you entered?
Where are you using the formula?
mlmcc
ASKER
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.
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.
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
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
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
I have requested this question be reopened so the point split can be made.
mlmcc
ASKER
Worked great once grouped by formula
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