Two multi-value parameters in SSRS

Question

I have two multi-value parameters in my SQL: where (PayToTIN IN (@PayToTIN) or PCP_ProvId IN (@PCP_ProvId). I am trying to drop in multiple values in either or parameters however when I drop in a value in either parameter, SSRS treats the other parameter as ''. I am also unable to null out the either param because they are multi-value. Is there a workaround to run multiple values in either one or the other parameter?

    Declare @PayToTIN as varchar(max) = '562660402' --'562660402'   
Declare @PCP_ProvId as varchar(max) --= 'LASRL12550' --'LASRL12550'
declare @detailfields as varchar(max) = NULL

declare @PayToTIN_switch varchar(max) =  
    (   select max(PayToTIN)
        from LA_Temp.dbo.vMemberPCP
        where cast(PayToTIN as varchar) in (@PayToTIN)
    )

declare @PCPAssignmentID_switch varchar(max) =  
    (   select max(PCP_ProvId)
        from LA_Temp.dbo.vMemberPCP
        where cast(PCP_ProvId as varchar) in (@PCP_ProvId)
    )

declare @include_PayToTIN bit = case when 'Pay To Tin' in (@detailfields) then 1 else 0 end 
declare @include_PCP_ProvId bit = case when 'PCP ProvID' in (@detailfields) then 1 else 0 end 

select distinct 
    x.CarrierMemID
--  , MemberName
    , MbrLastName =rtrim( e.LastName)
    , MbrFirstName = rtrim(e.FirstName)
    , MbrMI = rtrim(e.MiddleName)
    , x.DOB
    , x.Age
    , x.Sex
    , Address = e.addr1
    , City = e.city
    , State = e.state
    , Zip = e.zip
    , Parish = e.county
    , MemPhone = e.phone
    , PCPName
    , PCP_NPI
    , PCPProvID
    , PCPEffDate
    , Specialty
    , ServiceLocation
    , PayToProvider
    , PayToTIN
    , PayToProvID
    , PCP_ProvId 
    , PCPAssignment
from LA_Temp.dbo.vMemberPCP x
inner join PlanReport_QNXT_LA.dbo.enrollkeys ek
    on x.enrollid = ek.enrollid
inner join PlanReport_QNXT_LA.dbo.orgpolicy o
    on o.orgpolicyid = ek.orgpolicyid
    and right(rtrim(o.policynum),2) <> 'BH'
inner join PlanReport_QNXT_LA.dbo.member m
    on ek.memid = m.memid
inner join PlanReport_QNXT_LA.dbo.entity e
    on e.entid = m.entityid
--where  PayToTIN = @PayToTIN --AMB 05/07/18
where  (PayToTIN IN (@PayToTIN) 
                        or '' = @PayToTIN_switch
                )
        or (    PCP_ProvId IN (@PCP_ProvId)
                or '' = @PCPAssignmentID_switch
                    )

Jebbur Jebbur · Accepted Answer · 2020-08-20T23:01:24

where (case when PayToTIN = '' then null else PayToTIN end in (@PayToTIN)) 
OR (case when PCP_ProvId = '' then null else PCP_ProvId end in (@PCP_ProvId))

Two multi-value parameters in SSRS

1 Answers