Filtering Reports

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!)

You will now see a rather technical looking window called Specify Filter. (Sorry, I didn't design it, it comes with PowerBuilder, the program I used to create DONATION.) In the bottom-right corner is a box labelled Columns. 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". (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 make comparisons to fields containing dates, you unfortunately have to use a strange date format, namely YYYY-MM-DD. For instance, on this same report, to select all donations made on December 31, 2007, you would use the following filter:

date_received = 2007-12-31

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

date_received between 2007-12-01 and 2007-12-31

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 )

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 = 2007-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

N.B. "between" is just a short form for a combination of a ">=" condition with a "<=" condition.

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. It 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.

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.