CODEWITHSUNDEEP
reporting-servicesssrs-2012reportbuilder3.0
Jorgen V
Jorgen V

Reputation: 313

Multiple value parameter ssrs performance issues

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.

Upvotes: 1

Views: 764

Answers (2)

Bigroy
Bigroy

Reputation: 1

An approach which can give significant speed-up in reports with a large number (5+) parameters is as follows:

    select * into #mytable
    from (select 
              field1,
              field2,
              field3,
              ...
          from
              table1
          join
          join
          more joins
          where
              (optional hard-coded criteria)
         ) as x

    select * from #mytable
    where
        field1 in (@param1)
        and field2 in (@param2)
        and field3 in (@param3)
        ...

    drop table #mytable

Upvotes: 0

R. Richards
R. Richards

Reputation: 25161

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!

Upvotes: 3

Related Questions