Using 'Like' in Ssrs Expressions

Using 'like' in ssrs expressions

SSRS does NOT use SQL syntax, but instead uses Visual Basic.

Use something like this:

=IIf(Fields!Notes.Value.IndexOf("deadline") >= 0,"Yellow","Transparent")

Or .Contains instead of .IndexOf

=IIf(Fields!Notes.Value.ToLowerInvariant().Contains("deadline"),"Yellow","Transparent")

SSRS Contains or Like Expression

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)

SSRS IF/Like/Then Expression

To get conditional Sum values (or any other aggregate) you can use an expression like:

=Sum(IIf(Fields!AcctNum.Value like "*.001", Fields!Balance.Value, Nothing))

This only considers rows that satisfy your first criterion (i.e. certain AcctNum values) for the aggregation.

Using Parameters in SSRS with LIKE operator

It doesn't work without the quotes. I thought the quotes and wildcard
would be necessary to allow the LIKE operator to work.

First of all you don't need to use quotes, I tried to reproduce it right now and it works without quotes:

Second, the query that SSRS sends to server looks like this:

exec sp_executesql N'select model
from [dbo].[cars]
where model like @car',N'@car nvarchar(3)',@car=N'ca%'

So the only thing it changes respect to your code is parameter type, it always declares it as unicode, i.e. nvarchar(...), so maybe it make sense to try in SSMS your code with parameter type changed to nvarchar(12):

declare ProductCode nvarchar(5) = N'PA.A%'

Try to execute it in SSMS and see if it returns smth or not

SSRS - Adding LIKE filter criteria to report builder

You could use the InStr function

=IIF(InStr(Fields!MyFieldName.Value, "2451"),TRUE,FALSE)

SSRS 'Like' Expression comparing two fields

INSTR() function returns an integer specifying the start position of the first occurence of the string.

It returns 0 if the string is not contained.

Try:

=iif(instr(Fields!JT_Name.Value, Fields!Base_Name.Value)=0,"Transparent","Yellow")

Or if you want to apply the yellow color when the string is not contained try:

=iif(instr(Fields!JT_Name.Value, Fields!Base_Name.Value)>0,"Transparent","Yellow")

Let me know if this helps.

---

Related Topics