CODEWITHSUNDEEP
reporting-servicesssrs-2008
Nite Cat
Nite Cat

Reputation: 533

SSRS Wildcard search in Report Parameters

How do I write an SSRS Wildcard search in the Report Parameters

SELECT * FROM Table1 WHERE Table1.Name = LIKE '%'@Name'%'

or

WHERE Table1.Name = in (:Name)?

How do I do this in SSRS?

Upvotes: 0

Views: 37978

Answers (4)

Robert Wilkes
Robert Wilkes

Reputation: 1

For a filter on the dataset, instead of = use LIKE and then:

= "ASTERISK" & Parameters!YOURPARAMETER.Value & "ASTERISK"

Upvotes: 0

user2851776
user2851776

Reputation: 19

Use this will solve ur issue

="" & Parametername.value & ""

Upvotes: 0

Tom De Cort
Tom De Cort

Reputation: 211

This is how I did it:

  1. Create a report parameter (data type text) named MyFilter
  2. Create a DataSet ReportData with an expression for the source query, something like:

="SELECT FilteredField, FieldValue FROM DataTable " & IIF(Parameters!MyFilter.Value.ToString() = "", "", "WHERE FilteredField LIKE '%" & REPLACE(REPLACE(Parameters!MyFilter.Value.ToString(),"*","%"),"?","_") & "%'")

As you can see, I have a basic SELECT statement which I concatenate with an empty string if the value for MyFilter is an empty string. If a value is filled in, I add a WHERE clause in which I replace the "*" by "%" and "?" by "_"

This is just a simplified version of what we actually did, allowing only "?" and "*" to be used as wildcards, but it works fine for our users.

Upvotes: 1

Ian Preston
Ian Preston

Reputation: 39566

Say I have a very simple self-contained query in the report:

with val as
(
 select val = 'ABC'
 union all select 'DEF'
 union all select '1ABC3'
)
select *
from val
where val like @Param

I also have a parameter called Param in the report.

By default, even though I have like in the query, there are no wildcards so only exact matches will be returned:

enter image description here

If we look at the Dataset Properties, we can update the parameter being passed to add wildcards. By default it looks like this:

enter image description here

Change the Parameter Value expression to:

="%" & Parameters!Param.Value & "%"

Now the query text will be using a parameter with wildcards, so partial matches are returning data in the report:

enter image description here

Alternative method

Actually, thinking about this, perhaps an easier way to achieve the above is to do something like this in the report query text:

SELECT *
FROM Table1
WHERE Table1.Name = LIKE '%' + @Name + '%'

i.e. Just concatenate the wildcards directly to the parameter string. This works identically to the first option.

Upvotes: 4

Related Questions