Symptom
- How to group on a date field from Monday to Sunday?
- How to group on a date by week using Monday as the starting day?
- How do define the start and end day of the week when grouping by week in Crystal Reports?
- How to group records by a week, where a week goes from Wednesday to Tuesday, instead of the usual Sunday to Saturday?
- When grouping weekly on a date field, Crystal Reports defines a week to be from Sunday to Saturday by default. How to customize the start and end day of a week when grouping on a date field?
Environment
- SAP Crystal Reports 2008
- SAP Crystal Reports 2011
- SAP Crystal Reports 2013
- SAP Crystal Reports 2016
Reproducing the Issue
- In Crystal Reports, create a report off any data source.
- Insert a group on a date field, and group by week.
When refreshing the report, notice it grouped the data by week, where the start day of the week is Sunday.
How to choose a different start day when grouping by week?
Cause
- When grouping by week, Crystal Reports always uses Sunday as the starting day of the week.
- There is unfortunately no option to change the start day of the week when grouping on a date by week.
Resolution
- To customize the start day of the week when grouping on a date in Crystal Reports, create a formula that returns the desired start day of the week, then group on the formula.
- In Crystal Reports designer, create a formula like:
WhileReadingRecords;
Local DateTimeVar tempDate := <INSERT YOUR DATE FIELD HERE>;
// Start day of the week. Values accepted: crSunday, crMonday, crTuesday, crWednesday, crThursday, crFriday, crSaturday
Local NumberVar weekStartDay := crMonday;
// Subtract N number of days to the date.
Local DateVar vDate := Date(tempDate);
vDate - (DayOfWeek(vDate,weekStartDay) - 1)
Notes for the above forrmula:
- Insert the date field where it is written: <INSERT YOUR DATE FIELD HERE>, and
- Set the desired start day of the week. In the above example, we set the start day of the week to Monday (crMonday) - Insert a group based on the above formula.
- In the "Insert Group" window, for the option "The section will be printed", select "for each day" in the drop-down list.
- Click "OK" to create the group.
The report will now group the date by week, with the desired start date for the week.
- Sample report using the above technique to group by week: Group by week.rpt
Keywords
WEEK CUSTOM GROUP DATE DATEADD DAY OF WEEK Crystal Reports Grouping Day of week Dateadd function , c2008585 , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem
Product
Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016
Attachments
Group by week.rpt |