So I'm making a report in Reportbuilder 2012 3.0. One of my parameters has thousands of values that I can select separately or all at once. Problem is that when I want to select it takes an immensely long time to load. Also when I choose a few it takes an incredible long time to load the report. Is there a way to make the performance better? I really need all of them in the list.
Think about using cascading parameters to narrow down the values in the parameter list the end user chooses from. If the parameter data you are presenting has a category, then create a parameter with a list of categories that is shown to the end user before the list you refer to. When the end user chooses a category, the next parameter list if filtered based on that choice.
-- Category parameter SQL
SELECT DISTINCT category FROM tblParams;
-- Main parameter SQL
SELECT code, name FROM tblParams WHERE category = @Category;
If your parameter data doesn’t have a category, see if there is anything else that can be used to filter things down a bit. A territory, region, representative name, first letter in the name… something.
Just make sure that the parameter that you want to filter other parameters on is listed first in the parameters list in the report designer.
Good Luck!