CODEWITHSUNDEEP
reporting-servicesssrs-2008
Eliana
Eliana

Reputation: 181

conditional filter SSRS

My situation is

  1. I have a parameter, this is a list, allowing multi values. That mean the first record in the list is 'Select All'

  2. When user select All I need to include in my report all records that match with the list plus those that are blank. (My dataset is returning these)

  3. When user select only 1 or a few I want to include only these records. No those that are blank

My problem: I have a filter in my dataset that evaluate a parameter in a list, but I need to add a conditional filter to include a blank records when the selection will be "Select All" I tried to use expression but this doesn't work

Filter expression Fields!NAME.Value in = Parameters!List.Value !!!!!!!!!!!! Work Fine

But I need to change it like as

If Parameters!List.Value  =  'Select All' Then
   Fields!NAME.Value in = Parameters!List.Value  or Fields!NAME.Value = " "
Else
   Fields!NAME.Value in = Parameters!List.Value
End

Can you give an advice who can I resolve it please !!!

I'm working in SSRS R2

Thanks!!

Upvotes: 2

Views: 20160

Answers (4)

David Macdonald
David Macdonald

Reputation: 21

You can put the logic in just one location if you do it this way. You filter on the parameter, unless it's all values then the filter always matches. Just a little cleaner.

Expression: =IIF(Parameters!pLocation.Value <> " All Locations", Fields!LOCATION.Value, " All Locations")

Operator: =

Value: =Parameters!pLocation.Value

Upvotes: 2

user2434182
user2434182

Reputation: 51

This worked for me

Expression: =IIF(Parameters!pLocation.Value <> " All Locations", Fields!LOCATION.Value, FALSE)
Operator: =
Value: =IIF(Parameters!pLocation.Value <> " All Locations", Parameters!pLocation.Value, FALSE)

Upvotes: 5

Irawan Soetomo
Irawan Soetomo

Reputation: 1325

If you use Filter on your Dataset, try this:

Expression: [NAME]
Operator: IN
Value (fx): =Split(Replace(Join(Parameters!List.Value, ","), "Select All", " "), ",")

Try to work along this path. Basically you can reconstruct the multi value items into a string with Join(), and deconstruct it again into array by using Split(); where in between, you can manipulate them, for modifying (e.g. converting "Select All" into " "), adding (imitating "OR"), or removing extra items.

Upvotes: 2

M.C.Rohith
M.C.Rohith

Reputation: 3750

There is an alternative for this.

Add one more item to the paramater dataset values say "Not Available" as Label and value with the null. then there will be no change in the stored procedure and you can retrieve the data. If the user select the specific item then he will get those values only. If he selects all then he will get the data for the null also with the all the others.

Hope this will help

Upvotes: 1

Related Questions