Smith College Admission Academics Student Life About Smith news Offices
  Home > Offices > Information Technology Services > TARA > Banner System > Cognos ReportNet Tips and Tricks  

Getting Started
25Live Scheduling
Account Forms
Admin Systems*
Cloud Storage
File Transfer
Google Apps
   Smith Mail
Internet Browsers
   Backup & Restore
   Related Apps
   Other Info
Microsoft Office
Microsoft Windows
Mobile Devices
Smith Network
Smith Portal
Smith ResNet
Standard Software
Terminal Emulators
Viruses & Spyware
Web Development
Wireless Computing

*Smith login required




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.



Back to top >



Copyright © 2008 Smith College Information Technology Services  |  Stoddard Hall  |  Northampton, MA 01063
413.585.3770  |  Questions or comments?  Send us email.  |   Last updated May 5, 2009

DirectoryCalendarCampus MapVirtual TourContact UsSite A-Z