SSRS Multi value parameters - appropriate layer for implmentation of the filter

Question

When using multivalue parameters in sql reporting services is it more appropriate to implement the list filter using a filter on the dataset itself, the data region control or change the actual query that drives the dataset?

SSRS will support any scenario, so then I ask, is there a reason beyond the obvious of why this should be done at one level over another?

It makes sense to me that modifying the query itself and asking the RDBMS to handle the filtering would be most efficient but maybe I am missing something with respect to how the SSRS Data Processing Extension may handle this scenario?

Answer 1

Hi,

You are correct. The way to go is to pass the parameters through to the database engine.

Reporting Services should only be ideally used to render content. The less data that you need to pass back to the client web browser, the faster the report will render.

You may find my answer to a similar post regarding using mulit-value parameters to be of use.

http://stackoverflow.com/questions/512105/passing-multiple-values-for-a-single-parmeter-in-reporting-services/512300#512300

Hope this helps but please feel free to pose any further questions you may have.

Cheers, John

Answer 2

Using table-valued UDF is a good approach, but there is still one issue - in case if this function is called in many places of query, and even inside inner select, there can be performance problem. You can resolve this issue using table variable (or temp table eather):

DECLARE @Param (Value INT)
INSERT INTO @Param (Value) 
SELECT Param FROM dbo.fn_MVParam(@sParameterString,',')
...
where someColumn IN(SELECT Value FROM @Param)

so function will be called only once.

Othe thing, if you don't use stored procedure, but embedded SQL query instead, you can just put MVP into query: ... where someColumn IN(@Param) ...

Answer 3

Use the RDBMS to do the main filtering

SSRS provides filtering for the purposes on data driven display and/or dynamic display. Especially useful for sub reports etc