How to Create Join Filters

Join filters filter data in tables that are joined to the primary table with an outer join.

Join filters are fixed and cannot be changed when the report is run. Join filters are similar to report filters in that they set restrictions on the records that can be returned by the report, but they apply to the table join instead of the data returned by the query on the database. Join filters limit the data contained in the secondary table of the table join. Without join filters, there would be no way to add a report filter to limit returned data from the secondary table, while not preventing data from the primary table from being returned.

Note: From an SQL perspective, join filters are a condition of the ON clause, whereas report filters are a condition of the WHERE clause.

For instance, suppose you want your report to return a list of organizations and the number of incidents they have opened before January 1st. You also want to return any organizations even if they have not submitted any incidents, so you need to join the incidents table to the organizations table with an outer join. Using an inner join would prevent organizations that have no associated incidents from being returned in the report. You would then add a join filter of incidents.created < January 1st. If you also wanted incidents created after March 1st, you would add an OR join filter of incidents.created > March 1st to the first filter. The resulting join filters definition would be incidents.created < January 1st OR incidents.created > March 1st.

The method of adding join filters is similar to that for adding report filters. However, unlike report filters, join filters allow you to use either a constant value, as report filters do, or an expression you create from the database fields and the functions. See Functions for Reports for function descriptions.