question

DanekaOHagan-6794 avatar image
0 Votes"
DanekaOHagan-6794 asked OlafHelper-2800 answered

Is there a MAX amount of fields you can choose within a multi parameter

I have a filter which is populated. This has about 6000 fields which a user can choose from. When I run the filter for a couple it works however when I 'Select All' if returns no data.

Is there a MAX amount of fields SSRS allows or is there a way I can work around this? There may be a scenario where the user will have to see all so I would need this feature in. I also would not be able decrease the size of the list.

sql-server-reporting-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered

Hi @DanekaOHagan-6794 ,
Did you mean that there are 6000 values in your parameter drop-down list? As far as I know, there is no limit to the number of values.
I think you may need to optimize the report to avoid using 6000 Labels. Too many drop-down parameters not only affect the presentation time of the report, but may also lead to a bad user experience. Make good use of grouping and document map to effectively avoid this phenomenon.
If I misunderstand what you mean, please feel free to correct me.
Best Regards,
Joy


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

I guess in your SQL statement you use an IN operator with an parameter for the multi selected values.
And there is more the main problem with such an amount of selected parameters.
See IN (Transact-SQL) => Remarks:
Explicitly including an extremely large number of values (many thousands of values separated by commas) within the parentheses, in an IN clause can consume resources and return errors 8623 or 8632

See it's not predictable if or when an error occurs with the SQL query.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.