Filtering Reports


Navigation:  USING REPORTS >

   Filtering Reports

There is a Filter button on the reports viewing window. This is a somewhat technical option, which allows you to restrict the data viewed.


There is a training video on Filtering Reports on the Demos and Samples web page for the Software4Nonprofits DONATION web site, if you would prefer to see this feature demonstrated live, rather than reading about it. (The feature works the same way in both programs, the difference being the names of the fields in the different reports that can be filtered on.)


What a filter does generally is restrict the rows displayed in a report, by specifying a condition, which the rows have to satisfy to still be displayed.


As an example of filtering, suppose you want to see transaction details, but only the parts that affect a certain account. You might start by creating the Reports ⇒ Accountant ⇒ Transaction Details report. Then click the Filter button. (Do this as you are reading this page or it will be hard to understand it!)


You will now see a rather technical looking window called Specify Filter, like this:




Sorry, we didn't design this window, it comes with PowerBuilder, the program we used to create ACCOUNTS. The names of the fields that you can filter on are in the box labelled Columns in the bottom-right corner. 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 in almost all reports. (You can always try some trial and error.)


In this case, you want to filter on the account name column, and the obvious guess is the column which is called "account_name". Click on that and it will go up into the box at the top for the filter. Click into that box at the top after "account_name", and add in "= 'Chequing Account'" after that, so that what you see is:

 account_name = 'Chequing Account'

If you like, you can click on the Verify button and it will tell you whether you have entered a valid filter. If that says the filter is OK, click on the OK button and it will apply that filter to your report. In this case, you would then only see the rows of the report that are the splits rows for the Chequing Account.


Other comparison operators you can enter (like "=" for equals, which we used above) are shown in little buttons on the lower left side of the window. The most common ones you would use are "<" for less than, "<=" for less than or equal to, ">" for greater than, ">=" for greater than or equal to, "=" for equal to, and "<>" for not equal to. You can also use "and" and "or" to join multiple filter conditions, and parentheses (round brackets) for grouping. There are also some Functions you can use in a box on the bottom middle of the Filter window, but that is getting really technical!


If you are entering filter conditions involving numbers, like transaction or split amounts, you enter those numbers without quotation marks, and also with no commas or dollar signs.


When you are making comparisons to fields containing text rather than numbers, enclose your text in quotation marks. For instance, on a report listing accounts, to see only accounts with names starting with "A", use the following filter:

name < "B"

Unfortunately, comparisons involving text are case-sensitive, i.e. upper and lower case are treated differently. To work around that, you can use the Upper function, which converts a field to all uppercase, then compare to a quoted uppercase value, as in:

Upper(name) < "B"


When you are using quoted values, you can use either single quotes or double quotes, as long as you are consistent. For instance, the previous filter example would have worked exactly the same if it was:

Upper(name) < 'B'

One time when you have to specifically choose between single and double quotes is if the value being quoted contains one or the other already, such as an apostrophe (which is the same as a single quote). So,

 name = 'Bob's Garage'

would not work, because the apostrophe closes the opening single quote! Instead, use:

 name = "Bob's Garage"


To make comparisons to fields containing dates, you don't use quotes, and you must use the date format YYYY-MM-DD. For instance, on a report with a transaction_date column, to select all transactions made on December 31, 2012, you would use the following filter:

transaction_date = 2012-12-31

For a range of dates, for instance to select all transactions made between December 1, 2012 and December 31, 2012, you would use the following filter:

transaction_date between 2012-12-01 and 2012-12-31

Note: "between" is just a short form for a combination of a ">=" condition with a "<=" condition. You can also use "between" with numbers like amounts, and with text values.


To check whether a field is empty, you use the function IsNull. ("Null" is a techie word for empty or missing.) For instance, to select only accounts where the Description field was not filled in, use the filter:


The spaces before and after the parentheses don't matter - they can be there, or not. To select accounts that do have a Description, use "not" to reverse this condition:

 Not IsNull(description)


Another helpful function is IsNumber, which tells whether its contents is a number. So to select only rows of a detail report including the transaction Reference Number field where the value in that field is just a number (likely a cheque number!), use:


As usual, the name of that field may differ between different reports - that is what it is called in Reports ⇒ Details ⇒ Transactions by Date.


If you use "and" and "or", we have found that in many cases you have to put the conditions that they are joining in parentheses, or the filter will either be found to be invalid or will not work as expected. For instance:

 (name < "B") and (transaction_date = 2012-12-31)


To select rows of a report where a certain field is one of a group of values, you could use a complex expression with "or", but it's easier to use the "in" operator, like:

 name in ('General Fund', 'Building Fund')


 number in (3000, 3010, 3020)

The first example selects all rows for accounts whose name is either "General Fund" or "Building Fund", and the second example selects all with an account number that is one of those three specified numbers. You can put any number of comma-separated values within the parentheses.


To search for values anywhere in a text field, you use the Like operator, and percent signs. For instance, suppose you wanted to find accounts that are related to your Building Fund, and figured that you would find them if the word "Building" appeared in the account name. Run Reports ⇒ Listing ⇒ Chart of Accounts. A filter expression to do that (taking into account that it could be upper or lower case) would be:

 Upper(name) like '%BUILDING%'

When you use Upper, be sure to capitalize the value you are comparing it to! Basically what the "%" means is "anything", so "Upper(name) like '%BUILDING%'" means that the name contains "Building" (or any variant capitalization of it) anywhere within it, including right at the start or right at the end.


Please note that each time you create a given report, it will be displayed with no filters. In other words, filters that you previously used on a report are not remembered the next time you run that report, unless you memorize it. Memorizing a report saves both any Filter and any Sort that you have put on it, and allows you to re-run it later.


It is important to understand that filters can only limit the rows of data currently shown on the report to show fewer of the same rows. They cannot cause the data to be re-selected with different criteria. So, for instance, if you do a report for a given range of dates, you can't use the Filter feature to view the data for a different range of dates. To do that, you would have to re-run the report.


Some reports may contain fields that can be filtered or sorted on, but aren't displayed in the report at all.


When you change the filter, part of the header of most reports will change to reflect the new filter you have specified.


Filters may not work correctly (or may not be available) on some reports. In particular, if a report has multiple sections, your results will be unreliable. An example of such a report is Reports ⇒Summary ⇒ Government Form Amounts.


We have infrequently seen cases where using Filter causes the program to crash. If that happens, just open it up again, and try the Filter again. Usually these problems aren't repeatable, so it will likely work. If the problem does repeat, please contact us and describe exactly which report you were running, and what you were entering in as the filter expression.