In summary, I am expecting the report dataset filter to be appliedto the base SQL when it is running on the database but I believe this is not the case in my situation.In detail, I have built a report using BIDS 2008 and my data source is a SQL 2008 database.I have a report dataset filter with a start and end date.I have deployed the report to the report server, and execute it.When I look at thegenerated SQL (e.g. via theSSMS Activity Monitor, Recent Expensive Queries, Edit Query Text), I noticed that thewhere clause does not contain my dataset filters. I was expecting the generated SQL to have these filters applied.My question is - when are the dataset filters applied to the SQL?I hope I have missed some setting becuase it would be disappointing if SSRS could not apply filtering at the server level to facilitate performance.regardsJames Mathews

does the where clause in your dataset have something likeWhere @Param1 and @Param2And those parameters are being passed from the report?Do you have caching enabled on the report server?If you execute the report with different parameters multiple times, are you seeing new queries being generated via the Activity Monitor?Garth H MCTS: SQL 2008 BI http://bitinkering.spaces.live.com/

Thanks GarthH_BIThe parameters are created using the dataset filters property expression dialog screen... using the btween operator.More to the point, the SQL query generatedand viewed when running the report via the Activity Monitor,does notinclude the parametersin the WHERE clause.I don't believe that this hasanything todo with caching being enabled... it is more of an issue ofwhen the WHERE clause is being applied to the generated SQL. At the moment, my report appears tocompile a base SQL statement, submit it to the report server etc, and then return an unfiltered dataset, then apply the filteras a 'later' step of the process. My issue is that the WHERE clause is not seemingly appended to the base SQL which negatively affects performance.regards

This is expected if you build a report from a non-model based data source, because Reporting Services executes exactly the query you specified. Filters defined in the RDL are then executed inside Reporting Services.Model-based data sources are the only cases where Reporting Services automatically generates queries.HTH,RobertRobert Bruckner http://blogs.msdn.com/robertbruckner This posting is provided "AS IS" with no warranties, and confers no rights.

Thank you RobertJust to clarify my understanding, thedataset I have created is based on a deployed report model so I assume it falls into your description of a model-based data source.My question is, when is the dataset filter (the where clause) applied?regards

After further testing, I have discovered that applying a filter to the query of the dataset, instead of adataset property, will add the where clause to the generated SQL. This is a subtle change butimportant in terms of ensuring the generated SQL will return the intended result set.