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 you have generated the table. This topic introduces how you can add groups in a table and use the other group features.

This topic contains the following sections:

• 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.

   See a sample UI below.

   

2. Specify the position of the new group by selecting Table or an existing group in the right box, then:
   • For a table using a business view, select a group object in the business view as the group-by field and select the Add button or drag and drop it to the right box. You can also use a dynamic formula used as Group you have created for the business view in the current report as the group-by field.
   • For a table 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 uses a query resource, you can also specify the following:
   • If the group-by field is of the 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 the Move Up or Move Down button. To remove a group, select it and select the Remove button or drag and drop it to the Resources box.
7. Select Finish to apply the settings.

Designer displays one or more GH-GF (group header and group footer) row pairs with the selected group-by fields in the table.

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

You cannot group the following SQL type of data: Db.SQL_BINARY, Db.SQL_BLOB, Db.SQL_CLOB, Db.SQL_LONGVARCHAR, Db.SQL_LONGVARBINARY, Db.SQL_VARBINARY, and Db.SQL_OTHER.

Setting the Sort Manner of the Groups

After you add a group, you need to specify in which manner to sort the groups at the group level.

Ascend

Sorts the groups in ascending order.

Descend

Sorts the groups in descending order.

No Sort

Arranges the groups in their original order.

Special Group

When you select this item, Designer displays the User Defined Group dialog box for you to define how to group your information. This option is only available to tables that use 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 in the User Defined Group dialog box.

There are five groups in the order from A to F. If you want to change the order of the groups, you can also achieve it via the User Defined Group dialog box.

Custom Sort

When you select this item, Designer displays the Custom Sort dialog box for you to define how to sort the groups at current group level. You can specify some fields in the dialog box as the sort-by fields, then Designer sorts the groups 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 Report demo style. The table shows as follows in view mode.

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. Designer displays the Table Wizard dialog box.
2. In the Group screen, 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 box, add the field Hire Date as the sort-by field for the group, keep the default sort order Ascend, then select OK.

   

4. Select Finish in the Table Wizard dialog box 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. To achieve this, you can use the Select N feature. By specifying a Select N condition, you can decide how many records or groups you want to display. 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 box, you can see the text "The whole object" in the In text box. Select an item from the Select N drop-down list.

     

     By default, Designer selects "All", which means that all records are displayed.

     If you select Top N or Bottom N, 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 specify for the parameter is N, when you preview the table, you can find that Designer only retrieves the first or last N records in the whole table.

  3. Select OK to go back to the table wizard.
  4. Select Finish in the table 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 box, you can see that Designer displays the field you have just selected in the In text box. From the Select N drop-down list, select Top N or Bottom N, then specify a number or an Integer-typed parameter to retrieve the first or last N groups in the specified group level.
  3. To display all the other groups that do not match the Select N condition in an additional group (these groups are hidden by default), select 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 table wizard to apply the settings.

• The Select N conditions for the whole data component and each group are not mutually exclusive, so you can set them individually.
• If you use an Integer-typed parameter to define the Top/Bottom N condition dynamically, you should make sure that the parameter has at least one default value that is larger than 0; otherwise, the report users will get exceptions at runtime.

Example: Applying Select N conditions in a table

Based on the table result in the above example, we 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. Designer displays the Table Wizard dialog box.
2. In the Group screen, select the Table node in the right box, and then select the Select N button.
3. In the Select N dialog box, select Top N from the Select N drop-down list and type 2 in the combo box, then select OK.

   

4. In the Table Wizard dialog box, select the group-by field Assigned Region and select the Select N button again.
5. In the Select N dialog box, select Top N, type 2, select the Other checkbox and type Others in the text box, then select OK.
6. Select Finish in the Table Wizard dialog box to accept all settings.
7. Preview the table again. The table now displays only the first 2 records in each group (the group Latin America has only one record), shows the first 2 groups (Latin America and Europe, Middle East, Africa), and combines the records in the rest group into an additional Others group.

   

Applying Special Functions for the Groups

If a group-by field is of the Numeric, String, or Date/Time type, you can select a special function for it in the Special Function column. This is called "grouping data by intervals" in Logi Report. It enables you to group data more clearly and logically, and to summarize data more effectively. However, you can apply this feature to 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. Designer displays the Customized Function dialog box.

     

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

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

     If you want to group data by a range, select 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. Select 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.

  When grouping Numeric-typed data by intervals, Designer 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 result is 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 box.

     

  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 type 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, select Case sensitive when grouping; otherwise, select in which way you want to convert the group names: Uppercase, Lowercase or No Conversion.

     If you select Case sensitive when grouping, Designer selects No Conversion as the default converting method.

  4. Select 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 box.

     

  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 Report takes "1/1/1970 00:00:00" as the offset. If you want to define the offset by yourself, select the Customized Value radio button and then select the calendar button to choose the required offset from the calendar.

  4. Specify which day you want to use as the first day of a week.
  5. Select 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, you can specify the groups you want to display: if a group satisfies the filter condition, you can get all the records in this group; if a group doesn't satisfy the condition, the whole group does not display.

The Group Filter feature applies to page reports 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 Report demo style. Since you do not set any group filter condition, 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. Designer displays the Table Wizard dialog box.
2. In the Group screen, select the group-by field Employee Position and Designer enables the Group Filter button. Select the button to display the Group Filter dialog box.
3. Fill in the following information in the dialog box:

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

   

4. Select OK to close the dialog box and then Finish in the Table Wizard dialog box to apply the settings.
5. Select the View tab to preview the tab. The table only shows the two groups "Marketing" and "Sales Representative". The "Vice President" group 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 box as follows:

When you preview the table, it displays the "Vice President" group only.

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 box as follows:

When you preview the table, it shows the two groups "Sales Representative" and "Vice President".

• In the Value text box of the Group Filter dialog box, you can specify a constant or parameter.
• For String type constants, you can type the string without quotation marks; for Date/Time type constants, you have to follow the Date/Time format specified in the Date Format tab of the Get JDBC Connection Information dialog box.
• You should make sure that the report result contains at least one record; otherwise, Designer throws out an error message.

Previous Topic  Next Topic