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

ssrs-2008 reporting-services reportingservices-2005
Rajaram Shelar picture Rajaram Shelar · Oct 10, 2012 · Viewed 73.6k times · Source

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.

Answer

Tim Abell picture Tim Abell · May 19, 2015
  • 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.

Related questions

How to get named excel sheets while exporting from SSRS

Whenever a single page report is exported to excel, sheet in excel is named by the report name. If a report has multiple pages, the sheets are named as sheet1, sheet2,.... Is there any way to specify sheet names in …

Read More
how to set SSRS Report parameters to be optional i.e Non-Mandatory field in SSRS 2005

Please help me to set the SSRS Report parameters to be optional i.e Non-Mandatory field in SSRS 2005.

Read More
How do I pass parameter TO SSRS report FROM ASP.NET page which has the reportviewer for the report

I have an ASP.NET page which has report viewer control to access reports from report manager. I need to pass report parameter from this ASP.NET page to the report server. I want to pass region number based on …

Read More