tag:blogger.com,1999:blog-8413638223026049990.post3635592485748519814..comments2023-01-11T04:09:04.172-05:00Comments on <center></center>: 2 Shortcomings of Multi Valued Parameters in SSRSBob Pearsonhttp://www.blogger.com/profile/16528166535649336177noreply@blogger.comBlogger8125tag:blogger.com,1999:blog-8413638223026049990.post-75675034017194565922009-03-31T18:57:00.000-04:002009-03-31T18:57:00.000-04:00My solution was similar but required only two para...My solution was similar but required only two parameters and you don't need to do any counts. <BR/><BR/>In your where statement add this:<BR/><BR/>Where (Contact in (@Contacts) or @AllContacts = '*')<BR/><BR/>Then in the report parameters set @Contacts as your multi-value parameter. If you give your users default values to choose from in your multi-value list, add a * as the first option. Otherwise the user can just type * in.<BR/><BR/>Set @AllContacts to a Hidden parameter that is NOT multi-value and set the default value as Non-queried and =Parameters!Contacts.Value(0)<BR/><BR/>Then if the user selects/types * as the selection for @Contacts it will set the @AllContacts = * which will then evaluate the where statement such that they will get all data.<BR/><BR/>Thoughts?NJnoreply@blogger.comtag:blogger.com,1999:blog-8413638223026049990.post-8787314749081538242008-06-10T09:40:00.000-04:002008-06-10T09:40:00.000-04:00I figured out what was wrong.There are three param...I figured out what was wrong.<BR/><BR/>There are three parameters:<BR/>Contacts<BR/>AllContacts<BR/>AllContactsSelected <BR/><BR/>AllContactsSelected gets its default value from the Count of the first two. My mistake was that in defining the three parameters, I put them in this order:<BR/><BR/>Contacts<BR/>AllContactsSelected <BR/>AllContacts<BR/><BR/>So when AllContactsSelected tried to get the Count of AllContacts, AllContacts had not yet been run. A first year computer science error!<BR/><BR/>Thanks again.Markhttps://www.blogger.com/profile/05562108380647261960noreply@blogger.comtag:blogger.com,1999:blog-8413638223026049990.post-73801808205129304042008-06-09T22:21:00.000-04:002008-06-09T22:21:00.000-04:00Yes, both are set up as multi-value. If I use .Co...Yes, both are set up as multi-value. If I use .Count = .Count or .Count - .Count (or just any arithmetic involving .Count), I get an error. I will try again. :(Markhttps://www.blogger.com/profile/05562108380647261960noreply@blogger.comtag:blogger.com,1999:blog-8413638223026049990.post-23815151826774065692008-06-09T21:14:00.000-04:002008-06-09T21:14:00.000-04:00I am currently using this exact code in over 50 re...I am currently using this exact code in over 50 reports, with no problems.<BR/><BR/>I am running SQL Server Reporting Services 2005 sp2.<BR/><BR/>Not sure why you are saying the expression builder does not appear to like it if you use equal with the .Count fields of multi-value parameters. What error do you get? Are both parameters setup as multi value?<BR/><BR/>BobPBob Pearsonhttps://www.blogger.com/profile/16528166535649336177noreply@blogger.comtag:blogger.com,1999:blog-8413638223026049990.post-35848206593923603442008-06-09T16:23:00.000-04:002008-06-09T16:23:00.000-04:00This did not work for me. I think there is an err...This did not work for me. I think there is an error in your code, but even if fixed, it does not work.<BR/><BR/>For AllContactsSelected, use this expression:<BR/><BR/>=iif(Parameters!Contacts.Count = Parameters!AllContacts.Count,1,0)<BR/><BR/>that way, @AllContactsSelected gets a value of 1 or 0, not true or false.<BR/><BR/>But even if you fix this, the expression builder does not appear to like it if you use equal with the .Count fields of multi-value parameters. I even tried setting the value of this parameter to:<BR/><BR/>=(Parameters!Contacts.Count - Parameters!AllContacts.Count)<BR/><BR/>to see if there was any difference in the counts, and then in my query I used this (looking for @AllContactsSelected to have a value of 0):<BR/><BR/>Where (@AllContactsSelected = 0 or Contact in (@Contacts))<BR/><BR/>I still got the same error when I click on Preview:<BR/><BR/>"Error during processing of 'AllContactsSelected' report parameter."<BR/><BR/>If you just hardcode the value of this parameter to a number (=0), or just to a Count (Parameters!AllContacts.Count), that does not cause any errors. (Of course, neither of those is logically correct.)<BR/><BR/>Any way around this problem would be greatly appreciated.Markhttps://www.blogger.com/profile/05562108380647261960noreply@blogger.comtag:blogger.com,1999:blog-8413638223026049990.post-89261195053394270602008-02-29T10:32:00.000-05:002008-02-29T10:32:00.000-05:00I got around the "Please select a value" problem b...I got around the "Please select a value" problem by adding a null value to my list like so:<BR/><BR/>SELECT ID, Code FROM Codes<BR/>UNION<BR/>SELECT NULL, '(All)'<BR/>ORDER BY Code<BR/><BR/>Then in my Report Parameters Dialog, I checked the "Allow Nulls" check box. By default the report shows all codes but you can select a value from the drop down to narrow the results.Unknownhttps://www.blogger.com/profile/13720979942895621923noreply@blogger.comtag:blogger.com,1999:blog-8413638223026049990.post-45405167327220920592008-02-08T19:51:00.000-05:002008-02-08T19:51:00.000-05:00I can not get past the "Please select a value for ...I can not get past the "Please select a value for the Paramter". Any suggestions?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8413638223026049990.post-63341194858697322022008-01-22T03:01:00.000-05:002008-01-22T03:01:00.000-05:00Hi Bob,I try this technique but SSRS gives me an e...Hi Bob,<BR/><BR/>I try this technique but SSRS gives me an error: An error occurred during local report processing. Error during processing of AllContactsSelected report parameterAnonymousnoreply@blogger.com