Grouping by Range

Version

Expected Effect

As a typical advanced report grouping type, grouping by range is used to group data by the value range of data.

For example, data is grouped by a certain value range (such as the score range, age group, and time range), as shown in the following figure.

Implementation Method

You can set Data Setting to Group, select Adjacent, and click Custom to customize groups for a data column.

Report Design

Report Creation

Create a general report, and enter the SQL statement SELECT EMPID, TITLE, cast(strftime('%Y','now') as int)-cast(strftime('%Y',BIRTHDATE) as int)+1 AS Age FROM EMPLOYEE to createa dataset named ds1, as shown in the following figure.

The EMPLOYEE table stores employees' dates of birth. Therefore, you can use the function (strftime('%Y',BIRTHDATE) as int)+1 in the SQL statement to calculate employee ages.

Report Style

Design the report style, as shown in the following figure.

Expansion Direction

1. Select cell A3, choose Cell Attribute > Expansion > Expansion Direction, and set Expansion Direction to Vertical, as shown in the following figure.

2. Select cell B2, choose Cell Attribute > Expansion > Expansion Direction, and set Expansion Direction to Horizontal, as shown in the following figure.

Data Setting

Select B3, set Data Setting to Summary and select Count from the drop-down list under Cell Element > Basic, and set Expansion Direction to No Expansion under Cell Attribute > Expansion, as shown in the following figure.

Custom Grouping

When you preview the template in the designer, all ages are displayed. You can also display the ages in groups.

1. Select cell B2 that will be expanded as the age data column, choose Cell Element > Basic, set Data Setting to Group, and select Advanced, as shown in the following figure.

2. Click Custom to bring up the Custom Group dialog box, and select By Condition. Divide the age values into four groups.

Group 1: Name the group < 30 Years Old with the common condition (Column Name:Age) Less than '30', as shown in the following figure.

Group 2: Name the group 30 - 40 Years Old with the common condition (Column Name:Age) Greater than/Equal to '30' and (Column Name:Age) Less than '40', as shown in the following figure.

Group 3: Name the group 40 - 50 Years Old with the common condition (Column Name:Age) Greater than/Equal to '40' and (Column Name:Age) Less than '50', as shown in the following figure.

Group 4: Name the group ≥ 50 Years Old with the common condition (Column Name:Age) Greater than/Equal to '50', as shown in the following figure.

Mandatory Grouping

The < 30 Years Old group does not appear during the preview because no record in the database meets the condition of being less than 30 years old, as shown in the following figure.

If you want to display all defined groups regardless of matching data, you can select Mandatory Group on the Custom Group page, as shown in the following figure.

Effect Display

PC

Save the template and click Pagination Preview. The effect is the same as that shown in section "Expected Effect."

Mobile Terminal

The preview effects on the DataAnalyst app and the HTML5 terminal are the same, as shown in the following figure.

For details, you can download the templatesGroup by Range.cpt.