Filtering reports with SQL

Despite the name, this does not let you write conditions. It controls the ORDER BY and LIMIT of the generated query, and nothing else.

Despite the name, this does not let you write conditions. It controls the ORDER BY and LIMIT of the query Explore generates, and nothing else.

That is worth establishing before you go looking, because the name promises a general escape hatch and the feature is a narrow one. If you came here to express a condition the ordinary filters cannot, the answer is a calculated attribute, further down.

Opening it

1. Create a new report, or open an existing one, in Explore.
2. Click the Result manipulation menu icon.
3. Select SQL options.
4. Set the ORDER BY clause to control how results are sorted, and the LIMIT clause to control how many come back.
5. Compare the row count before and after, then save.

The panel also has a Replace NULL by field. Setting it to NULL writes the word into blank cells, which is easier to read than an empty one and removes the ambiguity between no value and a value of zero.

Zendesk's own caveat

The feature is documented as intended only for people comfortable working with SQL. That is not a formality: nothing validates your intent, and a clause that runs is not the same as a clause that is right.

What to use for an actual condition

Three cases are not solved here at all.

• Comparing two fields against each other rather than against a value: a standard calculated attribute.
• A condition across several fields the filter interface cannot express: a standard calculated attribute, then filter on it.
• Selecting a specific set of records for a one-off investigation: ordinary filters, or a filter on a calculated attribute.

That route is better anyway. A calculated attribute is readable, changeable, and visible to somebody who did not build it.

A LIMIT is invisible in the result

This is where the feature quietly misleads. A report with a limit shows a smaller number and looks complete, and nobody opening it later has any reason to suspect a clause is cutting the results off.

Say so in the report title or a caption. "Top 20 by volume" is honest; a bare count is not.

Check the effect on the count

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

Prefer one place

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

See also

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