March 27, 2012 at 4:08 am
I just experienced something a bit odd and was hoping to get an explanation.
We have sites and sites have items. It's a one to Many relationship so a site has many items but a given item belongs to only one site. I was designing a report where I wanted the user to pick a site, then pick an item from a dropdown. No problem there, just make the second parms query reference the value of the first parm and SSRS handles the rest for you. Lovely.
The thing I spotted when I came to test, though, was that some of the items I was expecting to see for a particular site weren't appearing in the list. They appear correctly if I query SQLServer directly in SSMS.
I think what's happening (but would like to get a confirmation) is that the number of items that a site can 'own' is too high to sensibly display in a drop-down. There were somewhere over 67,000 items being returned in SSMS. Is SSRS only part-populating the drop down?
Just to pre-empt some possible answers: Yes I know 67,000 record is too many to expect a user to select from using a dropdown. No I didn't realise there were going to be that many when I first decided to use a dropdown. Yes I've reverted to using a straight textbox for the parm. And Yes the users will be familiar enough with the item numbers to type them straight in without the aid of a drop down.
What I'm interested in is whether there is a theoretical upper limit to what SSRS will allow into a drop-down.
March 28, 2012 at 5:31 am
Here are some ideas of other people about the problem:
It's possible SSRS stores the values somewhere and that there is a 4000 character limit.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 28, 2012 at 7:46 am
That would make sense. I've noticed SSRS does tend to hold lists as delimited strings so a overall character limit is likely. It's interesting that the posters in that link were getting error messages though becuase I didn't get one - just some of the options weren't there.
Realistically, if I'm really putting over 4000 chars in a drop down I'm probably overloading them with choice but it's always nice to know the limitations.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply