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.
Many reports in ACCOUNTS, particularly anything like an Income Statement or Balance Sheet, would not work properly with Sort orders other than the ones already defined for them in the program. For those reports, the Sort button is disabled.
As an example of sorting, suppose you want to list the chart of accounts backwards. (That doesn't make a lot of sense, but it's a simple example.) Start by creating the Reports ⇒ Listing ⇒ Chart of Accounts report.
Now click the Sort button. (Do this as you are reading this page, so it will be easier to understand it!)
You will now see the following window, called Specify Sort Columns:
You will also get a message box, warning you that the report has group breaks, and that changing the sort order could cause problems. Many reports in ACCOUNTS will be like this! What can happen with inappropriate changes to the sort order in such reports is that groups get repeated, because not all lines of the report that should be in the same group are sorted together.
Instructions are written at the top of the window, as you can see in the image above. The box at the left labelled "Available Columns" contains the names of all of the fields (also called columns) 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. (If you aren't sure what a column name means, you can always try sorting on it and see what happens!)
The column accounts_order that this report (and many others) are sorted on is the official order of accounts as shown in the Chart of Accounts window. That order is explained in in the section of that window's Help topic titled Validation when you Close this Window.
As mentioned, the box at the right, labelled "Columns to Sort on, in Order", shows the current sort order for the report, and is where you put column names to specify that order.
Although most reports are sorted, the current sort order may in rare cases 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 such cases, it should be pretty easy to see what the current sort order is, by looking at the report before you click Sort.
You will see a checkbox to the right of where the name appears, labelled "Ascending?", which is checked. Since we've said you want to see the accounts in reverse order, that would be a descending sort (from the highest down to the lowest value of accounts_order), so click that checkbox to uncheck it. Now click on the OK button and the report will then be sorted in the reverse order that you wanted.
In the same example described above, you might instead want to sort on the Account Number column, and the obvious guess is a column called "number". (That may or may not give a different order than the accounts_order.) Click the Sort button on the report again to get back to that window. You can then get "number" to the box on the right either by double-clicking on it, or clicking on it to select it and then clicking the Add button. That will put it below any existing columns there, in this case the "accounts_order" column. To move it up to the top, so it is the highest priority for sorting, click the Move Up button. Now click on the OK button and it will sort your report in the order you wanted.
The Move Up and Move Down buttons will move a selected item in the "Columns to Sort on" box up or down to specify the sorting priority.
When multiple columns are in the "Columns to sort on" box, as in the example above, 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, and similarly for further columns. For instance, suppose you have sorted on "number" and "accounts_order" as in this example. The report will mostly be sorted on the number, but if there were two accounts with the same number (which is actually impossible!), then those two will be sorted by the accounts_order.
You can remove a column from the sort by moving it out of the "Columns to Sort On" box, by either double-clicking it in that box, or clicking on it to select it then clicking Remove. (The columns in the "Available Columns" box never change - they are always all displayed.)
Please note that if you remove all of the sort columns, the report won't necessarily go back to its original order. In fact, if you do that then click OK, the report will not change at all. To get back to that original order, Close the report and run it again from the Reports menu. 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.
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
You can actually edit columns that you are sorting on, to change them into complex expressions that do something tricky. Here is an example.
Suppose you are running Reports ⇒ Details ⇒ One Account Details, with the checkbox for "Include the splits / counter accounts in the report" unchecked. (If it was checked, sorting changes might mess up the report.)
If you want to sort a report like that on cheque numbers (held in the reference number field, usually called "Number" or "Chq / Ref #" on program windows), there are some issues. Usually not all transactions have cheque numbers, and not all entries into the Number field have to actually be numbers. So if you just sort on that field, all transactions without such a number will appear first in the report, followed by all of them with such a number, in dictionary order by that number. (Dictionary order means that "10" will sort before "2"!) That's probably not what you want - you would prefer all transactions with a number first, in regular numerical order.
To work around that, first, in the Sort window, get the relevant column, "refno", over to the right, above the top column name. (In some other reports, that same column might be instead named "transaction_refno".) Then, click on it if necessary to select it, then click the Edit button. After you answer a question to confirm you want to do that editing, the program will bring up a Specify Sort Expression window such as the following.
You will see "refno" (without the quotes) in the Sort Expression box at the bottom. Change that to:
IF (isNumber(refno), Number(refno), 999999999)
More instructions for using this window, including the meanings of the Functions in the top-right box, are in the Help on the Advanced Filter window, but you can always just type what you want.
Once you have made your change, you can click Verify if you want, to check whether it is a valid expression. (Unfortunately, not all expressions that the program will allow are actually sensible sort orders!)
Then click OK to go back to the main sort window, where you will see your expression displayed. (It also verifies when you click OK, and doesn't let you exit this editing window if it isn't valid.) Click OK again to apply the new sort. That forces every transaction with no reference number (or one that isn't a number) to be treated as if they had the number 999999999 and thus come out at the bottom of the report. Those columns with no reference number will be sorted on any further columns in the sort order, and every transaction with a number will come out at the top in order. (The part "Number(refno)" converts the field, which can contain non-digits, to a number, which is thus sorted numerically instead of in dictionary order.)
One function used in the example above but not described in the Help on the Advanced Filter window, because it is very unlikely to be needed there, is "IF". An expression such as:
IF (condition, value1, value2)
requires that the condition part be something that is true or false. If it is true, the result of the entire expression is value1. If it is false, the result of the entire expression is value2. Each of condition, value1 and value2 can themselves be expressions - the condition pretty much has to be.
Obviously, this type of complex sorting requirement will be very rare, and most reports are already sorted into very appropriate orders.
Memorizing Sorted Reports
Particularly if you will want 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.
This topic was last edited on Mar 23, 2023