24

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Rajaram Shelar
  • 7,537
  • 24
  • 66
  • 107
  • 1
    If you apply filter via dataset or tablix properties (Filters tab) your condition may look like Expression - Fields!ABC.Value, operator <>, Value =Nothing – user1578107 Oct 10 '12 at 19:54

3 Answers3

55
  • 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.

Tim Abell
  • 11,186
  • 8
  • 79
  • 110
  • This is not working for me. I'm getting `The FilterValue expression for the tablix ‘Tablix1’ has the value “False”, which is not a valid Boolean value` – Drew Chapin Apr 19 '17 at 20:36
  • I might be a little too late. Have you changed the type from text to Boolean as in the screenshot above from @Tim Abell. – Abe Jun 23 '17 at 13:35
36

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
KirstieBallance
  • 1,238
  • 12
  • 26
Aasai
  • 394
  • 5
  • 6
  • 11
    Within the Filter dialogue, in the Value field, if you use ="True" then that is a string and will cause an error because it cannot be compared to your Boolean expression. You just need to use the constant TRUE and write that directly into the value field. – Davos Jul 24 '13 at 01:11
  • @Aasai Say for example I'm looking at the text field in the Expression dialog box for setting the expression....what part of all that should I type there? – Jay May 31 '19 at 15:09
2

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'.

Naureen
  • 21
  • 2