Dynamic Filtering By User At Runtime?

I have a project where there are millions of records. For sake of example, let's use the White Pages.

One of the reports my client wants generated is a contact list. The contact list contains name, address, city, state, zip, phone, and several other key data elements.

Naturally, the client wants to be able to sort on each of those elements (easy to do.)

They also want to be able to filter the resultset the report shows, dynamically, at runtime (not so easy, I'm finding).

So, for example, one person may want to run the report and have the results only show people in PA. Another person may run a report and show all the people who are in Boston, MA. Yet another person may want everyone with the last name of "Smith".

Ultimately, the client wants to be able to filter results by virtually every day field displayed on the report (and some fields which are not!)

I've dug around the documentation for Reporting Services, and I readily admit I am a newbie. I cannot see any way to dynamically filter the resultset used to generate the report, other than using "Report Parameters".

The problem with Report Parameters, from what I can tell, is they also require me to have scalar expressions in a WHERE clause in the dataset. So, for example, if I wanted to let people filter on lastname, I would need a "WHERE LASTNAME LIKE @LastNameReportParameter" -- which works ok, as long as the parameter isn't null, of course, so I really have to do something like 'WHERE LastName LIKE ISNULL(@LastNameReportParameter, '%')"

With, literally, a dozen different fields on this report, I don't see where that is a real option -- I literally have to program a WHERE clause that has a dozen elements, some of which are numeric (not alpha), and thus LIKE doesn't work (AGE = 37) unless I get into completed gyrations (WHERE ISNULL(CAST(AGE AS VARCHAR), '') LIKE ISNULL(@AgeParameter, '%')

This seems to be incredibly complicated, and I cannot imagine there isn't an easier way to allow users to filter the resultsets on the reports generated by Report Server. Is there something I am missing?

Also, it has to be something I can address programatically, since the client wants a fairly 'seamless' front end (in C#) where users can simply click a radio button (to select a field to filter by) and type in a value (to filter on) prior to displaying the report in a reportviewer control.

Is there a way to dynamically program a filter expression and pass it to the report server, something akin to a filter expression on a BindingSource?


Any thoughts on this matter would be helpful, or pointers to online documentation, as I'm sort of out of ideas and cannot find anything in the docs. Thanks!