Increase limit of elements in Detail filter

[dssd]

I have a multi select prompt with 7000 elements. The report throws an error when i select more than 5000 elements. I believe its a constraint from SQL side. Is there a way around it?

[S2000]

I see two options depending on how it's being used.

Make it an optional filter.
Set it to include everything except what is selected.

I can't think of any real world example where a user would want to select 6000 elements from a list of 7000, it would take forever to make the selections.

[Lynn]

What is the error and what database are you using?

I know of one situation where very large lists of filter values had to be supported. It was for the pharmaceutical industry where an analyst might be querying a population of several thousand specific physicians. There was no single attribute that could be used to filter and get the entire population so listing every ID was the only option. Often the list was the result of some other analysis or provided by some other process, such as input from field sales reps.

It was a common requirement and going through a pick list or using a search prompt to select hundreds or thousands of doctors was impractical.

The solution was to support upload of user provided filter data via the ETL process. The Framework model included this table and a join between it and the physician table was established.

The layout included three columns. The first was the user ID so that multiple users could be supported and security on the data could be modeled. The second column was a name for the analysis that the user provided. This allowed them to have multiple sets of analyses active. The third column was the physician identifier.

All the user needed to do was identify the name of the analysis they wanted to employ so that a join effectively filtered the result set instead of a massive where clause.

Seemed like a reasonable approach to me for the situation, but it is a pretty specific scenario that may or may not fit with your requirements.

[dssd]

I am thikning of updating the database with the values selected in the prompt using a stored procedure. Then combine that table as you said with the other physician table to get my result. Is it feasible?

I wonder if the would execute in sequence? Also, can we update through report studio using a stored procedure or has it to be through event studio

[barrysaab]

The other way to group the values such as A-D as 1 or something and use cascading prompt.Thanks

[Lynn]

I am thikning of updating the database with the values selected in the prompt using a stored procedure. Then combine that table as you said with the other physician table to get my result. Is it feasible?

I wonder if the would execute in sequence? Also, can we update through report studio using a stored procedure or has it to be through event studio

I don't see how this solves your problem. They would still have to select all those things from a prompt which will be unweildy and tedious.