SSRS and PostgreSQL: How do I pass an optional parameter?

Question

I'm using a PostgreSQL ODBC data source in an SSRS report.

From my previous knowledge with MS SQL Server data sources, to pass an optional parameter to a SSRS dataset query, you would do this:

SELECT *
FROM MyTable
WHERE SomeField = @Param1 OR @Param1 IS NULL

The key was using OR @Param1 IS NULL which basically bypasses the SomeField = @Param1 part and effectively acts as an "optional" filter. However, this doesn't seem to work when using a PostgreSQL ODBC data source (not sure if it's due to Postgres or ODBC or both). I get an error when clicking on Refresh Fields, or when executing/previewing the report.

This might be partly due to not being able to pass named parameters into the SQL, but instead having to use "?" like so:

SELECT *
FROM MyTable
WHERE SomeField = ?

...which makes it impossible to do OR ? IS NULL basically.

So, anybody know how I might be able to pass optional parameters to a PostgreSQL data set in SSRS?

Answer 1

Not a specific ODBC solution but a workaround. This will work if SomeField can not be null:

select *
from mytable
where somefield = coalesce(?, somefield)

If SomeField can be null and you want to return nulls:

where somefield is not distinct from coalesce(?, somefield)

Check is distinct from