Selection Criteria for Reports

Selection Criteria for Reports 

This section features that are related to the logic for selecting records in the various reports (aside of improving the performance of ‘OR’ logic execution). 

When specifying selection criteria, the challenge is to specify the criteria such that the report will include all and only the records you intended it to include. In the discussion below references are made to two modes of record selections namely, ‘AND’ and ‘OR’.  For simplicity's sake one may think about ‘AND’ logic as restrictive / exclusive (less records may be displayed with each additional ‘AND’ criterion) while the ‘OR’ logic can be thought of as expansive / inclusive (more records may be included with additional criterion – once some other criteria were specified.)

The Selection Criteria area for a report can be found on the button in the lower right-hand corner. 

The AND and INCLUDE functionality is assumed unless specified otherwise in the Selection Criteria screen except when multiple criteria for the same field are present.

Thus, for most queries and reports, the system will initially assume that each of the criteria you list will restrict the set of records selected (‘AND’ logic). 

Example 1 – Single Criterion, Multiple Fields

Most reports typically fall into this category; You select a Date range, a Fund range or an Idcode or County or Code value.  You add more criteria to more finely tune the group of records or make the set of records selected smaller. If all the criteria are for different fields, then you are adding criteria together to select only those records with those values.

A good visual representation of this is the Venn Diagram where each circle represents the set of records matching a selection criterion of a given field with the end record set being the records that are common to (the intersection of) all criteria (this criterion AND that criterion AND the other one AND the fourth one… etc.)

In this example looking for all those records with a particular Date range, Fund range AND Type – the intersection of these criteria is represented by the following Venn diagram as shown in the black area:

Some questions in this category are:

How many cash gifts did we have this year for endowed funds?

Did we have any stock gifts from pass through funds last December?

Example 2 – Multiple Criteria, Multiple Fields:

Frequently the record set of interest includes several criteria for the same field. The smaller circles (a and b) below could represent two different funds or two different donor classes or gift types or batches. It could also represent two different ranges of values.

Some of the questions for this scenario would be:

How many gifts from fund class 00 or fund class 30 did we get this year?

How many donor advised and designated funds gave grants this year?

What were the grants made last year for the Program Areas of Arts or Education

Which gifts did we get with the purpose code of ADDA through ADDF or the purpose code of Match

For this purpose, selecting a Date AND (a OR b) is the appropriate method.

When entering criteria if you add a field selection twice you must now move to the Selection Criteria screen to review the results of your additions.  The system will make some assumptions and in many cases you can leave everything unchanged.

Suppose the desired report is a list of all of the gifts in the 00 and 30 fund classes for the year.

Given that a record can only have one class, the restriction boils down to “all of the gifts this year that were given to the 00 OR the 30 fund class funds”.

Frequently one thinks this is an ‘AND’ but (in technical terms) it is really an ‘OR’.

Entering a current year date selection and two fund classes will yield the following display but missing the right two columns:

When you click on the Selection Criteria button you will see:

Fortunately, the system understood that a record can’t have both a 00 AND a 30 fund class value so it automatically put an OR between the two fund classes.

The Values in the lower section of the screen – the Query section - will populate in the order they appear on the front window. It is strongly advised to add parentheses around your ‘OR’ criteria for clarity.

The system cannot determine it is asked for all gifts for (this year and fund class 00) as well as all the class 30 gifts for all time or all gifts in (class 00 or class 30) for this year. The intention might actually be to see all the class 30 gifts for all years, but just those in this year for class 00.

To ensure the correct record set is provided (all the current year gifts for those two classes) add parentheses around the fund class ‘OR’s:

To add the parentheses, click the (*) button and then place the left and then the right parenthesis in front of and after the appropriate elements – in this case the fund classes.  The system will assume that multiple criteria of the same type are ‘OR’ed together so usually only the parentheses will be needed.

But if changes are needed for changing an ‘AND’ to an ‘OR’, then double-click the ‘AND’ object and it will toggle between ‘AND’, ‘OR’ and ‘NOT’.

When ready, click OK to close the Selection Criteria window to redisplay the rest of the tabs in order to finish setting up the report.

There is Help text available by clicking Help in the lower left hand corner. Right clicking, double clicking and dragging and dropping all result in the various objects ( ‘AND’, ‘OR,NOT ) moving or changing. Please refer to the on screen help or this chart for assistance.

The following actions are available on the Selection Criteria screen:

Clicking the Help button in the lower left will display the FIMS Help form by default since there is Help available. Clicking on the User Notes radio button will open the User Notes form where one can enter in additional more personalized tips and tricks.

Use the Save button to Save notes for future reference.

A Spell Check button is available next to the Save button at the bottom.

Additional Examples of the Query Screen:

To exclude a value:

 All Gifts from 2013 with a Gift Type of Cash through SecP except for InK gifts

The only object added was the NOT placed after the AND.  To move a NOT into position, drag the NOT from the line of objects above the lower screen and drop it at the end of the phrase (After the InK in this scenario). After dropping it at the end, select it and move it into its proper location after the AND.

For a Journal history report you might have added this criteria before to look for information from two groups of funds, a specific date range, and two set of GL account ranges :

Without adding anything extra you get this set of criteria:

The AND and OR decisions made are correct, but what you get is probably not what you want. What you get did not restrict the journals to the current year and deliver both sets of GL accounts.  What you need to do is add parenthesis to group the funds and group the account criteria.  The system will color code the parenthesized sections so it is easy to determine if you did it correctly.

Fund Listing for Active Funds only:

In datagrids ( which also have this feature), you can exclude blank dates to get to retired funds now: