April 21, 2008 at 11:04 am
I have created a Report and attached it to my database. I have 4 parameters in the report, all of which are multi-value. If I select one value for each of the drop downs, the report runs fine. If I select more than one (either by checking 2 or 3 or by using "Select All") in any of the fields, I get the following error when previewing the report:
I have the machine fully patched and am assuming that SQL or SSRS doesn't like multiple parameters passed with a single quote (') as opposed to a double-quote(")? How can I fix this error? The parameters are all multi-value strings with available values coming from a query (a SELECT....FROM....WHERE statement). Any help is appreciated (I'm obviously not a SQL guru, so be gentle). :hehe:
April 22, 2008 at 7:15 am
Hi Joe,
Looking at your issue, I can guess that you want Multivalues to be working for your reports....
Actually, Multivalue parameters works when we use IN with them...
It seems that you are using EQUAL within your query that's why you are receiving the error after execution of the report..
I would advice you to use IN with Multivalue parameters b'coz it then splits the selected multiple values with a ',' [Comma]...and executes the concerned dataset..
You can try it and let me know if it works.
Cheers,
Niraj
April 22, 2008 at 3:03 pm
Yes, thank you. You were correct. I realized after I dove into some reference materials that I was creating the problem by using EQUALS rather than IN. Thank you for the quick response and support.
April 22, 2008 at 9:39 pm
Its my pleasure..Joe..:)
December 1, 2011 at 7:23 am
Hi, I just found this whilst searching my issue (Just posted today), I've been told to do this before too and it still doesn't work?? I'd appreciate it if you had time to have a look at my post. Many thanks!
Simon
December 1, 2011 at 12:01 pm
Simon,
you should allow multiple string concatenation using ',' in all your datasets.
for instance if you are using
select item_id, item_desc from Items
instead of that
USE ---
-----------------------
Declare @ParamItem varchar(1000),
@StringVARCHAR(500)
set @Paramitem = @item
Declare @temp table (item_id int, item_desc varchar(100))
Begin
WHILE LEN(@Paramitem) > 0
BEGIN
SET @String= LEFT(@Paramitem, ISNULL(NULLIF(CHARINDEX(',', @Paramitem) - 1, -1), LEN(@Paramitem)))
SET @Paramitem = SUBSTRING(@Paramitem, ISNULL(NULLIF(CHARINDEX(',', @Paramitem), 0), LEN(@Paramitem)) + 1, LEN(@Paramitem))
insert into @temp (item_id , item_desc )
select distinct item_id , item_desc from ITEM
END END
select distinct item_id , item_desc from @temp
-----------------------
do remember that ssrs is case sensitive. HTH
December 5, 2011 at 4:42 am
Thank you for this very useful info. I've managed to fix my issue another way (good old RS) but your info will go straight into my toolbox! Thanks again
December 5, 2011 at 1:08 pm
Sure, you are welcome.
November 28, 2016 at 7:18 pm
If you can remember that far back, what was the other way? I do not understand why you would not try to help others as other have helped you?
Regards,
D.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply