I have filtering implemented at tablix level in an ssrs report. The tablix is using a dataset which is getting lots of records from database using a stored procedure. The report has parameters whose values are used to filter the tablix data. First time when the report executes it gets all the data from stored procedure and bind it to tablix with all the details as parameters default value is set to select all. I want to know when user enters parameter values and click on view report , does the report executes the procedure again , get the entire set of data and then filter based on input parameters? Or the ssrs report is smart enough to know that already the data which was fetched the first time will be used to filter the data in the tablix
1 Answers
First let's talk about the difference between query parameters and report filters. Parameters are passed into the query so that it can run faster and return targeted results. Filters are applied after the fact so the full query has to run and then the report has to go through the records and check criteria row-by-row.
In addition to those options, SSRS offers caching. This allows you to save the query results so that the query is only re-run when needed. This is configured on the report server, not in the report properties.
The best optimization will vary by report. As a general rule it's best to pass parameters into the query/procedure and just get the data you need. If that is too slow, let the query get all the data, cache it, and just use filters at the report level.
