SSRS Parameter with ALL In OUT

Question

CREATE TABLE temp(
Name nvarchar(50),
Amount int)
Create a table and a parameter (named A) in report. Choose specify values in Available Values for parameter A. Add three values, type “ALL”,”IN” and “OUT” in both Label and Value textbox.
Create one more dataset (named DataSet2) to get all not null values. In our sample report, we put the text below into Query:
select * from temp where Amount is not null
Create one more parameter (named B). Select Allow multiple values and set Visibility Hidden. Set the data type same as the data type in your table (in our sample, Amount is integer). Select corresponding dataset and value field in Available Values (in our sample is DataSet2 and Amount).
Create a filter in your Tablix Properties.
Expressions: =Fields!Amount.Value
Operator: In
Value: =iif(Parameters!A.Value="ALL",Fields!Amount.Value,iif(Parameters!A.Value="IN",nothing,Parameters!B.Value))
Save and preview. The result looks like below:

Any ideas?

Many thanks
M224

Answer 1

Answer 2

Please refer to my steps below:

Answer 3

Hi @M224 ,
I did some tests.This is the effect you want：

I modified on your basis:
ParameterA:

ParameterB:

Filters on Dataset 1:

The expression here is:

=IIf(Parameters!A.Value="1",Fields!Amount.Value,iif(Parameters!A.Value="3",nothing,Parameters!B.Value))  

Hope this helps.
Best Regards,
Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Hi Joy,

This is what I'm trying to archive.

Answer 5

the issue I'm facing with the proposed expression is that if I select "IN" it will give a bank result instead.