CODEWITHSUNDEEP
ssrs-2008reporting-servicesreportingservices-2005
Rajaram Shelar
Rajaram Shelar

Reputation: 7877

How to filter rows with null values in any of its columns in SSRS

I want to filter out the output without rows containing null values or blank columns. I am using SQL Server 2012 there is no option named 'Blank' as in SS2005 where I can filter the rows. I also tried following expression but it gives me error or not showing correct output

=IsNothing(Fields!ABC.Value)!= True 
=Fields!ABC.Value = ''

Please suggest the solution.

Upvotes: 26

Views: 104142

Answers (3)

Aasai
Aasai

Reputation: 414

We should use the isNothing method in the Expression, change the Text to Boolean and then Value will be "True"

for example:

Expression 
=IsNothing(Fields!TestA.Value)<>True
(Expression type should be Boolean)
Operator
=
Value
=True

Upvotes: 38

Tim Abell
Tim Abell

Reputation: 11880

  • Pull up the tablix or group properties
  • Switch to "Filters"
  • Add a new filter

  • Set the expression to:

    =IsNothing(Fields!YourFieldHere.Value)

  • Set the type to "Boolean" (see screenshot below) otherwise you'll get a "cannot compare data of types boolean and string" error.

  • Set the value to false

screenshot of grouping dialog, higlighting expression type setting

This works for filtering both rows and groups.

Upvotes: 60

Naureen
Naureen

Reputation: 21

Edit the SQL query, so that it will not return NULL values in the column to group on, but let it return a dummy value; for example: ISNULL(columnA, 'dummy')

In the column group definition add a filter: ColumnA <> 'dummy'.

Upvotes: 2

Related Questions