There are a lot of options i-net Clear Reports offers when it comes to sorting and grouping data: various sort orders, group name fields and formulas, and hierarchical grouping will be explained using simple and more complex examples.

Grouped data is data that is sorted and broken up into sections according to a certain criterion. In an order list, for example, a group might consist of all the orders placed by the same customer, or of the orders generated by a particular sales representative, or both. i-net Clear Reports can also infer data groupings that don't explicitly exist in the data's source. For example, all data for a specific month and year can be grouped on a report even if the underlying data source does not have the data grouped by month and year.

One of the primary purposes for dividing data into groups in a report is to collect summary information on each group of records instead of running a calculation for all the records in a report. Summary information can be calculated for each defined group in addition to calculating values such as grand totals which include all records in a report. This satisfies the needs of users who want to see data summarized across many levels but also want the ability to see the detailed report data used to compute summary numbers. (For details see chapter "Grouping data")

Sorted data allows to present information in a way that is most helpful to the reader. Sorting is a method of organizing the order in which data appears on your report. (For details see chapter "Sorting data")

Grouping means that elements aggregated by a certain criterion are bundled together. These bundles appear in a certain sort order. A group consists of:

• the group criterion (a "group field")
• the fields that belongs to the group
• an optional field ("group name field") describing the group and additional flags (e.g., "keep group together") which describe the way how the group instances are placed and displayed,
• the sort order of the group instances and an optional back-link to a field which describes the parent node of the group (hierarchical grouping),
• three sections: a group header, a group footer and the detail section. All groups share the same detail area.

A group criterion is a group field which collects certain rows into a group. For example, all rows which have the same customer ID can be grouped together. And in turn these groups can be grouped together by using another criterion. The group field can be any field for example a database field or a formula field.

A group name field can be used to give the group a name. The default name is the name of the group field (i.e. the group criterion, "EmployeeID" in the below example), but it can be any other database or formula field. The group name field will also be displayed in the group tree showing the outline of the groups available.

Additional flags describe the layout of the group. The group can be kept together and the group header area can be repeated on each page.

Associated with the group are:

• a section flag specifying that this section is or is not visible on drill down,
• a section flag specifying that this section is or is not visible in the main (non-drill down) report,
• a group tree which shows an outline of all group instances available in the report.
• a group selection formula

If a report includes groups, the GroupTree displays the report's group names in a hierarchical fashion. The group tree can be used to drill down into a group instance or to select a group instance in the main report. Each group can be examined in detail; you can "Drill Down" to a certain group instance and you will only see the data which makes up the group instance. "Drill Down" is a way to focus on the specific group instance by discarding all records which are outside of the selected group boundaries.

The group-tree draws an outline of the report's group structure showing the group instance's group name field in the nodes. Clicking on the node in the tree with the left mouse button will select this node. When the group instance's group header and group footer are invisible, the node will appear with a magnifying glass indicating that clicking on this node will drill down into the group instance. If not, then clicking on the node will select the appropriate group in the document by placing a red mark on the left side.

In PDF export the group tree is also available. Selecting a node will show the page which has the selected group instance.

In order to drill down into a group instance, right-click on a node and select "drill down". A new tab will appear in the viewer with the name of the group (the value is taken from the group name field). The content of the tab is the partial group tree and the data for the drill-down group only. It is possible to drill down from every tab, and it is possible to drill down from every node in the group tree more than once. The page numbers in the drill down tab start from 1. Embedded objects like charts and cross tabs will only see the data of the part being drilled down to, even if they are placed in the report header or report footer.

You can suppress individual areas in the drill-down tab by using the area flag "visible on drill down". In the drill-down tab, only the areas with the flag set to "visible on drill down" are visible. In the main report only the areas which don't have the flag "invisible" set are visible.

This example explained how to create a simple report with grouped data. To add a new group, open the "Insert" menu and click on the menu item "Group...". The Group Dialog appears. The Group Dialog helps you to create a new group or to change or remove an existing group. If you have several groups, you can specify their order by using the "Up" and "Down" buttons. For instance, you might have two groups, an outer group "sales per year" and an inner group "division" – by switching their order, your outer group will become "division", and your inner group "sales per year".

If you want to delete a group, click on the group you want to delete and click on the "Delete" button. The group will be removed from the groups list.

Example:

1. A group is first of all defined by its field. To set it, simply open up the upper-most drop down list "Grouping" and select the column you wish (say, Employees.EmployeeID). The changes are applied to your report design when you click on the "Ok" button.
2. The report will now have two new areas: "GroupHeader" and "GroupFooter". Now drag and drop the new Group Name Field "#1 : Employees.EmployeeID" into the group header area.
3. Now, drag and drop the following database fields into the "Detail" area between the group header and footer: Employees.EmployeeID and Customers.CompanyName.
4. To highlight the group header you can set a background color in "Section Properties" of this area.

