Unit 12. Reports (VI).
Sorting and grouping
As we have already seen, with the wizard's help we can define grouping levels for the records in the report and extract a special heading or line of totals for each group, we can also define a determined order for the records that appear in the report.
To define the order of the records, create a new grouping level or modify the levels that we have already defined in an already defined report, we open it in design view and click on the button on the toolbar. The Sorting and grouping dialogue box will open as shown below.
In the Field/expression columnn we indicate the columns which we want to sort or group , and the columns that serve to define groups appear with a symbol to their left. Normally the name of the column is put, but on occasions we can type an expression e.g if we want to group the records of a date field by month, we put the expression =month(datefield). The expression always needs to be preceeded by an = symbol.
The order into which we put the various fields is important. In the previous example we type State, then City, and finally Zipcode, which means that the records will first be gouped by State and within the same state the courses will be sorted by City, and then within the same city will be sorted by zipcode.
We can create up to ten grouping levels, and these levels will be added (one within another) in the same order as what they appear in the Sorting and Grouping box.
In the Sort order column we define the type of order that we want for each field, it can be in Ascending (in alphabetic order if the field is text type, least to most if the field is numeric, and oldest to most recent if it is a date field) or Descending, in inverse order.
In the bottom part we have each grouping and sorting columns properties.
The Group header property is where we indicate whether we want to include a group header, it will contain all the data we want to print only when a group start. If you change the property to Yes you will see that a new section appears in Report design window for the group header.
In the Group footer property we indicate whether we want to include a group footer, and it will contain all the data that we want to print only when the group ends and is normally used to print the group totals. If you change the property to Yes you will see that a new section appears in the report design window for the group footer.
In the Group on property we can choose between .
If we select Each value, it will sort the records in the report by field, and every time the field value changes it will end the group and start a new group of values. If we select Prefix Characters, in the Group interval property we put a n number of characters, and it will group by n first characters in the field.
The Group interval property serves to indicate a number of characters if we have the Group in property with an Prefix Characters value.
It also serves to form groups of a fixed number of records e.g if we want to form groups of five records, we put Each value in the Group on property and we put 5 in the Group interval property.
And lastly we have the Keep together property where we can choose between .
If we select Whole group it will try to write the group header, the detail section, and the group footer on the same page, i.e if after the records of the first group have been printed and there is half a page left empty but the second group does not fit in this space, it will skip the page and start the new group on a new page.
If we select With First Detail it will only print the group header if it can also print the first detail record.
If we select No,
it will be printed without maintaining the group header, the detail section,
and the group footer on the same page.
To practice these operations you can perform the Step by step exercise in creating reports with groups.
Legal warning: Authorised on-line use only. It is not allowed the use of these courses in companies or private teaching centres.
© aulaClic. All rights reserved. Reproduction in any form whatsoever is prohibited. April-2006.