0

I have a SSRS report which has a multiselect dropdownlist parameter which loads data from database.

The problem is, I have to load 130000 records in the parameter dropdown and it takes so much time to load the dropdown. But when I check my query which is a simple select query

eg: 

SELECT Id, Name
    FROM Table1

it executes within 3 secs and retreive data. I cannot reduce the ammount of records that needs to be loaded in to the parameter dropdown.

Is there any way to do pagination or that kind of thing to improve the performance.

tarzanbappa
  • 4,930
  • 22
  • 75
  • 117
  • 5
    There's no potential pagination of drop down parameters. How would a drop down parameter containing 130,000 records be in any way usable by the person running the report, if they wanted to unselect one of the options? Frankly this sounds like a report in need of a redesign- maybe a combination of inputting text search string for what you want to select (or unselect), or another similarly creative option. – Rich Jun 14 '17 at 07:16
  • 4
    Is there no way you could break it down to cascading params with the final param being this one but filtered by the first couple. Ie. Filter Sales Dept --> Sales Cate --> Sales item. – Snowlockk Jun 14 '17 at 08:10
  • 3
    *I cannot reduce the ammount of records that needs to be loaded in to the parameter dropdown.* ***WHY NOT?*** – iamdave Jun 14 '17 at 09:07
  • 3
    Perhaps if you said what the report is designed to do and why you would need a user control with 130,000 entries then somebody might be able to suggest a different approach. – Alan Schofield Jun 14 '17 at 10:05
  • It sounds like the problem isnt the query its that each item in the dropdown needs to be dynamically loaded into the DOM at runtime. I agree with Rich, a redesign is definitely required, from a usability point of view its ridiculous to expect a user to select from a range of 130,000 options. – Master Yoda Jun 19 '17 at 11:43

1 Answers1

-1

You can add input parameter before drop down and then based on that test given by input parameter filter next dropdown.

Khati Lx
  • 31
  • 5