Previous Topic Next Topic

Grouping the Data in Tables

Data in a table is usually organized into groups based on certain criteria. You can define multi-level groups in your table either when creating it or after the table is generated.

Below is a list of the sections covered in this topic:

• Adding Groups
• Setting the Sort Manner of the Groups
• Specifying Select N Conditions for the Groups
• Applying Special Functions for the Groups
• Filtering the Groups

Adding Groups

1. When creating or editing a table with the table wizard, select the Group screen (for a summary table in a web report and library component, the Columns screen).

   For a table in a page report, you can also select Insert > Group to add groups in the table.

   Below is a sample UI.

   

2. Specify the position of the new group by selecting Table or an existing group in the right box, then:
   • For a table created using a business view, select a group object in the business view as the group-by field and select or drag and drop it to the right box. You can also use a dynamic formula used as Group created for the business view in the current report as the group-by field.
   • For a table created using a query resource, select a DBField in the query resource or a formula or parameter valid to these DBFields in the current catalog as the group-by field and select or drag and drop it to the right box. For the usage about parameters as group-by fields, see Grouping Data Dynamically.
3. In the Sort column, set the sort manner of groups at this group level.
4. Specify the Select N condition for groups at this group level.
5. If the table is created using a query resource in a page report, you can also specify the following:
   • If the group-by field is of Numeric/String/Date/Time type, you can select a special function for it in the Special Function drop-down list.
   • If you want to filter groups at a group level, select the group level and select the Group Filter button to specify the filter conditions.
6. Repeat the above steps to add more groups. To adjust the group levels, select a group and select or . To remove a group, select it and select or drag and drop it to the Resources box.
7. Select Finish to apply the settings.

One or more GH-GF (group header and group footer) row pairs with the selected group-by fields are now displayed in the table.

You can also add groups to a table by means of inserting group columns in the table.

Setting the Sort Manner of the Groups

After a group is added in a table, you need to specify in which manner groups at the group level will be sorted.

Ascend

Groups will be sorted in ascending order.

Descend

Groups will be sorted in descending order.

No Sort

Groups will be arranged in their original order.

Special Group

Selecting this item will bring out the User Defined Group dialog for you to define how to group your information. This option is only available for tables created using query resources.

For example, if you place a field named Score for grouping which contains student scores that range from 0 to 100, and you want to group the students in 5 ranks, namely rank A: 90~100, B: 80~89, C: 70~79, D: 60~69, and E: 0~59. You can set the following with the User Defined Group dialog.

There will be five groups in the order from A to F. If you want to change the order of the groups, you can also do so via the User Defined Group dialog.

Custom Sort

Selecting this item will bring out the Custom Sort dialog for you to define how to sort the groups at current group level. You can specify some fields in the dialog as the sort-by fields, then the groups will be sorted by the values of the first record in each group on the related fields.

The following example shows how to sort groups by a specific field.

Suppose that you have created a table of Group Above type in a page report in the catalog SampleReports.cat saved in <install_root>\Demo\Reports\SampleReports. The table uses the query EmployeeInformation in Data Source 1 of the catalog, displays Name, Hire Date, Employee Position and Notes, is grouped by Assigned Region in Descend order, and applies the Logi JReport demo style.

Now you want to sort the groups based on the hire date information. You can specify the sort manner as follows:

1. Right-click the table and select Table Wizard from the shortcut menu.
2. In the Group screen of the Table Wizard, select Custom Sort from the drop-down list in the Sort column for the group-by field Assigned Region.
3. In the Custom Sort dialog, add the field Hire Date as the sort-by field for the group, set the sort order as Ascend, then select OK.

   

4. Select Finish in the Table Wizard to apply the settings.
5. Select the View tab to preview the table. You can find that the groups are sorted based on their first record's Hire Date values.

   

Specifying Select N Conditions for the Groups

Sometimes, you may want to show data from a range in a group or some groups in a table. To achieve this, you can use the Select N feature. By specifying a Select N condition, you can decide how many records or groups will be displayed in the table. You can also use an Integer-typed parameter to control the value of Select N.

• To display the top or bottom N records in a table:
  1. In the Group screen of the table wizard, select the Table node in the right box, then select the Select N button.
  2. In the Select N dialog, you will see that "The whole object" is displayed in the In text box. Select an item from the Select N drop-down list.

     

     By default, All is selected, which means that all records will be displayed.

     If you select Top N or Bottom N, then you can further specify a number in the combo box below. You can also use an Integer-typed parameter in the current catalog to define the Top/Bottom N condition dynamically. Supposing the number or the value you will specify for the parameter is N, when you view the result, you will find that only the first or last N records in the whole table or in each group (if any group has been defined) are retrieved.

  3. Select OK to go back to the Table Wizard.
  4. Select Finish in the wizard to apply the settings.

• To display the top or bottom N groups in certain group level of a table:
  1. In the Group screen of the table wizard, select the group-by field in the right box, and then select the Select N button.
  2. In the Select N dialog, you will see that the field you have just selected is displayed in the In text box. From the Select N drop-down list, select Top N or Bottom N, and then further specify a number or an Integer-typed parameter. If the number or the parameter value is N, then the first or last N groups in the specified group level will be displayed.
  3. To display all the other groups that do not match the Select N condition (which are by default hidden) in an additional group, check the Other box and type a name for the additional group.
  4. Select OK to go back to the Table Wizard.
  5. Select Finish in the wizard to apply the settings.

Example: Applying Select N conditions in a table

Based on the table result in the above example, we will set two Select N conditions in the table, one for the whole table and the other for the groups in the table.

1. Right-click the table and select Table Wizard from the shortcut menu.
2. In the Group screen of the Table Wizard, select the Table node in the right box, and then select the Select N button.
3. In the Select N dialog, select Top N from the Select N drop-down list and enter 2 in the combo box.

   

