Filtering Reports

<< <%SKIN-STRTRANS-SYNTOC%> >>

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, if you would prefer to see this feature demonstrated live, rather than reading about it.

 

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 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, like this:

 

FilterWindow

 

Sorry, we didn't design this window, it comes with PowerBuilder, the program we 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 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 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 of the Filter window, but that is getting really technical!

 

If you want to make comparisons to fields containing text rather than numbers, enclose your text in quotation marks. For a simple comparison to fields containing text, use "=", as in to following for the report above:

 category = "General"

 

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"

or

Upper(name) = "SMITH"

 

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) = 'SMITH'

 

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 this same report, to select all donations made on December 31, 2016, you would use the following filter:

date_received = 2016-12-31

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

date_received between 2016-12-01 and 2016-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 quoted 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 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 do have a Cheque # / Paid By, use "not" to reverse this condition:

 Not IsNull ( cheque_no )

 

If you use "and" and "or" to create more complex expressions, we 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, the following would work to select everyone with a name starting with "A" and donation date received December 31, 2016:

 (name < "B") and (date_received = 2016-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')

or

 membernumber in (15, 23, 99)

The first example selects all rows for donations whose category is either "General" or "Library", and the second example selects all with a Member/Envelope 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 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. The exception is if you memorize a report with filters, and then re-use it - see the details on that below.

 

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 Reports ⇒ One Date Donation ⇒ 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. (Fortunately, you can do that via the selection criteria when you run that report, so a filter isn't needed!)

 

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_6, membernumber, email and total_amount (total amount donated this year, or in the selected range of dates). None of those fields are displayed on the label! 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. Those additional fields that you can filter on will be displayed in the Columns box listing the fields, at the bottom right of the filter window.

 

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

 

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 filter results may be unreliable, though it actually does work correctly on that report. 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 donor fields: full_name, reversed_name, donornum (an internal numbering that you cannot see), the two donor category fields, and the email address. 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 cannot filter out the donations within each donor. Most reports do work well with filters, however.

 

We have very occasionally 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.

 

Memorizing Filtered Reports

 

Particularly if you will be wanting to re-run the same report in the future with the same or a similar filter, you may want to memorize it for re-use with the Memorize button. That will also memorize any Sort you have added, in addition to the Filter. 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.