Report Designer Dynamic Filter

I have a requirement to add the ability to filter a report by partial last name, at the report level (i.e. not at the database server). In case someone else needs to work out how to do this, here is what I did.

Add a Parameter to the report. In my case it is named LastName, is non-queried, allows Null or Blank, and has a default of null.

Add a filter to the dataset that contains the field to be searched. Set its Expression to:
=IIF(Parameters!LastName.Value = System.DBNull.Value OR Parameters!LastName.Value = "",
Fields!LastName.Value, Nothing) ,
its operator to "="
and its value to =IIF(Parameters!LastName.Value = Nothing OR Parameters!LastName.Value = "", Nothing, Parameters!LastName.Value & "*")

voila!

Helen

I'm not sure I fully understand. What exactly does this do?

Would this have the same effect as using a query with a parameter for lastname (@.lastname) where lastname LIKE @.lastname + "%"?

|||

It allows me to make entering a value for the parameter optional. If the user does not want to filter by last name, my filter expression still has to work. I got it to work by using an expression for both the filter and its value, and returning Nothing for both when the value is null or empty.

When using an expression for the filter, the LIKE option for the comparator is disabled, so I had to use = as the operator.