Sort in dataset

1. Application scenarios

The sorting in the dataset refers to sorting directly when querying the database through SQL. This method has the best performance, but all calculations must be done in the database, which will cause SQL to be very complex and difficult to maintain. In addition, some calculations cannot be done with SQL. Therefore, it is recommended to only use SQL to implement some simple, single Ascending or Descending.

This document will introduce the sorting types that can be set using SQL statements when defining a dataset.

2. ORDER BY keyword

The ORDER BY keyword is used to sort the result set by one column or multiple columns. The default sorting is in Ascending, that is, the ASC keyword; if you need to sort in Descending, you can use the DESC keyword.

E.g:

Ascending order: SELECT * FROM sales order by sales

Descending order: SELECT * FROM sales order by sales desc

1. Ascending order

Create a new general report, create a new DB Query, enter the SQL statement as: SELECT * FROM Sales_Volume order by Sales_Volume

Click Preview, you can see that the data is sorted in ascending order by the Sales_Volume column, as shown in the figure below:

2. Descending order

Create a new general report, create a new DB Query, enter the SQL statement as: SELECT * FROM Sales_Volume order by Sales_Volume DESC

Click Preview, you can see that the data is sorted in descending order by the Sales_Volume column, as shown in the figure below:

3. Multi-column sorting

Create a new general report, create a new DB Query, enter the SQL statement as: SELECT * FROM Sales_Volume order by Product, Sales_Volume

Click Preview, you can see that the data is sorted in ascending order by the Product column, and the Sales_Volume column is then sorted by the corresponding Product in ascending order, as shown in the figure below:

4. Dynamic sorting

Define a dataset parameter, and realize dynamic sorting by clicking on the title by adding Dynamic Parameters to the title.

1) Data preparation

Create a new general report, create a new database query ds1, the SQL query statement is:

SELECT * FROM sales_Volume where region = 'North China' order by Sales_volume ${a}

A dataset parameter 'a' is defined to indicate the sorting asc or desc. As shown below:

Note: If you need to set dynamic parameters for both fields, the SQL statement can be modified to: SELECT * FROM sales_voleme where region = 'North China' order by sales_volume ${a}, salesperson ${a}

2) Design report

Drag the data in the dataset into the table, set the A2 cell data to List, the report style is as shown in the figure below:

3) Add dynamic parameters

Select the E1 cell where the title of Sales_Volume is located, and add a Hyperlink>Dynamic Parameters, the parameter is the parameter a set in SQL, and the parameter value input formula: if(a="desc","asc","desc ").

Note: a defaults to asc.

4) Effect preview

• PC terminal

    Save the report, click Pagination Preview, the effect is shown in the figure below: 

• Mobile terminal

    Both App and HTML5 are supported, and the effect is shown in the figure below:

The completed template can be found in:

%FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\ReportApplication\FeaturesApplication\Sort in dataset.cpt

Click to download the template:  Sort in dataset.cpt

1. MySQL Chinese sort is invalid

If the MySQL database cannot be sorted by the first letter of Chinese pinyin, you need to check whether the table field uses UTF-8 encoding. If you use UTF-8 encoding, you need to use MySQL's convert method to convert to GBK encoding for sorting. And the premise is that MySQL has installed the GBK character set, otherwise an error will be reported.

The SQL statement is as follows:

Ascending order: Select * from [table] order by convert([column_name] using gbk)  asc  

Descending order: Select * from [table] order by convert([column_name] using gbk)  desc