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