The example is now ready to execute (click "Result" tab button). On the left, you’ll see a group tree with all employee IDs and on the right two group name areas (marked grey), including detail areas with the employee ID and the customers’ company names (see image 1)

You have many options to improve the appearance of your report. If you want a different label for the group, you can check the "Customize Groupname" option in the "Group Properties" tab and enter the field or the formula in order to provide the group’s label (see following image 4).

The data will continue being sorted on Employees.EmployeeID, but instead, you’ll see the employees’ last names in the group tree and in the group header area, instead of ID’s (see following image 5). This will help to make your report more informative.

With a formula as group name it is possible to create even more complex reports.

1. Select "Use Formula Value" (see Illustration 4) and click the formula icon . The Formula Dialog will appear.
2. Enter the following formula: {Employees.FirstName} + " " + {Employees.LastName} and click on the "Ok" button.

To modify this formula you can use all of available columns on the left-upper side. Also are all of the formulas and options are listed there. For more information, see the i-net Designer Help, in the chapter "Formula".

When data is grouped, the following 6 sort and group directions are available. Direction refers to the order in which the values are displayed.

• Ascending
• Descending
• Original
• Specified Sort Order
• Order by Summary
• Hierarchical Sorting

Also you can define the sort order direction over formula. This formula should return a positive integer value. Allowed values: 0 for ascending, 1 for descending and 2 for original (also you can use constants "ascending", "descending" and "original" instead of integer values).

All of other formula will be ignored. There are only fields in this formula permitted, which are constant for the whole report (e.g. database fields are not allowed).

Example for Sort Direction Formula:

if  {?parameter} = 0 then ascending
else if  {?parameter} = 1 then descending
else if  {?parameter} = 2 then original
else -1 //formula will be ignored

Ascending order means the values of group field will be sorted by rule "smallest to largest": 0 to 9, a to z, A to Z, False to True. Special characters, such as spaces or ‘!’ are sorted as all other characters. Non-space characters will be ignored. The program sorts the groups' records in ascending order.

Descending order means largest to smallest. This is reverse to ascending sort order.

Original order is the order the data was originally saved in the database. The program leaves the records in the order in which they appear in their originating database table. Notice the original order in which the data from database comes depend on parameters of the database driver and is not constant.

Usually, data is sorted based on the values from a field or a formula in the report. Sometimes, however, you may not want to group data based on the values found in one of the fields on your report, but would like to specify the order yourself. In this case you can create a custom group.

Custom groups are free form groups that are not necessarily groups by any one field value. You define the groups and you specify the individual values that belong in each.

You need a custom group, for example, if the field you would want to group on does not exist or if the field exists, but you want to select specific values or ranges of values for each group.

For example, say you have three columns (Country, CountryID and City) with following data:

Let’s say you want sort this record geographically by continental areas.

With the following steps you can specify a custom group:

1. Create a new group over CountryID.
2. Set "Specified Sort Order" as the order method for this group.
3. Click on the "Options" button, a new dialog appears (see image 8 above).
4. Add three new group elements with the following formulas:
   1. Group Name: Europe

      {Countries.CountryID} >= 1 and {Countries.CountryID} <= 6

   2. Group Name: America

      {Countries.CountryID} >= 7 and  {Countries.CountryID} <= 12

   3. Group Name: Asia

      {Countries.CountryID} >= 13

This will give the following result:

Now, say you want group this data by the actual continents.

1. Create a second group over Country, and also set specified sort order for this group.
2. Set two group elements with the following formulas:
   1. Group Name: North America:

      {Countries.Country}  = "Canada" or {Countries.Country}  = "USA"

   2. Group Name: South America:

      {Countries.Country}  = "Argentina" or {Countries.Country}  = "Brazil"
          or
      {Countries.Country}  = "Venezuela" or {Countries.Country}  = "Mexico"

   3. Add Groups and Formulas for all other continents you want to see.

Result for two first group formulas:

Note: The group name field or formula will not be used for records which are grouped according to a specified formula – the only records which will use the group name field or formula are those which cannot be assigned to a specific group.

Ordering groups by summarized values is a very useful method to arrange the data to fit your individual wishes. It can be used for sorting and filtering. There are the following types of ordering by summary values:

• All data in ascending order – shows all of the groups arranged from lowest to highest.
• All data in descending order – shows all of the groups arranged from highest to lowest.
• Top N – shows only the N greatest group values, in descending mode.
• Bottom N –shows only the N smallest group values in ascending mode.

For example, say you have a list of customers and would like to see the list ordered by the amount of the orders the customer has made. Then do the following steps:

• Create a group over CustomerID and place a group name field of this group in the Group Header area.
• Create a new sum with the name "Count of orders", with the type of summary set to "Count" and set for the field OrderID. Notice this must not be a running total and this sum must be based on the group CustomerID.

• Place this sum in the Group Footer area.
• Return to the Group Dialog, select the CustomerID group and set "Order by Summary" as its sort order.
• Click on "Options" button, and the dialog "Order by Summary" will appear.
• Select "Count of orders" as the Summary Field (this is the summary field you just created). Now set the sort type and order – here, "All" and sort order "Descending".

