darkhelmutis (5/2/2013)
I figured out that it is possible to use cascading filters (i.e. dependant filters) in SSRS 2005:This is how I did it:
- Updated the SP that retrieved the Transaction IDs to include @start_date and @end_date parameters, both set to a default value of NULL
- Updated the dataset based on that SP in the report to now include two parameters whose values are retrieved from the report filter paraters Start Date and End Date
- Reordered the report filters so that the Start Date and End Date filters occur first
Voila, now the Transaction ID filter only displays the Transaction IDs that apply to the time frame. Yes there is the annoying screen refresh. Yes it is possible that someone could specify a large date range that returns a lot of records. I will consider strategies to deal with the later.
Next step will be to convert the SP to use dynamic SQL with an explicit parameter list so that I can have a variable predicate and more stable execution plans!
Thanks for the feedback guys!
DH
Glad you figured out the cascading report parameters. That's exactly how I used to do it when I spent most of my time developing reports in SSRS 2005. And yes, the screen refresh after selecting the first parameter values is annoying!
I'm curious about what you mean by "convert[ing] the SP to use dynamic SQL with an explicit parameter list so that I can have a variable predicate and more stable execution plans". Are you saying you want to allow the user to select values for only some of the parameters and then filter the results using only those parameters (i.e., a catch-all query)? If so, you're right on that dynamic SQL is the way to go - this blog post gives an example of how to do it. Good luck, and open another thread here if you run into trouble.
Jason Wolfkill