Toggling WHERE clause conditions with Query Based Dropdown List Query Parameters

You can toggle filters (or whatever you want) on and off by creating a query as the source for a Query Parameter’s Query Based Dropdown List (QBDL) like so:

select '--' as value, 'false' as name
union all
select '' as value, 'true' as name

Which returns:

And then in the query you’re applying it to just do this:

...
WHERE 1=1
{{ Condition Toggle }} AND table.column = value

And then connect the query parameter to the QBDL query you created:

Then you just let the user toggle it on or off from the Query Parameter dropdown box, so if the report consumer chooses “disable [whatever] filter” it will insert double dashes to comment out the line with the condition and if the enable it, it leaves it uncommented.

Below is an example where I combined it with additional filters, so the user can decide to either filter by a state or account property. If they set Filter by State = True then it basically doesn’t do anything and the Account State filter gets applied. If they set it to false then it inserts the -- from the QBDL query and it comments the line out.

I’ve also been using this to allow the user to toggle columns on and off:

Can you add a couple screenshots?

Neat tip, particularly for those cases where you want to show either ALL or just one subset of the data. One thing, I found that the value for ‘true’ in the parameter query has to contain a space between the quotes or it returns an error. That’s PostGres data source, not sure if that makes a difference here…

Updated it. I’m open to more feedback if it still needs to be a bit clearer.

It’s working fine with postgres for me, but I’m using true/false as the names rather than the values here. The values are either an empty string or the double dash inline comment symbol.