ADVANCED SORTING

In this example, a report was sorting alphabetically by SCHOOL_CLUB. There are two problems however. First, totals without a school club show up as the last row. This needs to be changed so totals without a club are the first line in the report. Second, school clubs in the U.S. need to be displayed before international clubs. The following steps will show how to fix these problems using Advanced Sorting.

1. We're starting with a report that currently sorts on SCHOOL_CLUB in ascending order.

 

 

2. When you want to apply any type of advanced sorting, you must first create an expression to evaluate the data. In the following example, we'll create an expression in a data item that will evaluate whether there is a null school club. Start by opening the query associated with the list. Then drag a data item to the right pane.

 

 

3. Type the expression to evaluate for null school clubs. Validate your code then click OK.

 

 

4. In the properties pane, rename the data item you just created. In this example, we'll use SortNullSchoolClub.

 

 

5. In your query, drag another data item to the right pane. In this expression, we'll evaluate the length of SCHOOL_ZIP_NATION using the SQL function "char_length". The school zip from international school clubs will be two characters. U.S. school clubs will have a five digit zip code length. Validate your code, then click OK.

 

 

6. In the properties pane, rename the data item you just created. In this example, we'll use SortZipLength.

 

 

7. Now that we have the two expressions set, go to the report layout and select the SCHOOL_CLUB column. While the SCHOOL_CLUB column is selected, click on Advanced Sorting under the sort button of the task bar.

 

 

8. In the Grouping and Sorting dialog box, drag each new data item you just created into the Detail Sort List folder in the right pane. You can only drag one data item at a time. Be sure to drop them directly on the Detail Sort List folder.

 

 

9. Now we have to modify the list of sort items. Since we want to first sort for school clubs that are null, we'll move that item to the top of the list by selecting it, then clicking the green "up arrow".

 

 

10. Once it's moved to the top of the list, we need to change it's sort order to descending. You do this by clicking on the Sort Order button. Notice the gray arrow next to the SortNullSchoolClub data item is now pointind down.

 

 

11. Repeat steps 9 and 10 so that SortZipLength is second on the list. When completed, sorting should look like this. Click OK. Advanced Sorting is complete.