Overview
Application Scenario
You can sort data in datasets through SQL statements during database queries. Although this sort method boosts the best performance, all calculations must be done in the database, which can result in complex SQL statements and troublesome maintenance. Additionally, some calculations cannot be completed through SQL statements. Therefore, you are advised to use SQL statements to perform simple and single sorts (ascending/descending).
This document introduces sort types (that can be set when you use SQL statements) when you define a dataset.
ORDER BY Keyword
The keywords of ORDER BY are used to sort the result set by one or more columns. The sort is performed in ascending order (namely the ASC keyword) by default. If you need to sort a result set in descending order, you can use the DESC keyword.
For example, the SQL statements for sorts in ascending and descending orders are as follows:
Ascending order: SELECT * FROM Sales_Volume ORDER BY Sales_Volume
Descending order: SELECT * FROM Sales_Volume ORDER BY Sales_Volume DESC
Example
Ascending Order
Create a general report, create a database query, and enter the SQL statement SELECT * FROM Sales_Volume ORDER BY Sales_Volume.
Click the Preview icon. You can view that data is sorted in ascending order based on the Sales_Volume column, as shown in the following figure.
Descending Order
Create a general report, create a database query, and enter the SQL statement SELECT * FROM Sales_Volume ORDER BY Sales_Volume DESC.
Click the Preview icon. You can view that data is sorted in descending order based on the Sales_Volume column, as shown in the following figure.
Multi-Column Sort
Create a general report, create a database query, and enter the SQL statement SELECT * FROM Sales_Volume ORDER BY Region, Sales_Volume.
Click the Preview icon. You can view that data is first sorted in ascending order by the Region column and then by the Sales_Volume column in ascending order based on the corresponding region, as shown in the following figure.
Dynamic Sort
Define a dataset parameter and add a dynamic parameter in the title to sort data by clicking the title.
Data Preparation
Create a general report, create a database query named ds1, and enter the SQL query statement SELECT * FROM Sales_Volume WHERE Region='North China' ORDER BY Sales_Volume ${a}.
The defined dataset parameter a is used to represent the ASC/DESC order, as shown in the following figure.
Note:
Report Design
Drag data from the dataset into the table, set Data Setting of cell A2 to List, and set the report style, as shown in the following figure.
Dynamic Parameter Adding
Select cell E1 where the column header Sales Volume is located, add a dynamic parameter (the parameter a set in the SQL statement) in Hyperlink, and enter the formula if(a="desc","asc","desc") in Value.
Note:
Effect Display
On PC
Save the template and click Pagination Preview. The preview effect is shown in the following figure.
On Mobile Terminals
The template can be previewed on both the DataAnalyst and HTML5 apps. The effect is shown in the following figure.
Template Download
You can download the example template Dynamic Sort by Clicking the Column Header.cpt.
Notes
Invalid Sort Based on the First Letter of Chinese Pinyin in MySQL
If the MySQL database cannot sort data by the first letter of Chinese Pinyin, you need to check whether the UTF-8 encoding is used in the table field. If so, you need to use the CONVERT () function in MySQL to convert the UTF-8 encoding into the GBK encoding and then sort data. Additionally, the GBK character set must have been installed in MySQL, otherwise an error will occur.
The SQL statement is as follows:
Ascending order: SELECT * FROM Sales_Volume ORDER BY CONVERT (Product USING gbk)
Descending order: SELECT * FROM Sales_Volume ORDER BY CONVERT (Product USING gbk) DESC
