Filters with large number of values

I have web-based reports using SQL server 2005 with filters called location
and suppliers and the number of locations and suppliers are more than 200
each. When the report is rendered with this filters, selection of locations
and suppliers is taking long time on the browsers and it is not easy to
search for the required options in the drop down with checkboxes,
1. Can we modify the parameters display area to show a file-open image
clicking on which opens a popup window for users to select the filter
values?
2. Can we improve the performance while user selects the filter values?
3. Is there any other way to handle this type of large filters (more than
200 filter options) for the reports?
Thanks and Regards,
SridharOn Jun 6, 12:13 am, "Sridhar" <dsrid...@.translogicsys.com> wrote:
> I have web-based reports using SQL server 2005 with filters called location
> and suppliers and the number of locations and suppliers are more than 200
> each. When the report is rendered with this filters, selection of locations
> and suppliers is taking long time on the browsers and it is not easy to
> search for the required options in the drop down with checkboxes,
> 1. Can we modify the parameters display area to show a file-open image
> clicking on which opens a popup window for users to select the filter
> values?
> 2. Can we improve the performance while user selects the filter values?
> 3. Is there any other way to handle this type of large filters (more than
> 200 filter options) for the reports?
> Thanks and Regards,
> Sridhar
In response to number 1, I'm afraid not. In response to number 2, if
I'm understanding you correctly, the more filters you have the more
the performance will be affected. To improve performance, I would
suggest running the report query/stored procedure through the Database
Engine Tuning Advisor to see if there are any indexes that can be
implemented/removed that would improve the performance of the query.
Then, make sure that you are doing as much of the processing in the
query/stored procedure as possible and not in the report. After that,
I would say to limit the number of filters you have on the report.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant