Filtering reports with SQL

Filtering a report with a query for cases the ordinary filters cannot express. Powerful, and rarely the right first answer.

Filtering a report with a query for cases the ordinary filters cannot express. Powerful, and rarely the right first answer.

Use it

1. Open the report.
2. Add a query-based filter.
3. Write the condition.
4. Compare the row count before and after.
5. Save, and write down what the query does.

Try the ordinary filters first

Most requirements that feel like they need a query are a filter plus a calculated attribute. That combination is easier to read, easier to change, and visible to somebody who did not build it.

Where a query genuinely helps

• Comparing two fields against each other rather than against a value.
• Conditions involving several fields in a way the filter interface cannot express.
• Selecting a specific set of records for a one-off investigation.

It becomes invisible

A query filter is not obvious to anybody opening the report later. They see a number and no reason for it, and the reason is hidden inside a filter they may not think to open.

Put the condition in the report title or a caption.

Check the effect on the count

Before and after, every time. A query with a subtle error still runs and returns a smaller, plausible number.

It is fragile

A field renamed or a value changed elsewhere breaks the query silently. Anything built on one needs rechecking after configuration changes.

Prefer one place

If several reports need the same condition, put it into a calculated attribute on the dataset instead and filter on that. One definition, changed in one place.

See also

• Adding filters
• Creating a standard calculated attribute
• Writing formulas