SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Select All (multi values) and Error


Select All (multi values) and Error

Author
Message
Joe Violanti
Joe Violanti
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 10
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:

An error occurred during local report processing.


An error has occurred during report processing.


Query execution failed for for data set 'DATASETNAME'.


Incorrect syntax near ','.



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
Niraj-627298
Niraj-627298
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2143 Visits: 719
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
Joe Violanti
Joe Violanti
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 10
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.
Niraj-627298
Niraj-627298
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2143 Visits: 719
Its my pleasure..Joe..Smile
simon.letts
simon.letts
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2124 Visits: 621
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
SQL_path
SQL_path
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1354 Visits: 342
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),
@String VARCHAR(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
simon.letts
simon.letts
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2124 Visits: 621
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
SQL_path
SQL_path
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1354 Visits: 342
Sure, you are welcome.
Duran
Duran
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7384 Visits: 1899
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search