View Report: Tutorial_1-Building-a-Simple-Listing-Report
Download Report: Tutorial 1: Create Simple Listing Report (172 downloads)
This task is about How to Sort Data in BIRT. Reports are not much use if people can’t find the information they need.
In the previous Task, we learned how to Lay Out the Report for our Customers Data Set in a Table. But, as is, the Report Table needs a lot of improvement. For starters, the Report lacks any sort order.
The data rows in the report display in the same order as the query results in the “Customers” Data Set.
The sort order in the Report copies the sort order of the Data Set. We can use SQL to control the sort order in the Data Set Query but we are going to be able to control the sort much better after we extract the data from the database.
And sorting the data after we run the query is what this lesson is about.
Let’s go back to the Layout Editor.
We are going to use the Property Editor to sort the Table. By default, the Property Editor should be directly below the Layout Editor. If it isn’t, we can get it onto the Eclipse IDE easily. Click on “Window”. Then click “Show View”, and select “Property Editor”. We discussed this in the previous task, but it’s worth repeating here because there’s a 99% chance that you are going to accidently close an important window at some point in the future.
If you have the Property Editor displayed, select the Table in the Layout Editor. To select the Table, you need to hover your mouse over the lower left-hand corner of the Table and click the Table tab when it appears.
When you successfully select the Table, the Property Editor tab will read “Property Editor – Table”. Below this tab, there are 7 more tabs.
There are 4 drop-down menus in the “New Sort Key” window.
In “Key”, select CUSTOMERNAME from the drop-down menu. Let’s leave all the defaults in place for the other parameters and hit OK.
But there are a few exceptions…
The sort appears to be CASE-SENSITIVE. Customer Names that are all uppercase letters are moved to the top of the list (like ANG Resellers and AV Stores). Customer Names that are all lowercase letters are moved to the bottom of the list (such as giftsbymail).
You may be asking, “Why is our sort order CASE-SENSITVE?!”. The short answer is that the sort was done using a Sort Strength called ASCII. ASCII is a system of sorting that assigns rank numbers to letters of the alphabet. Uppercase letters have a better rank that lowercase letters. That’s why the Customer Names with all uppercase letters show up at the top of the list and the one with all lowercase letters show up at the bottom of the list.
We want a sort for our report that is CASE-INSENSITIVE.
Let’s get out of Preview and Go back to the Layout Editor.
On the Sort page, select the Sort Key and then click the “Edit” button.
Let’s go to the Key parameter of our Sort Key and type: .toUpperCase()
When you are done, it should read: row[“CUSTOMERNAME”].toUpperCase()
Double check that you have the function typed properly, and then hit OK.
We can see that all the Customer Names are now in proper ALPHABETICAL order.
The 3 stores that were out of place are now sorted correctly.
If your Preview shows the data rows in a sort that is case-insensitive, you’ve done it right. If you have an error, go back to the Sort Key and check your spelling.
The lesson in the Field Guide to BIRT concludes here.
Though the lesson is over, as a bonus, I’d like to discuss a couple points on Sorting beyond the scope of the original lesson in the Field Guide to BIRT.
You may be wondering why BIRT even bothers to include ASCII sorting at all given the fact that you have to manipulate the data to get it to sort right.
ASCII is short for American Standard Code for Information Interchange
ASCII is a method of SORTING that was developed for mainframe computers back in the 1960’s. And this method was built from an even older system needed for WWII-era teletype machines.
This old style code was used as a foundation to modern coding and was STILL the most common character encoding on the World Wide Web until 2007.
And that’s why ASCII is found not just in BIRT, but in just about every software out there.
But BIRT gives us other options besides using ASCII in the Sort Key.
In the Sort Key, go to the Strength drop-down menu. ASCII is the default.
But there are 5 other choices:
If you test any one of these options on the Sort Order of the report, you will find that they all result in a proper alphabetical sort.
After “Primary” sort strength, the purpose of these other sort levels gets really precise and narrow. These other levels start to consider differences in letters with accents, letters with punctuation, and other non-English markings on letters. These options start to have less and less relevance to English language users of BIRT. I’ve listed these other options here with a brief description as a reference so that, you can pause and review them if you like.