The Filters Screen in the ReportWriter

The Filters screen allows you to restrict the data in three ways:

1. Control what records are extracted from the physical database
2. Limit the staff members who are included to those on a selected staff list.
3. Calculated in some way based on the parameters chosen and combination of Columns in the report.

Each section of the Filters screen is a separate filter or filters.  These three areas combine together to eliminate all records except those you want your report to contain:

SQL Filters

SQL Filters allow you to control and limit the rows of data that are extracted from the physical database.

Columns used as SQL Filters must:

1. Come from one of the joined tables.
2. A "real" field in one of the joined tables - NOT a MagicGetter (see the note onMagicGetters below).
3. Not an Expression.

The SQL filters are applied when data is physically extracted from the physical database:  they are handed off to the database software (Oracle or Postgres);  they cannot come from "computed or added later" data items.

There can be multiple SQL filters and these are combined together with "AND" and "OR" operations to create a single logical expression.

Adding an SQL Filter

Components of an SQL Filter

An SQL filter has 3 parts:

• An existing Column
• The "Operand" (i.e. operator) 
• The value on the right side of the equation.  This can be:
  • A report parameter
  • A built-in macro  (e.g. ${today}).   These are found in the macro browser (more on that below).
  • A literal value  (e.g. 2014-25)

Once those are selected, you can click Add to create your parameter:

Adding parentheses to group your SQL parameters

There are "Parens" drop downs at either end of each SQL parameter.  These are there to help you group your parameters logically so you can ensure that the SQL query is doing the right thing.

It's important to use the parentheses to group your logic.  Remember this:   "Let's eat, Mom" vs "Let's eat Mom".  Punctuation matters!  Use parentheses to group your SQL Filters and have your report do the right thing:

Operators

• =   - "Equals" This is the same as "==" used elsewhere.
• <>  - "Not Equals"   This is the same as "!=" used elsewhere.
• >  - Greater than.  This is a numeric comparison.  It may not work as you expect if your fields are Strings.
• >=   - Greater than or equal to.  This is a numeric comparison.  It may not work as you expect if your fields are Strings.
• <  - Less than.  This is a numeric comparison.  It may not work as you expect if your fields are Strings.
• <=  - Less than or equal to.  This is a numeric comparison.  It may not work as you expect if your fields are Strings.
• Starts With - This is a String comparison:  Match the right side up to the start of the left side.
• Ends With  - This is a String comparison:  Match the right side up to the end of the left side.
• Contains  - This is a String comparison:  Slide the right side along the left side, from left to right, to find a match.
• Not Empty  -True when a field been set.  Does not test actual values - only if the field has been set.  Works for Booleans, Strings, Dates, TimeStamps, ...
• Empty -True when a field has NOT been set.  Does not test actual values - only if the field has not been set.  Works for Booleans, Strings, Dates, TimeStamps, ...
• Is One Of/In
• Is Not One Of/In

Adding Multiple SQL Filters

You can add additional SQL Filters.  There is no built in limit.  Each has to be added with either an AND or an OR operator.

To start, click the "Add Filter" button:

This brings up the usual "Add a FIlter" dialog, which can be filled in with your choices for the Column, the operator and the "Value".  For example, if you want to limit your report to only active staff members, you can add an "employmentStatus = A" filter:

Again, click Add to create the filter:

• The new filter is attached to all previous filters with an "And" operator.  This can be changed to an OR operator if needed.  The connecter must be either And or OR.  Think through the logic carefully!
• The parentheses need to be adjusted to include the new filter:

When you use parentheses you are communicating your intent to the ReportWriter.  When you omit them, you may not get the expected result.

Staff List Filter

Staff List filtering requires that you create a "Staff List" parameter.  If there is no parameter, nothing can be setup for this  filter:

You can create at most 1 Staff List filter for your report.   

To create the Staff List parameter, go to the Parameters screen and and click "Add Parameter":

You will need to use a "Type" of "Staff Lists".  The Code and Prompt are up to you.  For more information on Parameters, refer to the Report Parameters article.  Once you have a 'staff list' parameter, it will appear in the drop down:

Click the drop down and select your parameter:

 You now have to populate the "Column with the Staff Object ID to filter:

What "Column with the Staff Object Id to filter" means is:  you need to match the Staff List parameter with the staff members' ObjectID.  The "ObjectID" (and not the local id - staffId - or the SMID) is what uniquely identifies each staff member.  You need to select the existing Column that contains the Staff member's "ObjectID".

If "Staff" is your base table, then you want the "Object Id" field for the Staff table (code=objectId):

If Staff is not your base table, you will need a field where the code is "staffObjectId".

Once you have your Staff List parameter and the Staff table's 'objectId' field, your Staff List parameter is complete:

Click "Save Staff List Filter Parameters" to permanently store your setup.

Expression Language Filters

These filters are applied after all other work is done and control the final set of rows that are included in the output.  They can be run before or after the report is sorted (See the Sorting article for more information on sorting the rows).

How do Expression Filters Work?

• Each Expression Filter must result in a Boolean true/false value.
• All where all Expression Language filters evaluate to 'true' are kept in the report.
• Any row where at least one Expression Language filter evaluates to "false" are suppressed.
• If you need an "OR" option (where A OR B can be true), the full A OR B expression must appear in the same Expression Language Filter.  

Creating an Expression Language Filter

Creating the Filter is the first step is really setting it up:

You can optionally select one column and one parameter to initially add to the filter.  You will be able to add others later after the filter has actually been created.    Note that none  of the fields are required.  It is possible to create an initially empty Expression Language Filter - however it must be fully setup later.

When you select a Column, a parameter or both, they are added to the Expression field as separate Expressions.  You need to make sure they work together to produce a single Boolean true/false value.

Editing the Expression and Controlling When It is Used

Once the filter has been created you will need to edit the "Expression" field itself to ensure it produces a true/false value.