Hi There.
I have a Multi-valued parameter that is a string type and it freaks out when a do a select all from my drop down list. I suspect it has something to do with size as it works great if a pick a reasonable amount of items. My parameter list is populated by 1463 items of 12 characters each. Is there a threshold I should have to worry about
Thanks, Mike
Well I'm not sure exactly how "it freaks" manifests itself <s>. But for one thing, if you have applied SP1, you should find that "Select All" is not available for mv params any more. See
http://blogs.msdn.com/bwelcker/archive/2006/08/14/700189.aspx
This may be revisited with more granularity to the choices later.
Meanwhile, if you do want a "Select All" functionality, you can put in a boolean param that says something like "All" or "By Selection" and then build what is sent to the server as the query based on what is in that radio button. Or, even simpler, change the prompt to read something like "Limit to these values:" so that selecting none is interpreted by your code as the same as meaning "All".
Just use an IIF() as you build up that part of the SQL string to leave out this particular part of your WHERE clause if they haven't selected anything, or if the boolean is set to "All".
>L<
Hi Lisa.
By "freaks out", I mean that when I select too many, my parameter window goes blank and I get erroneous results. If I select 1 less item than the "freaks out" stage, it works fine.
Any ideas
Mike
Yes, I have ideas, that is why I wrote what I wrote in my post <s>. Please read and let me know if it makes sense to you... or if not, what part doesn't.
>L<
Hi Lisa.
The reason I did not respond directly to your suggestions is that you do not have to select all to have the problem. If you select somewhere in the 40% range of the items or more, that is where the problem occurs. What I was really looking for is to see if anyone has had problems with large string lists.
Thanks, Mike
The part of the message I was hoping you would respond to, Michael, was the part about updating to the SP1 <s>. Which you haven't done, otherwise you would not see the Select All option... Sorry I wasn't more explicit.
Look: I think, given some of the posts I've seen, that the reason they pulled Select All was probably performance-related or large-set related. I'm hoping that whatever they changed might also have some bearing on the rest of your "freak" issue, even though I haven't seen that happen. My suggestion is to patch.
I also gave you a workaround for Select All (or hope I did) so that you would not be put off patching because this option is missing.
Once you have patched, it would be fun to talk about threshholds, but it's kind of irrelevant until you do.
As a secondary issue, we could also talk about the threshhold of a user's patience when it comes to selecting items from a list of ~1400 items! I don't know for a fact that I haven't seen your "freak" issue because I've patched. It could well be that I have not seen it because I would not make that kind of interactive list available There might be a better UI decision you could make (perhaps with drillthrough or something like that).
Regards,
>L<
Hi Lisa.
Thanks for your input. FYI - They brought back the select all for SP2. Also, I do understand that 1400 items is yecch. I will probably look into cascading parameters.
Thanks, Mike
If you are using SP2 then I take back what I said about your needing to patch! I had a braino about this... I apologize, it's right in front of me, too...
If your "freak" is still there in SP2, then they put it back in without fixing the underlying performance issues, I guess. Which they have said is the reason they took it out in the first place.
Brian W's blog (https://blogs.msdn.com/bwelcker/) does have an interesting workaround with an internal param, which you might be able to use with a slight adaptaption, though, if you really must have huge param lists but need to set a safety threshhold. Here is how I imagine this working for you:
This is, in fact, what I thought they would be doing as ancillary work when they made the change in SP1. It is what I would have done, if I had discovered that the thing was unsafe -- taking out Select All didn't really solve the underlying issue. I would have set some sort of a threshold beyond which the LOV would be ignored, published it, and treated it as a necessary fix. I imagine they are looking into doing something like this, post SP2, where they make <N> tunable, but I would have expected them to do it without the tunability right away.
>L<