How can the dynamic filtering functionality of Access/Excel be implemented in SSRS?
We have an SSRS report with a dataset that calls a stored procedure. This stored procedure cannot be changed, and the report parameters cannot be changed. We would like the user to be able to filter the report dynamically in the same sort of way that filtering is done when viewing an Access table or an Excel spreadsheet. For example, to see only the data for a particular client, we would like the user to be able to click on the ClientNumber column header, see a list of possible values that are contained in the data, and select one or more values, after which the report will only display those rows with the selected ClientNumber(s). Just to re-iterate the limitations at the beginning of this post: the stored procedure that this report calls cannot be changed, and no new report parameters can be added to the report. This is a very basic reporting requirement, and I am sure most SSRS developers out there have come across this problem. How can this be implemented?
Use a parameter in the report and reference that value from a filter on the table object that you want filtered. The expression will be something like '=Parameters!paramName.value' but you can select it through the expression GUI. This [
http://www.mssqltips.com/tip.asp?tip=1897] shows you how to add the filter, etc. Its a shame you cant alter the procedure as every time someone runs the report with a different filter value it will still be calling all the data from the database. This means the data server and the network will be working harder than is necessary. [Edit] @WilliamD points our no report parameters can be added. Can you create a linked report that could have parameters? :