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.