SSRS Report - How to make parameter flexible-CodePudding

I want the SSRS report have flexible parameter.

Example, got two parameter as OTP No (Text field) and Branch Code(multiple values from dropdown).

If want to search based on OTP No, report should only search based on OTP No.

Same goes to Branch Code if want to search based on Branch Code, report should only search based on Branch Code. If both parameter got value, then report should search both field A and field B.

Below is my query.

where ((@otpNo ='' ) and (@branchCode is not null) and (branch_code in (@branchCode))) 
or ((@branchCode is null) and (@otpNo is not null) and (otp_no = @otpNo))
or ((@otpNo is not null) and (@branchCode is null) and (otp_no = @otpNo) and (branch_code in (@branchCode)))

However I encountered error on this and I suspect branch code is multi values which cause my query not working.

CodePudding user response：

You should have the branch code send all the values (if the user doesn't want to filter for a specific one) to make the query simpler.

where 
   (NULLIF(@otpNo, '') IS NULL OR otpNo = @otpNo) AND
   branch_code IN (@branchCode)