Filter your data based on null, not null or all values

Question

Hi All,

I have a report with parameter and filter. Now I want to filter data based on null, not null or all values. The below works and show All and NULL but doesn't work for NOT NULL values.

Created a sample table and insert some null values (on Amount column).
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”,”NULL” and “NOT NULL” 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="NULL",nothing,Parameters!B.Value))
Save and preview. The result looks like below:

Any ideas?

Many thanks
M224

Answer 1

Hi @M224 ,

Thanks for your detailed steps.

I followed that and just modify the filter expression like below and it works fine with NOT NULL values.

Expressions: =Fields!Amount.Value
Operator: In
Value: =
switch(Parameters!A.Value(0)="ALL",Fields!Amount.Value,Parameters!A.Value(0)="NULL",nothing,Parameters!A.Value(0)="NOT NULL",Parameters!B.Value)

Regards，

Zoe

---

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.
Hot issues October

Answer 2

Hi Zoe,

Are you able to tell if in the above example you created 2 Datasets or just 1?

Many thanks
M224

Answer 3

Hi Zoe,

All working as expected!
Many thanks

M224