Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SSRS Contains or Like Expression

I'm trying to create a calculated expression from a field in my dataset. I need to find everything that contain the word Exchange Traded from one field and in my new field have the words 'ETF 13F'. If nothing matches then it would just be blank.

I have tried the Like("Exchange Traded") and Contains ("Exchange Traded") functions and when running the report I get a #error in my new field.

Here's what I have tried...

=IIf(Fields!DESC2.Value.like("*Exchange Traded*"),"ETF_13F", false)

How do I write this wildcard expression and then replace with a different text in the new calculated field?

Thanks in advance

like image 538
BIReportGuy Avatar asked Nov 14 '14 19:11

BIReportGuy


People also ask

How do you write if condition in SSRS report expression?

location. Value = “CA”, “Bold”, “Italic”) SSRS iif statement The format of the IIF() statement is as follows: =IIF(Expression, Condition set when the expression is true, Condition set when the expression is false) It should be a Boolean expression, according to parameter 1.

How do I use IIF in SSRS expression?

Using IIF Function in SSRS We are going to add a new field to the report data set to determine if the Order Year is the Max or Current Year. As shown below, the dataset properties window is opened, and the Fields tab is selected. After clicking Add, at the bottom of the list a new field is added.


1 Answers

In String is your friend. You pass 2 arguments to the function InStr(), the first is the text to search and the second is the text you're searching for, it returns an Integer which represents the starting position of the text you're looking for in the text you told it to search. If it can't find the text, it returns 0, which you can use as false (or simply use >0 to represent true). So...

=iif(InStr(Fields!DESC2.Value, "Exchange Traded") > 0, "ETF_13F", Nothing)

So that's looking for the string "Exchange Traded" in the field "DESC2". If it finds "Exchange Traded" then it returns a value that is more than 0, so all we do is say "If this value is more than 0, show ETF_13F, otherwise show nothing"

Hope that helps

EDIT:

A few people have upvoted this, so as it's getting some visibility I'm going to update the answer to say there's a better way that someone clued me in to. You can simply use .Contains() on String fields to perform the same inspection:

=iif(Fields!DESC2.Value.Contains("Exchange Traded"), "ETF_13F", Nothing)
like image 55
Dan Scally Avatar answered Oct 21 '22 09:10

Dan Scally