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?

Hmm, I think I may have realized what I did wrong. Turns out this works rather well: WHERE (somefield = ? OR CAST(? AS <somefield's data type>) IS NULL). So, the CAST on the parameter ? is necessary, otherwise the SSRS tooling gives you an error of unknown data type. And I have to use 2 parameters in the Data Set to refer to the same parameter twice (because of 2 ? references in the WHERE clause), but that's not terrible. Don't know why I didn't think of casting before. — Jiveman

Clodoaldo Neto Clodoaldo Neto · Accepted Answer · 2015-12-29T20:31:03

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

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

1 Answers