CODEWITHSUNDEEP
reporting-servicesfilterdataset
Henrov
Henrov

Reputation: 1610

Filter dataset on either value OR nothing

I have a dataset that contains a lot of records. I would like to include all those records where PType in @Ptype OR PType = ''

YEARMONTH --|-- PTYPE --|-- VALUE
201207 --|-- bike --|-- 600
201208 --|-- moped --|-- 0
201209 --|-- '' --|-- 0
201210 --|-- bike --|-- 600
201211 --|-- '' --|-- 0
201212 --|-- car--|-- 1200

This is filtered using a parameter. The filtering occurs in the report on the reference to the shared dataset; 

Expression:PType
Operator: in 
Value: @Ptype

Now when @PType contains ('bike','moped')

I get this resultset:

YEARMONTH --|-- PTYPE --|-- VALUE
201207 --|-- bike --|-- 600
201208 --|-- moped --|-- 0
201210 --|-- bike --|-- 600

but I would like 

YEARMONTH --|-- PTYPE --|-- VALUE
201207 --|-- bike --|-- 600
201208 --|-- moped --|-- 0
201209 --|-- '' --|-- 0
201210 --|-- bike --|-- 600
201211 --|-- '' --|-- 0

So I would also like to see those records where Ptype is Empty/NULL/Nothing. Is this possible?

If so, how?

Upvotes: 3

Views: 8126

Answers (1)

Ian Preston
Ian Preston

Reputation: 39566

You can set the filter expression to be something like:

=IIf(IsNothing(Fields!PTYPE.Value)
      or Fields!PTYPE.Value = ""
      or InStr(Join(Parameters!PType.Value, ","), Fields!PTYPE.Value) > 0
    , true
    , false)

Set this as a boolean filter to show when the expression = true.

This will check if the field exists in the multi-value parameter, but will also include NULL values and empty string values.

Works OK for me:

enter image description here

Upvotes: 5

Related Questions