CODEWITHSUNDEEP
reporting-services
Hisham Saeed
Hisham Saeed

Reputation: 13

SSRS Passing parameters to oracle DB Issue

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!!

Upvotes: 0

Views: 525

Answers (1)

Hannover Fist
Hannover Fist

Reputation: 10870

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.

Upvotes: 0

Related Questions