Is it possible to create a report that will preset the user with a number of different filter parameters and allow them to use one or more. In other words if I have filters for ID#, Name and Address and the user wants to search on ID# only they can. If they want to filter on just Name they can but they can also filter on name and address or address and ID#. I want them to be able to filter on a number of different parameters or only one.
You can present all the possible parameters to the user, with each parameter having an additional option of "Omit", or "All" or something similar that indicates to them that that specific parameter is not filtering out any data. Then your query has to handle that additional option as indicative of excluding that from the where clause.
This type of query where your filtering condition is dynamic is referred to as "catch-all query" or "dynamic search condition" You can find efficient ways to write such queries on the web.
Thanks, I will look into it some more. I think part of the problem is I wasn't using the correct terminology in my searches so I will use your suggestion.
In a case like that I create a stored procedure, default the parameters to null then in the where clause:
so one of my parameters is @col2 and I'm using it as criteria against col2;
WHERE col2 like '%'+case when @col2 is null then col2 end+'%';in the procedure.
You can do this with all the columns in one procedure.