This will result in a list of all your customers sorted from highest to lowest amount of orders made.

The Hierarchical Grouping Options command enables you to group data in a report to show hierarchical relationships. This means you can sort information based on the relationship between two fields.

For example, say you want to group data on Employees.EmployeeID. And you want sort employees in hierarchical relationships to their supervisors.

We use the above example to group on the field Employees.EmployeeID and customizing the group name field with a formula. To set hierarchical grouping:

• Select option "Sort Data Hierarchically".
• Enter the field Employees.ReportsTo as a parent field.
• Set 1 cm as "Group Indent" for better clarity (see Illustration 12).
• Place two fields, Employees.EmployeeID and Employees.ReportsTo, into the detail section.

As a result, you’ll see a hierarchical list. The group with employee name "Andrew Fuller" has no value in its ReportsTo column, which is why this is on position one. Columns are sorted into groups when the ReportsTo field of the group equals its own EmployeeID. The group values with same ReportsTo IDs are then sorted by EmployeeID.

You can see the result below in image 13.

Note - the group field and parent field must be of the same type, because their values must be able to be compared. You can not combine the hierarchical sort order with other sort order, such original, specified order and "by summary field" - only with ascending and descending.

A record sorting allows you to set the order in which the records in your report are displayed. In the following example, you have a table with employee names, and will sort the report’s records by their birth date.

• Place two database fields, Employees.FirstName and Employees.LastName, into the detail area.
• Open the "Report" menu and click on "Sort Records...". The Sort Dialog appears. On the left panel you see all available fields and formulas. On the right you’ll first see an empty panel for sort fields.

• Add the Database Field Employees.BirthDate to the Sort Fields panel by selecting it and clicking on the "→" button.
• Execute the report with click on "Result" tab.

You will see a list of employee names, sorted by birth date of the employee.

Note: if you try to add a formula which includes a summary function to the Sort Fields, an error message will be shown - because the summary information is calculated after the all data is sorted and filtered, it is impossible to use this as a Sort Field.

Also you can define sort order direction over formula. This formula should return a positive integer value. Allowed values: 0 for ascending, 1 for descending and 2 for original (also you can use constants "ascending", "descending" and "original" instead of integer values).

All of other formula will be ignored. There are only fields in this formula permitted, which are constant for the whole report (e.g. database fields are not allowed).

Example for Sort Direction Formula:

if {?parameter} = 0 then ascending
else if {?parameter} = 1 then descending
else if {?parameter} = 2 then original
else -1 //formula will be ignored

Drill down is a way to focus on the specific group instance by discarding all records which are outside of the selected group boundaries. The group tree can be used to drill down into a group instance or to select a group instance in the main report.

The group-tree draws an outline of the report's group structure showing the group instance's group name field in the nodes. Clicking on the node in the tree with the left mouse button will invoke an action. When the group instance's group header and group footer are invisible, the node will appear with a magnifying glass indicating that clicking on this node will drill down into the group instance. If not, then clicking on the node will select the appropriate group in the document by placing a red mark on the left side.

In PDF export the group tree is also available. Selecting a node will show the page which has the selected group instance.

In order to drill down into a group instance right-click on a node and select "drill down". A new tab will appear in the viewer with the name of the group (the value is taken from the group name field). The content of the tab is the partial group tree and the data for the drill-down group only. It is possible to drill down from every tab, and it is possible to drill down from every node in the group tree more than once. The page numbers in the drill down tab start from 1, embedded objects like chart and crosstab see only the limited data, even if they are placed in the page header or page footer.

You can suppress individual sections in the drill-down tab by using the section flag "visible on drill down". In the drill-down tab, only the sections with the flag set to "visible on drill down" are visible. In the main report only the sections which don't have the flag "invisible" set are visible. For compatibility with Crystal Reports (TM), we set the "visible on drill down" flag only when suppress is off.

Every on-demand sub-report may have its own drill-down tree. It is possible to print and export every drill down tab as if it were a on-demand sub-report. However, rendering an on-demand sub-report will consume less resources than a drill-down tab, so if performance is an issue, then on-demand sub-reports should be preferred.

It is possible to switch off the group tree in the java viewer either by calling viewer.setHasGroupTree(false) or by changing the appropriate applet parameter. The default value for the applet parameter is true, which means that drill-down is available. If run outside of i-net Designer as a standalone or Servlet version the URL property hasGroupTree=false can be appended to the end of the request URL to switch off the group tree.

It is also possible to switch off the group tree globally by setting the i-net Clear Reports property "Has Group Tree" to false using the Configuration Manager. If this is the case then the drill down tree will never be rendered by i-net Clear Reports and it will arrange to set the applet parameter for the java client so that the client will not request the group tree. If a client requests the group tree while the group tree is switched off on the server-side, the server will respond by sending the name of the current report file only.