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

M224 21 Reputation points
2021-04-21T06:16:37.873+00:00

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

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,126 questions
No comments
{count} votes

Accepted answer
  1. ZoeHui-MSFT 18,896 Reputation points Microsoft Employee
    2021-04-22T03:09:36.723+00:00

    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)

    90144-screenshot-2021-04-22-110842.jpg
    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


2 additional answers

Sort by: Most helpful
  1. M224 21 Reputation points
    2021-04-22T04:52:13.797+00:00

    Hi Zoe,

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

    Many thanks
    M224


  2. M224 21 Reputation points
    2021-04-26T23:15:57.947+00:00

    Hi Zoe,

    All working as expected!
    Many thanks

    M224

    No comments