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.
- In Crystal Reports, open the report on which you want to group by an accounting period of 4-4-5
- 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.
- Save the formula.
- 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