SSRS Query parameter is not working correctly

Question

I have 4 parameters in my SSRS reprot data is being captured with SP

        ALTER PROCEDURE [adjuster].[rpt_HIIGAdjusterLicense]
@Adjustername varchar (max),
@State char (3),
@LicenseStatus char (3),
@RenewalNotAllowed varchar (5)
AS BEGIN(
SELECT  
 FirstName +' '+ LastName as AdjusterName ,
 StateLicense,
 LicenseNumber,
 LicenseStatus,
 ExpirationDate,
 RenewalNotAllowed,
 NPN
FROM adjuster.vw_HIIGAdjusterLicense   
Where ( FirstName LIKE (CASE WHEN @Adjustername IS NULL THEN FirstName ELSE @Adjustername + '%' END))
OR  (@State = 'ALL'  OR @State IN (SELECT Value FROM dbo.FnSplit(@State, ',')))
OR ( @LicenseStatus = 'ALL' OR @LicenseStatus   IN (SELECT Value FROM dbo.FnSplit(@LicenseStatus, ',')))
AND  (@RenewalNotAllowed = 'ALL' OR @RenewalNotAllowed IN (SELECT Value FROM dbo.FnSplit(@RenewalNotAllowed, ',')))
)
END

GO

and Parameter Datasets are

SELECT DISTINCT 2 AS Priority, LicenseStatus
FROM            vw_License
UNION ALL
SELECT        1 AS Priority, 'ALL' AS Choice
ORDER BY Priority, LicenseStatus



SELECT DISTINCT 2 AS Priority, StateLicense
FROM            vw_License
UNION ALL
SELECT        1 AS Priority, 'ALL' AS Choice
ORDER BY Priority, StateLicense



SELECT DISTINCT 2 AS Priority, RenewalNotAllowed
FROM            LicenseDetail
UNION ALL
SELECT        1 AS Priority, 'ALL' AS Choice
ORDER BY Priority, RenewalNotAllowed

When In SSRS when Name value is 'null' state value is 'ALL' and Status value is 'All' and RenewalNotAllowed Value is 'N'. The report is still showing records that have 'Y' value of RenewalNotAllowed .

but is should show records with 'N' value for RenewalNotAllowed. please refer below picture

Personally, I would start by fixing your query, All those ISNULL's are going to ruin performance. Use proper boolean logic. I wouldn't be surprised if that tells you why the report isn't behaving as your expect too when you do that, — Larnu
@Larnu can you please update it accordingly as you are mentioning it should be — ETL - BI
Update what accordingly? If you need to make changes to your question, you should be doing that with the edit feature. — Larnu
@Larnu I have updated SP can you please suggest me how can i show Y values only and N values only — ETL - BI
@RenewalNotAllowed appears to be defined as a delimited value, but your image says otherwise. Infact, all you're parameters appear to defined as delimited values. Is that right? I would guess you're not just passing 'Y'. A minimal reproducible example would help. — Larnu

Alan Schofield Alan Schofield · Accepted Answer · 2020-06-23T09:53:45

Change the outer ORs in the where clause for @State and @LicenseStatus to ANDs

Currently if @State is 'ALL' then the 2nd line in your WHERE clause evaluates to True for every row so every row would be returned.

SSRS Query parameter is not working correctly

1 Answers