4. Select OK to go back to the Table Wizard.
5. Select the group-by field Assigned Region and select the Select N button again.
6. In the Select N dialog, select Top N , enter 2, check the Other box and enter Others in the text box.
7. Select OK to close the Select N dialog, then select Finish in the Table Wizard to accept all settings.
8. Preview the table again. You can find that the first 2 records in each group (the group Latin America has only one record) have been retrieved, and then the first 2 groups (Latin America and Europe, Middle East, Africa) are displayed, with the records in the rest group combined into an additional Others group.

   

Applying Special Functions for the Groups

If a group-by field is of Numeric, String or Date/Time type in a table, you can select a special function for it in the Special Function column. This is called "grouping data by intervals" in Logi JReport. It enables you to group data more clearly and logically, and to summarize data more effectively. However this feature is supported in page reports created using query resources only.

The following is a short description of each special function.

Special functions for Numeric type

Special functions for String type

Special functions for Date/Time type

Customized Function

In the special function list for all the above data types, there is an item called Customize. By selecting this item, you can define a special function by yourself.

• Customizing a special function for Numeric type group-by field
  1. Select a group-by field of the Numeric type and then select Customize in the Special Function drop-down list. The Customized Function dialog appears.

     

  2. Specify the way in which you want to group the data.

     If you want to group the data by intervals, check the By Intervals radio button, then define the interval in the Numerical Value text box.

     If you want to group data by a range, check the Within Range radio button, specify the range in the Within text box and select how to apply the range: apply to increasing data or decreasing data.

  3. Check Keep values outside of the range in special group if you want to put values that do not fall within the specified interval or range in a new group, then give a name for the group in the Special Group Name text box.
  4. Select OK to save the settings.

  Note: When grouping Numeric typed data by intervals, Logi JReport follows this rule: all values in each range are >= the minimum value in the range and < the maximum value in the range, and 0 is the offset for the intervals.

  For example, if you specify to group the following values by intervals of 5: -8, -6, -5, -3, -1, 0, 1, 3, 4, 5, 8, the results will be as follows:

  Ranges	Values
  -10 to -5	-8, -6
  -5 to 0	-5, -3, -1
  0 to 5	0, 1, 3, 4
  5 to 10	5, 8

• Customizing a special function for String type group-by field
  1. Select a group-by field of the String type and then select Customize in the Special Function drop-down list to display the Customized Function dialog.

     

  2. Specify the group intervals as required.

     For example, to group records by the first N letters of the group-by field values, select First from the drop-down list and then enter N in the text box. Note that the number of letters should be an integer no larger than 255.

  3. If you want to distinguish between uppercase and lowercase characters when grouping, check Case sensitive when grouping; otherwise, check in which way you want to convert the group names: Uppercase, Lowercase or No Conversion.

     Note: If you check Case sensitive when grouping, No Conversion will be checked as the default converting method.

  4. Check Keep values outside of the range in special group if you want to put values that do not fall within the specified interval in a new group, then give a name for the group in the Special Group Name text box.
  5. Select OK to save the settings.
• Customizing a special function for Date/Time type group-by field
  1. Select a group-by field of the Date/Time type and then select Customize in the Special Function drop-down list to display the Customized Function dialog.

     

  2. Specify the group intervals by inputting a value in the Time text box and then selecting the required item from the drop-down list behind.
  3. Define the offset with which you want to group the data.

     By default, Logi JReport takes 1/1/1970 00:00:00 as the offset. If you want to define the offset by yourself, check the Customized Value radio button and then select the calendar button to choose the required offset.

  4. Specify which day will be used as the first day of a week as required.
  5. Check Keep values outside of the range in special group if you want to put values that do not fall within the specified interval in a new group, then give a name for the group in the Special Group Name text box.
  6. Select OK to save the settings.

Filtering the Groups

By applying a filter condition to the groups in table, you can specify the groups to display in the table: if a group satisfies the filter condition, all the records in this group will be shown; if a group doesn't satisfy the condition, then the whole group will not be displayed in the table.

The Group Filter feature is supported in page reports created using query resources only.

Suppose that you have created a table of Group Above type in a page report in the catalog SampleReports.cat saved in <install_root>\Demo\Reports\SampleReports. The table uses the query EmployeeInformation in Data Source 1 of the catalog, displays Name, Hire Date, Home Phone and Salary, is grouped by Employee Position in Ascend order, and applies the Logi JReport demo style. Since no group filter condition has been set, there are three groups in the table: Marketing, Sales Representative and Vice President.

Example 1: Showing groups where the lowest employee salary is less than 50000

1. Right-click the table and select Table Wizard from the shortcut menu.
2. In the Group screen of the Table Wizard, select the group-by field Employee Position, the Group Filter button is then activated. Select it to display the Group Filter dialog.
3. Fill in the following information in the dialog:

   Logic	Function	Field Name (before Occurs With)	Field Name (after Occurs With)	Operator	Value	More
   Unchecked	Min	Salary	Salary	<	50000	End

   

4. Select OK to close the dialog and then Finish in the Table Wizard to apply the settings.
5. Select the View tab to preview the tab. You can find that only the two groups Marketing and Sales Representative are displayed, while the group Vice President that does not fit this condition is ignored.

   

Example 2: Showing groups in which the highest-salary employee was hired before Jan. 1, 1989

In this example, you need to set the Group Filter dialog as follows:

When you preview the tab, you will find that this time only the group Vice President is displayed.

Example 3: Showing groups where at least one "not highest" employee salary is larger than or equal to 50000

In this example, you need to set the Group Filter dialog as follows:

When you view the report result, you will find that this time only the two groups Sales Representative and Vice President are displayed.

Previous Topic Next Topic