Select All (multi values) and Error

  • Joe Violanti

    SSC Veteran

    Points: 208

    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

    Hall of Fame

    Points: 3591

    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

    SSC Veteran

    Points: 208

    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

    Hall of Fame

    Points: 3591

    Its my pleasure..Joe..:)

  • simon.letts

    Hall of Fame

    Points: 3876

    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

    SSCrazy

    Points: 2810

    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

    Hall of Fame

    Points: 3876

    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

    SSCrazy

    Points: 2810

    Sure, you are welcome.

  • Duran

    SSChampion

    Points: 13259

    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 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply