SSRS Passing parameters to oracle DB Issue

Question

i have a report in ssrs with odbc data source as the data is stored in oracle database in the normal scenario while adding some filters in ssrs report they are not working after searching, i found that i can add these filters in the dataset query as below and this expression worked find

="SELECT * FROM VIEW_NAME " & IIF(IsNothing(Parameters!partynumber.Value),"","WHERE A.PARTY_NUMBER=:partynumber )

but when i tried to add another parameter in this expression nothing returned from the query after using the filters

so please is there any idea can help!!

Hannover Fist Hannover Fist · Accepted Answer · 2021-05-10T15:58:30

Since you are already building your query with the Dataset's Expression Builder, why not use it for your Parameter?

="SELECT * FROM VIEW_NAME A " & 
    IIF(IsNothing(Parameters!partynumber.Value),
        "",
        "WHERE A.PARTY_NUMBER = " & Parameters!partynumber.Value )

Usually when I used Oracle with SSRS, I would DECLARE all of the parameter and set them up front so you're query would be:

DECLARE :partynumber INTEGER = ?

SELECT * FROM VIEW_NAME A 
WHERE (
        A.PARTY_NUMBER = :partynumber
        OR 
        :partynumber is NULL
    )

And then set your parameter in the PARAMTER tab of the dataset properties.

SSRS Passing parameters to oracle DB Issue

1 Answers