1

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

Using 2 parameters in Oracle and mapping them to 1 SSRS

jdids
  • 561
  • 1
  • 7
  • 22
  • 1
    It's been a while since I connected to an Oracle server but I think I used to declare another parameter in the query to map to the SSRS parameter then use the query parameter in multiple locations. – Hannover Fist Feb 14 '19 at 17:34
  • That worked. If you add in the answer section I can give you credit for this solution. Thanks! – jdids Feb 14 '19 at 18:45

1 Answers1

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.

Hannover Fist
  • 10,393
  • 1
  • 18
  • 39