May 2, 2013 at 9:31 am
So I have a VERY simple report returning three columns from one table. It has four report filters:
Transaction ID
User Name
Start Date
End Date
The first filter, Transaction ID, would ideally return a list of ALL Transaction IDs in the table. The problem is there are more than 700,000 rows. This drags the report performance to its knees. Right now, it only returns the most recent 1000 filters.
So is there a way to return all 700,000 rows and maintain performance here given that this is SSRS 2005? I don't think cascading report filters are an option here (I think that is SSRS 2008 or newer). In other words, I don't think I can filter the results of the Transaction ID filter by the other three filters.
Any suggestions?
Thanks,
DH
May 2, 2013 at 12:48 pm
My .02c
From a user perspective, who wants to scroll thru 700,000 id's?
especially because the ssrs drop down does not allow type ahead, and the list is small
From a technical perspective, no platform, (asp.net, SSRS) will cleanly handle 700,000 items in a dropdownlist.
Your going to have to get creative.
Output the id's to a table, with other information that will help the user.
Then the report will drilldown when a user selects one of the id's?
Not sure what your trying to accomplish.
May 2, 2013 at 1:20 pm
I agree, 700,000 rows in not feasible, thus why I am looking for options. Sadly SSRS 2005 is pretty limited in that regard.
The Transaction ID is one of the criteria the end user can use to identify the Transaction in question. They will sometimes look up very old Transaction IDs at that.
For the moment, they can overcome this by using the Start and End Dates.
We are in the process of upgrading to SSRS 2012, so I may not bother any further with this. Thanks for the .02 cents!
DH
May 2, 2013 at 1:38 pm
how about changing the drop down into an optional text field...that way if the user KNOWS the ID, they can just type it in;
i'm no SSRS expert, but i do a lot of web stuff; my rule of thumb is > 50 values really needs a different selection criteria than a SELECT / drop down list.
I usually have a search screen that allows them to search on various criteria, and then provide links to the details based on their search...and i typically limit it to 100 records at a time at the most.
Lowell
May 2, 2013 at 1:48 pm
darkhelmutis (5/2/2013)
I agree, 700,000 rows in not feasible, thus why I am looking for options. Sadly SSRS 2005 is pretty limited in that regard.The Transaction ID is one of the criteria the end user can use to identify the Transaction in question. They will sometimes look up very old Transaction IDs at that.
For the moment, they can overcome this by using the Start and End Dates.
We are in the process of upgrading to SSRS 2012, so I may not bother any further with this. Thanks for the .02 cents!
DH
Then allow them to run a report that searches for TransactionID's between dates.
which outputs the id's to a data table then link to another report that calls your report by ID.
and / or allow them to just type it in as Lowell suggests.
May 2, 2013 at 2:20 pm
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
May 3, 2013 at 9:29 am
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
May 3, 2013 at 11:03 am
Wolf-
You are exactly correct. I generally never create catch-all queries and opt for the dynamic sql implementation every time. Gail Shaw did a great write up on the problems with the catch-alls. Following Erland Sommarskog write up on the right ways to do dynamic SQL also helps.
I can't wait till I can put this SSRS 2005 implementation in the grave and move onto SSRS 2012.
DH
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply