SAP Knowledge Base Article - Public

2617568 - How to group by a 4-4-5 accounting period in Crystal Reports?

Symptom

  • When grouping on a date, is there an option to group by accounting period?
  • How to group the data by a 4-4-5 accounting period in Crystal Reports?

Environment

  • SAP Crystal Reports 2008
  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016

Resolution

  • When grouping on a date field, there is no option to group by accounting period, but you can group on a 4-4-5 accounting period by creating a formula.
           
    1. In Crystal Reports, open the report on which you want to group by an accounting period of 4-4-5
        
    2. Create a formula for the accounting period 4-4-5, like:

        WhileReadingRecords;
        Local DateVar myDate := <INSERT YOUR DATE FIELD HERE>;

        
        Local NumberVar myWeek := DatePart("ww",myDate);
        Local NumberVar myMonth := Switch (myWeek in 01 to 04, 1, // 4
                                                    myWeek in 05 to 08, 2, // 4
                                                    myWeek in 09 to 13, 3, // 5
                                                    myWeek in 14 to 17, 4, // 4
                                                    myWeek in 18 to 21, 5, // 4
                                                    myWeek in 22 to 26, 6, // 5
                                                    myWeek in 27 to 30, 7, // 4
                                                    myWeek in 31 to 34, 8, // 4
                                                    myWeek in 35 to 39, 9, // 5
                                                    myWeek in 40 to 43, 10, // 4
                                                    myWeek in 44 to 47, 11, // 4
                                                    myWeek in 48 to 52, 12, // 5
                                                    myWeek = 53, 1 // 4 

                                              );

         If myWeek < 53 and myMonth in 1 to 12 Then
              Date(Year(myDate),myMonth,1)
         Else
             Date(Year(myDate)+1,myMonth,1)

         
      Notes:
      - Insert in the code above, your database field of data type: Date, where it is written <INSERT YOUR DATE FIELD HERE>
      - The formula will output the first day of the calendar month for each 4-4-5 accounting period.
      - The above formula is an example on how to group by accounting period, but may require modification to fit your specific accounting period definition.
                
            
    3. Save the formula.
          
    4. Under the menu "Insert", select "Group", and group by the formula created in step 2, in ascending order, for each month.

Keywords

CR, accounting period 445 , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To

Product

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