As the previous section shows, sorting data displays report data in a more meaningful order. Without sorting, a report can be much less usable, as the first report in Figure 12‑1 shows.
You can sort data in ascending or descending order, and you can sort by as many fields as you like. For example, you can sort a list of customers by credit rank, then by customer name.
Figure 12‑3 shows the first six data rows in three lists. The first list is unsorted, the second sorts data by credit rank, and the third sorts data first by credit rank, then by customer name.
Figure 12‑3 Three examples of sorting data in a listing report
A field that you use to sort data is called a sort key. For example, the third list in Figure 12‑3 has two sort keys—the credit field and the customer name field.
Ways to sort data
Sort data in one of two ways:
Specify sorting in the data set query so that the database processes the data before sending the results to BIRT. Databases are efficient at sorting data, especially if they have indexes to optimize sorts. For that reason, use the query to sort data whenever possible.
Sort data in BIRT. Use this method if the data source, such as a text file, does not support sorting.
How to sort data through the query
1 In Data Explorer, create a new data set, or edit an existing one.
2 In the query text area, write an ORDER BY clause in the SELECT statement. For example, the following statement returns customer information and sorts rows by credit rank, then by customer name:
SELECT Customers.customerName,
Customers.phone,
Customers.creditRank
FROM Customers
ORDER BY Customers.creditRank, Customers.customerName
3 Choose Preview Results to verify the data that the query returns. The rows should be sorted by the fields in the ORDER BY clause.
4 Choose OK.
How to sort data in BIRT
The instructions in this section assume that you already inserted data in a report.
1 In the layout editor, select the table element or list element that contains the data that you want to sort. Property Editor displays the properties for the table or list.
Figure 12‑4 shows an example of a selected table and the table’s general properties.
Figure 12‑4 Properties for a selected table
2 In Property Editor, choose the Sorting tab.
3 In the Sorting page, shown in Figure 12‑5, choose Add to specify the criteria on which to sort the rows.
Figure 12‑5 Sorting page of Property Editor
4 In New Sort Key, specify the following information:
1 In Key, complete one of the following steps:
To specify a field to sort by, select a field from the drop-down list.
To specify an expression by which to sort the data, choose the expression builder button, then provide an expression.
2 In Direction, specify the sort order by selecting Ascending or Descending.
3 In Locale, select a language. The language determines the sorting, or collation, attributes that conform to local conventions. Select Auto to sort data according to the locale set on the user’s machine.
4 In Strength, select a collation strength, also known as a collation level. The collation strength determines whether accent marks, case, or punctuation are considered when sorting strings.
Figure 12‑6 shows an example of a sort definition.
Figure 12‑6 Sort expression in New Sort Key
Choose OK. The Sorting page displays the sort key that you defined for the selected table or list.
5 Preview the report. The data in the details section appears in a different order.
Setting the sort strength
BIRT supports locale-sensitive sorting and provides six strengths, or levels, of string comparisons, described in Table 12‑1. The strength specifies what type of difference needs to exist between two letters or strings for them to be considered different.
Table 12‑1 Sort strengths
Strength
Description
ASCII
Collates by the ASCII values of the letters. Differences in case and accents are significant. For example:
A < B < a < à
Primary
Different base letters are considered a primary difference. Use Primary to ignore case and accents. For example:
a = A = à < b < c
Secondary
Different accented forms of the same base letter are considered a secondary difference. Use Secondary to ignore case. For example:
ab = Ab < àb < ac
Tertiary
Case differences and different accented forms of the same base letter are considered tertiary differences. For example:
ao < Ao < aò
Quaternary
When punctuation is ignored at the previous strengths, use Quaternary to differentiate strings with and without punctuation. For example:
ab < a-b < aB
Identical
When all other strengths are equal, the Identical strength is a tie-breaker. The Unicode code point values of the NFD form of each string are compared. Use this strength sparingly as it decreases performance and is rarely needed.
The effect of applying the different strengths depends on the locale. Each language has its own rules for determining the proper collation order. For example, in some languages, such as Danish, certain accented letters are considered to be separate base characters. In most languages, however, an accented letter has a secondary difference from the unaccented version of that letter. The following example shows the difference in sorting when the collation strength is secondary, but the locales are different:
Danish German
a A
A a
â â
Ä B
B Ä
Sorting string data ignoring capitalization
By default, BIRT sorts string data according to ASCII values, so uppercase letters precede lowercase letters. For example, “Z” appears before “a”. The following list of values is an example of an ASCII sort:
ANG Resellers
AV Stores, Co.
Alpha Cognac
Anna’s Decorations, Ltd
abc Shops
Typically, users prefer to view a list of names in simple alphabetical order, without regard to capitalization. To display string values in case-insensitive alphabetical order, set the collation strength to Primary or Secondary.
Alternatively, use JavaScript’s toUpperCase( ) or toLowerCase( ) function to convert the values to all uppercase or all lowercase before sorting. The following expression is an example of a sort key expression that you specify:
row["CUSTOMERNAME"].toUpperCase()
Using this expression, the previous list of values appears alphabetically as:
Specify sorting in the data set query so that the database processes the data before sending the results to BIRT. Databases are efficient at sorting data, especially if they have indexes to optimize sorts. For that reason, use the query to sort data whenever possible.
