February 3, 2022 at 8:04 pm
Hello,
We have a client that would like to limit the user to only selecting 10 items in a multi-select parameter that could have 100s of items to chose from.
I did see this article https://social.msdn.microsoft.com/Forums/office/en-US/7a145d94-bc6d-4232-a256-521e622b653b/limit-number-of-values-selected-in-a-multiselect-parameter
It mentions using javascript for a pop up but I have never done anything of the sort.
Has anyone had any success doing this?
February 4, 2022 at 8:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
February 8, 2022 at 8:55 pm
Anyone?
February 8, 2022 at 9:11 pm
Since you got no replies, my guess is nobody here has tried it. My suggestion would be to try it out and see if it works like how you expect. You even found an article on it.
I've never had a need for limiting the number of selections in a dropdown in a report before and personally can't see much benefit except maybe for performance, but in that case I would much rather work to improve performance on the query (indexes perhaps, or maybe the query can be tuned).
An alternate approach would be to let them pick more than 10, but have the SQL look at the number of values that are being passed in and if it exceeds 10, it selects an error message rather than selecting the regular query.
But again, I've not done this before. What I would do is try it on a test system and see if it works rather than posting on a random forum HOPING someone else is willing to take the time and the risk for you. Whoever created that post obviously tried it and it worked, so take their work and make it suit your needs. Worst case, you kill the test site and need to rebuild it.
I think this could probably be handled without the need for Javascript pretty easily based on what they have on that link. You have a hidden parameter to count the number of things selected in the dropdown. If it is larger than 10, your SQL selects an error message instead of the regular query. No javascript required!
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 8, 2022 at 9:33 pm
Hi Brian,
Thank you for the reply. I am unsure of how or where to even enter the javascript so that it can allow for the pop up
February 8, 2022 at 10:05 pm
Personally, I wouldn't worry about the javascript part of it. I would handle it in the TSQL. Have the TSQL be something like
IF (TooManySelected > 10)
BEGIN
SELECT 'Too many things selected in the dropdown. Please select 10 or fewer and try again' AS ERROR
END
ELSE
BEGIN
--INSERT YOUR QUERY HERE
END
The reason I'd skip over the javascript side is it feels like a lot of overhead for little benefit. May need to tweak the query so it works for you like adding more columns in the initial select so the metadata isn't changing while the query is being processed and thus not able to see all columns, but this gives you an idea on what to go with.
At least that is how I'd handle it. Otherwise I feel this had a lot of overhead (javascript) for little benefit.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 9, 2022 at 2:46 pm
I definitely agree and appreciate the answer, we're under the gun by the client. Issue really is there is about 10 parameters and the one we need locked down is 3rd in the list so they will go through everything and chose all the other parameters and then get the error after running the report. Client doesn't want that. Believe me I am pushing for the simple solution LOL
February 9, 2022 at 3:28 pm
I am pretty sure Javascript is going to give you the same limitation. Pretty sure that Javascript will need a trigger to tell them to pick 10 or fewer options in the dropdown.
My opinion - I would tell them that is not possible in SSRS and that the suggestion I gave is the "best" option available, especially given the time constraints.
My option is nice because it doesn't blow out their selections after picking 10+, they just have to try again after picking fewer options and the error should pop-up nearly instantly after they click on "view report".
If you are needing something else , I expect you are going to need more help than what a forum like this would offer both in terms of development and support. Even if you DO get it working how they want, can you support it going forward if updates to the browser or java cause your javascript to break?
One alternate way around it would be to handle it in a .NET app (for example) that is really just a web-wrapper for the SSRS report BUT have the .NET app handle the logic around selecting things from the dropdown. Alternately, design your own ASP.NET page that would handle the parameters and then would redirect to the proper SSRS page. Again though, both of these options require that you have someone with ASP.NET or .NET on your team to build that up. And your client may not want that either.
If the client gave you a short timeline and you can't meet all of the requirements, you shouldn't have signed up for the work. You shouldn't agree to do the work until you both agree on the requirements. I would reach out to the client as they are hopefully reasonable and telling them that it is a limitation of the technology and give them options, they are likely to be reasonable with you.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply