Generic Report Filter Screen
-
I'm trying to build a generic filter screen for a set of reports with a similar purpose off the same database. Most reports include a CallData table, with filters like DateFrom, DateTo, DurationFrom, DurationTo, etc. So, I use controls for these to build a section of the where clause. When I run the report, I read the query text, build the where clause for the CallRecords filters, and replace a token in the query with this filter text. This works well with most reports, as most only filter on CallRecords. Some also filter on, say, "Employees.EmployeeId in (1,2,3)", which I also built dynamically using checkbox lists presented to the user in the filter screen. My issue here is how to decide which criteria to insert into which report query. I could use attributes on the report classes, but I think that's clumsy. My currently winning candidate solution is to have a separate token for each filter in the report, so if there is no /*@@Employees@@*/ token in the report query, no employees criteria will be inserted. Any suggestions of other means of doing this?
-
I'm trying to build a generic filter screen for a set of reports with a similar purpose off the same database. Most reports include a CallData table, with filters like DateFrom, DateTo, DurationFrom, DurationTo, etc. So, I use controls for these to build a section of the where clause. When I run the report, I read the query text, build the where clause for the CallRecords filters, and replace a token in the query with this filter text. This works well with most reports, as most only filter on CallRecords. Some also filter on, say, "Employees.EmployeeId in (1,2,3)", which I also built dynamically using checkbox lists presented to the user in the filter screen. My issue here is how to decide which criteria to insert into which report query. I could use attributes on the report classes, but I think that's clumsy. My currently winning candidate solution is to have a separate token for each filter in the report, so if there is no /*@@Employees@@*/ token in the report query, no employees criteria will be inserted. Any suggestions of other means of doing this?
If you are using a stored proc this may help. Where @SetID is the criteria from the client, -1 can be any value. There is a neater method floating around somewhere but I have not changed mine for a decade or more. Search for 'Dynamic where clause'.
AND (ISNULL(@SetID,-1) = -1 OR FileSetID = @SetID)
Also the EmployeeID in (@List) will not work, you need a Split function that returns a resultset and then you can use an inner join or where in method.