OLE DB connection in SSRS doesn't like when parameters are repeated

Question

I'm changing an Oracle based SSRS report and I'm having all sorts of issues with parameters.

The connection to Oracle is OLE DB.

My code is not doing anything complicated. I've only added in a new parameter. When I only have one instance of said parameter, it runs without any issues. As soon as I add it again, it bombs.

What I'm trying to do is show records if a parameter has a match. If no match, show all records.

I can run both queries in DBVisualizer without any issues.

This is what I've done

WHERE FieldName = nvl(:parameter, FieldName)

This one doesn't return the same results as this below

WHERE FieldName = :parameter 
   OR :parameter IS NULL

Problem is the second WHERE clause will not run in SSRS with an OLE DB connection. We cannot use another connection manager, unfortunately.

EDIT: Thanks to Hannover Fist, I was able to get this to work by doing this

I changed my WHERE clause to

WHERE FieldName = :parameter
   OR :parameter2 IS NULL

Then mapped parameter2 to pull from the same SSRS parameter as the original parameter

Answer 1

I haven't found a good solution to this problem but I have worked around it by declaring the parameter in the Oracle SQL and mapping it to the SSRS parameter.

Then use the parameter created in the Oracle SQL in the rest of the query. This way you'll only use each SSRS parameter once.