You will also see a Filter button on the reports window. This is a somewhat technical option, which allows you to restrict the data viewed. For instance, suppose you want to see only donations over $1,000. You might start by creating the Reports à Donation à All Donations à Sort by Name report. Then click the Filter button. (Do this as you are reading this page or it will be hard to understand it!)
There is a training video on Filtering Reports on the Demos and Samples web page, if you would prefer to see this feature demonstrated live, rather than reading about it.
You will now see a rather technical looking window called Specify Filter, like this:
Sorry, I didn't design this window, it comes with PowerBuilder, the program I used to create DONATION. 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 figure out which is which. (You can always try some trial and error.)
In this case, you want to filter on the Amount column, and the obvious guess is the column which is called "amount". 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 "amount", and type in "> 1000", so that what you see is:
amount > 1000
(Note: do not format numbers with commas and dollar signs when filtering.) 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 where the amount is more than $1,000.
Other comparison operators you can enter (like ">" for greater than, 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, but that is getting really technical!
If you want to make comparisons to fields containing text rather than numbers, enclose your text in single or double quotation marks. For instance, on this same report to see only people with last 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'
To make comparisons to fields containing dates, you don't use quotes, and you use the date format YYYY-MM-DD. For instance, on this same report, to select all donations made on December 31, 2009, you would use the following filter:
date_received = 2009-12-31
For a range of dates, for instance to select all donations made between December 1, 2009 and December 31, 2009, you would use the following filter:
date_received between 2009-12-01 and 2009-12-31
N.B. "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. For instance, to select only donations where the Cheque # / Paid By field was not filled in, use the filter:
IsNull ( cheque_no )
The spaces before and after the parentheses don't matter - they can be there, or not. To select donations that have a Cheque # / Paid By, use "not" to reverse this condition:
Not IsNull ( cheque_no )
If you use "and" and "or", I have found that in many cases you have to put the conditions that they are joining in parentheses, or the filter will be found to be invalid. For instance:
(name < "B") and (date_received = 2009-12-31)
If you want to filter on the Member/Envelope number in a report, say to select only those with numbers between 50 and 200, you just have to be aware that the values of that field are numbers, not strings, so they should not be quoted. You would use the following filter:
membernumber between 100 and 200
The Member/Envelope number field is almost always called membernumber or member_number in the list of fields, if it is present in a report.
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:
category in ('General', 'Library')
which selects all rows for donations whose category is either "General" or "Library". 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 addresses in Texas, and figured that you would find them if "TX" appeared in either addr2 or addr3 in a report, such as Reports à Donor à Mailing Labels. A Filter expression to do that would be:
(addr2 like '%TX%') or (addr3 like '%TX%')
This is an example of a complex filter that won't work without the parentheses. Basically what the "%" means is "anything", so "addr2 like '%TX%'" means that the Address Line 2 contains TX 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.
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 Reports à Donation à One Date Details for a given date, you can't use the Filter feature to view the donation details for a different date. To do that, you would have to re-run the report. Similarly, if you do Reports à Donor à Donation Info à Sort by Name, you can't use a filter to restrict the report to only include donations to a certain donation category, because the donation category isn't one of the fields in the report.
Some reports contain fields that can be filtered or sorted on, but aren't displayed in the report at all. For instance, in Reports à Donor à Mailing Labels, when you click Filter you will see that you can filter on category1 and category2 (the two Donor Category fields), other_info_1 through other_info_3, and email. This lets you do things like print mailing labels for only people with a certain Donor Category 1 value, only people that don't have an email address, etc.
When you change the filter, part of the header of most reports will change to reflect the new filter you have specified. The only exception to this is if you also change the sort order (see the following section), in which case the sort order will show in the header instead.
Filters do not work correctly on some reports. In particular, if a report has multiple sections, like Reports à Donation à Details, One Page per Donor, your results will be unreliable. That report has two sections per page, the first showing the detailed donations for each donor, the second showing the category totals of donations for each donor. If you use Filter, the only field names that show up are full_name and donornum. What this means is that the most basic database selection controlling this report is selecting only the donors, not the donation details, and using Filter will probably lead to unexpected results. Most reports do work, however.
I 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 me and describe exactly which report you were running, and what you were entering in as the filter.
Another way to modify reports, for changes that cannot be done with Filter, is Modifying Reports Using SQL.