Wildcard search in dataset parameters

Question

I have a standard report that uses a Program name as a dataset paramter in multiple reports. Is there a way, in SSRS, to write and expression that uses a wildcard search.

Right now, the dataset Parameter name is @P.P.U.H. Program .
In Parameter value I am trying to do something like CCPA%.

But I know this would need to be an expression. Can anyone give me any help? I really can't change the underlying query. The issue is I have one report that needs to read a program that has CCPA or CCPAP. I thought a wildcard search would solve this.

Answer 1

Hi @jmv ,thanks for your reply.However I couldn't fully understand your meaning. Do you mean that you couldn't modify the dataset query?

I tried another way to add a filter but I'm not sure if it meet your needs. Below is the steps.

We already have the parameter @P.P.U.H. Program .

And then add a filter to the dataset.

Expression:=IIF(InStr(Fields!program.Value,Parameters!program.Value) OR Parameters!program.Value = "*", TRUE, FALSE)

Setting like this

I set the parameter @P.P.U.H. Program default value to "*" so the report will grab everything by default.

If this way doesn't meet your requirement, you may describe your needs with more details. Or do you mean that you just want to add an expression value to the parameter to do the wildcard search?

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @jmv ,

You can concatenate a wildcard charater in your query for the parameter:

where Program like ('%' + @P.P.U.H. Program + '%')

And then you may enter CCPA% for search.

Here is my test.

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Thank you so much for your detailed reply. I really appreciate that. There are some instances where a wildcard search wouldn't be good for all the @Programs. Is there a way to write a single expression for just this report in particular? I may have mentioned, I use this single query to run multiple reports. Again, your input is greatly appreciated.