Date Formatting Tips with the Group Expert
The Group Expert offers several advanced options for the display of groups. When grouping on a date field, there is an additional option which groups by various time periods, both calendar (day, week, month, etc.) and clock (second, minute, hour). This is a simple method to quickly group records by various reporting time periods.
Grouping on Dates In our sales report, we would like to group sales by week. Open the Group Expert and add the OrderDate field to the “Group by” pane, then click Options. You will notice that there is now a new option on the Common tab (“The section will be printed:”) and a drop-down for a variety of time periods. Choose “for each week” and click OK. The report is now grouped by week based on order date. The Group Name Field is driven by the time period selected. For our weekly grouping, the group name is the week beginning date. Group Name Fields can be formatted just like any report field. We will format this field as 1-MAR-1999 and drop it inside a text field to display “For the week of {Group #1 Name]”.
Options Tab On the Options tab, you can customize the Group Name field that will display in the report. Change the grouping to “for each month” and click on the “Options” tab. Check “Customize Group Name Field” and then “Use a Formula as a Group Name.” Click the formula icon. Create a custom group name displaying the month name with the following formula:
“For the month of ” & MonthName(Month({Orders.Order Date}))
In a report grouped by quarters, the Group Name formula would be:
“Q” & ToText(DatePart(“q”,{Orders.Order Date}),0) & ” ” & ToText(year({Orders.Order Date}),0,””)
“Keep Group Together” will force a group to start at the beginning of the next page if it will not fit on the current page. If the first group is too long to fit on the first page, using “Keep Group Together” will force the group to start on page 2 of the report. To avoid the blank first page, go to File\Report Options… and un-check “Respect Keep Group Together on First Page.”
The other option on the “Options” tab can be very handy. Checking “Repeat Group Header on Each Page” will force the Group Header to be repeated on each page. This can be helpful with long groups that spread over several pages.