1213036 - How to define the start day of a week when grouping on a date in Crystal Reports?

SAP Knowledge Base Article - Public

1213036 - How to define the start day of a week when grouping on a date in Crystal Reports?

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

  1. In Crystal Reports, create a report off any data source.
  2. 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.
        
    1. 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)
    2. Insert a group based on the above formula.
        
    3. In the "Insert Group" window, for the option "The section will be printed", select "for each day" in the drop-down list.
        
         Group Week - 03.png  
             
             
    4. 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 BusinessViews Manager , Problem

Product

Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016

Attachments

Group by week.rpt