Select All (multi values) and Error

  • 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:

  • 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

  • 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.

  • Its my pleasure..Joe..:)

  • 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

  • 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

  • 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

  • Sure, you are welcome.

  • 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