Visual Studio Reporting Services - MultiValue Parameters

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • RiddleMeThis
    New Member
    • Feb 2010
    • 8

    Visual Studio Reporting Services - MultiValue Parameters

    Hi, I dunno if this is in the correct catagory, hope so, sorry if it's not.

    I'm trying to get to grips with SSRS and having a bit of trouble with multi value parameters.
    I'm using the following code in the data section:
    Code:
    SELECT AccID, AccName, StartDate 
    FROM Account 
    WHERE AccID= @prmAccID
    This works fine with the default settings, the report prompts me for @prmAccID, so I put in an Account ID and it gives me the relevant name and start date to go with that account.

    However if I go to the 'Report > Report Parameters' dialog and check the "Multi-Value" checkbox then try and run the report, it still works if I only put one account ID in at the prompt, but if I put 2 account ID's in I get the following error:
    An error occured during local report processing.
    An error has occured during report processing.
    Query execution failed for data set 'DSAccountRepor t'.
    Must declare the scalar variable "@prmAccID" .
    Surely it should use the following code if i put in AccID 34 and 35
    Code:
    SELECT AccID, AccName, StartDate 
    FROM Account 
    WHERE AccID= 34
       OR AccID = 35
  • RiddleMeThis
    New Member
    • Feb 2010
    • 8

    #2
    Just realised the code I should have been using is:
    Code:
    SELECT AccID, AccName, StartDate  
    FROM Account  
    WHERE AccID IN (@prmAccID)
    It doesnt treat it like an OR condition, it treats it like an IN condition and so, with account IDs 34 and 35 for example, it would use the following code:
    Code:
    SELECT AccID, AccName, StartDate  
    FROM Account  
    WHERE AccID IN (34,35)

    Comment

    Working...