Dataset Filter in SSRS - The IN Operator

Kinetic ERP
, ,
1

Good afternoon,
I am customizing the Job Traveler Report with a new style. In the Report Builder, I can see that there is a filter for the Dataset, and I can choose “IN” as an operator in that filter. I would like to use this to filter out a list of OpCodes from my report. I have dozens of opcodes to filter out. I was thinking I could use the “IN” operator, along with a list of OpCodes. How does that work in SSRS?

Capture245234
Capture245234615×636 38.3 KB

1 Like
2

As advised by Chat GPT, I have to use the split function:
that value expression that I circled should be:

=split("5DT,8BB,9,20,3DW",",")
1 Like
3

While this approach worked when filtering the dataset, it looks like I am going to have to use the visibility flag to hide the rows that have the wrong OpCodes. I am trying to use this statement in the expression for the row group Visibility. Basically show me just this list of opcodes. If it is not in the list, hide it.

=if(Fields!OpCode.Value in split("13-OP,10AB-OP,8BB-OP",",") = true, false, true)

also tried this:
=if(Fields!OpCode.Value in split("13-OP,10AB-OP,8BB-OP",","), false, true)
also tried using iif instead of if

But I keep getting this error:

Summary

Error calling UpdateCatalogItem.
ErrorCode: InternalServerError(500)
Content: {
“error”:{
“code”:“1027”,“message”:“The Visibility.Hidden expression for the tablix ‘Tablix2’ contains an error: [BC33104] ‘If’ operator requires either two or three operands.”
}
}

5

=IIF(InStr(Fields!OpCode.Value, “13-OP,10AB-OP,8BB-OP”)>0,False,True)

This should search for you “value” within the string and return an integer position of the beginning of that string. So, anything >0 means its in there… so false, don’t hide, else hide.

… worth a shot.

1 Like
6

If there is a chance that there will be an opcode string that’s a subset of another opcode string, then you also have to look for the delimiter on both ends, or beginning of the string, or end of the string. Not a big deal, just add opcode & "," = left(whatever) or "," & opcode = right(whatever).

1 Like
7

Wouldn’t I still need a split function in there someplace to check each of the substrings?

8

I think it just treats it like one big long string. But I’m not exactly sure what you’re looking for, I guess.

If your OpCode value was “10AB-OP”… the expression should give a result of (7)… your value starts at the 7th character position. Since that is >0, it shouldn’t be hidden.

Again, not overly sure this would work. That why I said it was “worth a shot” haha.

1 Like
9

This seems to have done the trick. I placed this code in the lowest group’s visible expression.

=IIF(Fields!OprSeq.Value = First(Fields!OprSeq.Value, "BOM_Seq3"), False, IIF(InStr(Fields!OpCode.Value, "20-OP")>0 OR InStr(Fields!OpCode.Value, "FP-OP")>0 OR InStr(Fields!OpCode.Value, "MP-OP")>0 OR InStr(Fields!OpCode.Value, "11HT-OP")>0 OR InStr(Fields!OpCode.Value, "11PF-OP")>0 OR InStr(Fields!OpCode.Value, "9-OP")>0 OR InStr(Fields!OpCode.Value, "10MB-OP")>0 OR InStr(Fields!OpCode.Value, "10-OP")>0, True, False))

First it looks to see if it is the first op. If so, it shows it in the list. If not, then it check to see if it is in the filtered list. If it is, then it is hidden, if it is not, then it is shown.
Thanks for your help guys!

I would love to clean up this expression to have all the filter values in a single, easy-to-read list. But I will take what I can get! This works for now.