SSRS contains or as an expression
I am trying to create a calculated expression from a field in my dataset. I need to find anything that contains the word Exchange Traded from one field and my new field has the words "ETF 13F" in it. If nothing is found, it will just be empty.
I tried to use the "Like" ("Exchange Traded") feature and contains ("Exchange Traded"), and when I run the report, I get #error in my new field.
Here's what I've tried ...
=IIf(Fields!DESC2.Value.like("*Exchange Traded*"),"ETF_13F", false)
How do I write this wildcard expression and then replace it with other text in the new calculated field?
Thank you in advance
source to share
The String is your friend. You pass 2 arguments to the InStr () function, the first is the text to search for and the second is the text you are looking for, it returns an integer that represents the starting position of the text you are looking for in the text you said to search. If it can't find the text, it returns 0, which you can use as false (or just use >0
true to represent). So that...
=Iif(InStr(Fields!DESC2.Value, "Exchange Traded") > 0, "ETF_13F", Nothing)
So, search for the string "Exchange Traded" in the "DESC2" field. If it detects "Exchange Traded" then it returns a value greater than 0, so all we do is "If this value is greater than 0, show ETF_13F, otherwise show nothing"
Hope it helps
EDIT:
Several people have supported this, so after gaining some visibility, I'm going to update the answer to say that there is a better way that someone attracted me to. You can just use .Contains () fields on strings to do the same check:
'= iif (Fields! DESC2.Value.Contains ("Exchange Traded"), "ETF_13F", Nothing) `
source to share