Sorting Reports


Navigation:  USING REPORTS >

   Sorting Reports

There is a Sort button on the reports viewing window. This option allows you to change the order of the data displayed on the report, if you want to see it in another order. For instance, suppose you want to list donations in descending order by amount. You might start by creating the Reports ⇒ Donation ⇒ All Donations ⇒ Sort by Name report. (There is actually a built in version of this report sorted by Amount, but for the purposes of this explanation we are getting at the same thing in a different way!)


Now click the Sort button. (Please do this as you are reading this page or it will be difficult to understand it!)


You will now see the following window, called Specify Sort Columns:




Instructions are written at the top of the window. Basically, the box at the left labelled "Source Data" contains the names of all of the fields on the report that you can sort on. While the names displayed won't exactly match the column headings on the report, you should be able to easily figure out which is which, for almost all reports. (You can always try some trial and error.)


Although most reports are sorted, the current sort order may or may not already be displayed in the box on the right when you bring up this sort window for the first time for a given report. That's for technical reasons, which are a bit hard to explain. In any case, it should be pretty easy to see what the current sort order is, by looking at the report before you click Sort.


In the example described above, you want to sort on the Amount column, and the obvious guess is a column called "amount". Click on that and drag it over to the box on the right labelled "Columns". You will see a checkbox to the right of where the name appears, labelled Ascending, which is checked. We want to see it in descending order (from the highest down to the lowest Amount), so click that checkbox to uncheck it. Now click on the OK button and it will sort your report in the order you requested.


You can even sort on more than one column by dragging a second column over from the "Source Data" list to the "Columns" list. When you do that, it first sorts by the first column listed, and then it only sorts on the 2nd column listed if the values in the 1st column are the same. For instance, suppose you have sorted on "amount" and "date_received". The report will mostly be sorted on the amount, but if there are two donations for $1,000, then those two will be sorted in order of date.


You can remove a column from the sort by dragging it back from the "Columns" box to the "Source Data" box. Please note, however, that if you remove all of the sort columns, the report won't necessarily go back to its original order. To get back to that order, Close the report and create it again from the Reports menu, or use the Rerun button. Each time you create this report, it will be displayed in its original order. Your sort order from a previous run is not remembered - but see the section below about Memorizing Sorted Reports for how you can do that.


A couple of reports will not work properly with arbitrary Sort orders. For those reports, the Sort button is disabled.


When you change the sort order, part of the header of most reports will change to reflect the new order you have chosen. The only exception to this is if you also change the filter (see Filtering Reports), in which case the revised filter criteria will show in the header instead.


Complex Expressions for Sorting


If you want to sort a report on the Member/Envelope number, you have to get a bit more tricky, because usually not all donors have Member/Envelope numbers. If you just sort on that field, everyone without a Member/Envelope number will appear first in the report, followed by everyone with such a number, in order by that number. That's probably not what you want - you would prefer everyone with a number first.


To work around that, first, in the Sort window, drag the column named "membernumber" over to the right. Then, double-click on it on the right, to bring up the Modify Expression window. You will see "membernumber" (without the quotes) in the big box at the top. Change that to:


 if ( isNull ( membernumber ), 999999999, membernumber )


Then click OK twice to apply the new sort. That forces everyone with no Member/Envelope number to be treated as if they had the number 999999999 and thus come out at the bottom of the report, and everyone with a number to come out at the top in order. If all of your donors have Member/Envelope numbers, or you want those without one to sort to the top, just sort normally on only the membernumber column instead.


Another case of using complex expressions came to us from a user who wanted a report that was sorted by donation categories to show the category "Tithes and Offerings" first, before all other categories. To do that, we had them sort on the category field, then double-click on it to get to the Modify Expression window, and enter the following expression:


 if ( category = "Tithes and Offerings", "", category )


That sorts "Tithes and Offerings" as it if was nothing (and thus sorts it to the top) and sorts everything else normally by its category value.


Problem with Descending Sorts with Large Fonts set on your Computer


If you have large fonts set on your computer, the Ascending column may not be visible in the Sort window, and as a result, it may be impossible for you to change a sort order to be Descending by unchecking the Ascending checkbox. (I'm sorry, this is a bug in the program we use to create DONATION, PowerBuilder, and we can't do anything about it.)


In many cases you may be able to work around this. The trick is that if you double-click on a selected sort Column in the right-hand side of the window, the Modify Expression window comes up (as used in the previous section) that allows you to change it from just a column name to a calculated expression.


Suppose the column you wanted to sort Descending on was "amount" (without the quotes, of course), i.e. you wanted to see the top donors at the top of the report. After double-clicking on the "amount" column name at the right, in the window that comes up, change "amount" to "-amount", without the quotes, then click OK. Sorting Ascending on a negative amount gives the same result as sorting Descending on a positive amount, so you will get the result you want, even though you can't change it from Ascending. This same trick will work with any numeric field.


Memorizing Sorted Reports


Particularly if you will be wanting to re-run the same report in the future with the same sort, you may want to memorize it for re-use with the Memorize button. That will also memorize any Filter you have added. You can then re-run the memorized report with Reports ⇒ Memorized Reports.


Memorizing and re-running memorized reports is not available in the free feature-limited Lite version.