select all not working in Report

Question

User496086301 posted

Parameters:

MemberTypeId,FullName,Active

Active Parameter Properties:->Allow Multiple Values->Available Values:Active=0, Inactive=1

Query:

select UserId,FullName,Active from dbo.Users where MemberTypeId=@MemberTypeId and (Active=@Active) and (((FullName like '%'+@FullName+'%' or @FullName IS NULL) and @MemberTypeId=1) or @MemberTypeId=0)

So the active will be a dropdown of Select All, Active,Inactive.

Active and Inactive works but not select all. I would like to have an option of select all where it displays all active and inactive.

Answer 1

User-1716253493 posted

select UserId,FullName,Active from dbo.Users where (Active=@Active) and (FullName like '%' + @FullName + '%' 
or @FullName IS NULL) and (@MemberTypeId is null or MemberTypeId=@MemberTypeId)

Answer 2

User496086301 posted

Not working with select all both active and inactive

Answer 3

User1644755831 posted

Hello sushsudh,

So the active will be a dropdown of Select All, Active,Inactive.

Active and Inactive works but not select all. I would like to have an option of select all where it displays all active and inactive.

You could try this.

1.) Setting the "Allow Null Value" checkbox to TRUE

2.) Setting NULL as the default value for the parameter. I will assume that SelectAll will give me NULL value for the @Active. Then you could do something like this.

select UserId,FullName,Active from dbo.Users where MemberTypeId=@MemberTypeId and (Active=@Active OR @Active IS NULL) and (((FullName like '%'+@FullName+'%' or @FullName IS NULL) and @MemberTypeId=1) or @MemberTypeId=0)

Add @Active IS NULL condition so when select all is chosen all active and inactive users will be shown.

Hope is helps.

With Regards,

Krunal Parekh 

Answer 4

User496086301 posted

we can't select both Allow Null Value and Allow Multiple values.

Either 1 only. 

Answer 5

User1644755831 posted

Hello,

Please see this: https://www.mssqltips.com/sqlservertip/2866/sql-server-reporting-services-using-multivalue-parameters/

set allow multiple then Can you please try it like this.

select UserId,FullName,Active from dbo.Users where MemberTypeId=@MemberTypeId and (Active IN (@Active)) and (((FullName like '%'+@FullName+'%' or @FullName IS NULL) and @MemberTypeId=1) or @MemberTypeId=0)

Hope this helps.