CODEWITHSUNDEEP
reporting-servicesssrs-2008reportbuilder
David Silva Smith
David Silva Smith

Reputation: 11706

How do I make wildcards work in a like operator in SQL Server reporting services?

I have added a filter expression using the like operator. What do I put in the value field? my parameter is named @test and I'd like the filter to function as a like %@test% would in SQL. I want it to act like %@test% so I can pass '' to get all values for the query.

Upvotes: 12

Views: 58644

Answers (5)

Bhavik Desai
Bhavik Desai

Reputation: 1

You need to set Parameter Value with Like operator and that will work like a charm ! (use double quotes(") near following example instead of single quote(') as the editor was not allowing me to put double quotes with asterisk(*) symbol)

LIKE "asterisk" & Parameters!Roles.Value & "asterisk"

Upvotes: 0

Ramgy Borja
Ramgy Borja

Reputation: 2458

you could use this simple query.

WHERE fieldname LIKE CONCAT('%',@test,'%')

Used CONCAT

Upvotes: 0

Daniel LL
Daniel LL

Reputation: 1

You should use this Query in the DATA SECCTION of the REPORT WIZARD.

SELECT * FROM HR.JOBS WHERE JOB_TITLE LIKE  :JOB_TITLE | |'%'

Upvotes: 0

JBarnes
JBarnes

Reputation: 491

You could also simply use string concatenation in the original Dataset that you want to filter on. You can access the parameters in the same manner as SQL:

WHERE fieldname LIKE '%' + @test + '%'

You can then link the report parameter @test to the @test in the dataset via the parameters option on Dataset Properties.

Upvotes: 18

David Silva Smith
David Silva Smith

Reputation: 11706

I got this to work by adding a filter expression with a like operator and setting the value to this:

="*" + Parameters!Roles.Value + "*"

To get all values I pass ''.

Upvotes: 19

Related Questions