none
Passing multiple integer values as parameter in SSRS

    Question

  • Hello,

    I am trying to pass a comma separated multiple integer value parameter in SSRS report and it errors out complaining that it is not able to convert the nvarchar value to datatype int. But it works fine when I pass a single value. Here is the code I am using for the report and I am stuck at passing multiple values.

    SELECT         
    id,count(*) as cnt
    from table
    and ID in (@id) group by id

    eg: if I pass 10,20,30 then I would get error however if I pass 10 then it works fine

    Is there a different method to pass multiple integer values?

    Thanks for your help.

    Thursday, April 03, 2014 4:01 PM

Answers

  • What does your split function look like? Where are you putting it? what does the parameter string look like?

    Split has to work. I have used it before. In fact I just validated it so I guess what I was trying to describe wasn't clear enough and there is something wrong with your implementation. Here is a more detailed account:

    I created a simple dataset that gets all rows from a table:

    SELECT
      DimArea.AreaSK
      ,DimArea.AreaName
      ,DimArea.AreaGUID
      ,DimArea.ParentAreaGUID
      ,DimArea.AreaPath
      ,DimArea.[Depth]
      ,DimArea.ForwardingID
      ,DimArea.ProjectGUID
      ,DimArea.ParentAreaSK
      ,DimArea.LastUpdatedDateTime
    FROM
      DimArea
    WHERE
      DimArea.AreaSK IN (@id)

    Note the WHERE clause. I saved the dataset which automatically created an id parameter for me. It is of type text. For convenience I set the default value to "1,2,3" so I don't have to set anything when I run the report. I then opened the dataset properties by double-clicking on it, then chose the parameters tab (left column).

    I clicked the expression builder (fx) button next to the value for the id parameter (right column) and entered the formula:

    =Split(Parameters!id.Value,",")

    Then clicked OK until I get back to the report designer. Run the report and I get only 3 rows returned where I had hundreds before, the 3 rows with AreaSK values of 1, 2, and 3.

    Is this how you implemented it?


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Thursday, April 03, 2014 7:19 PM

All replies

  • Try to "Specify Values" from the "Available values" tab, that is if there are not too many values...

    And then click "Allow multiple values" on the general tab

    This way you will be able to see a drop-down of values and you will be able to select multiple values viz, 10, 20, 30...


    Dhananjay Rele

    Thursday, April 03, 2014 4:06 PM
  • These values are not always the same. Since this is a report each user using this will enter his/her own value not necessarily 10,20,30. The code and parameters I specified above are just for an example (not actual code).
    Thursday, April 03, 2014 4:12 PM
  • A comma is, of course, a textual character and not an integer, thus the error you are getting. Multi-value parameters can be used for this.

    Select "Allow multiple values"and set data type to Integer. Multi-value parameters deliver the values as an array. The query interface is smart enough to convert them to the proper syntax (comma-delimited) automatically so a statement like:

    WHERE Field IN (@id)

    where the parameter has an array of values {1, 2, and 3} is interpreted as:

    WHERE Field IN (1,2,3)

    You do not need to manually convert it in a transact SQL query. That is not the case for displaying the value array in your report. If you wish to display the array (described above) in your report as "1, 2, 3", you will need to use a join expression:

    =Join(@id, ", ")

    As DJ described, the other part to a multivalue parameter is the Available values. These can be set explicitly but given your example, I think it would be best to create a new dataset that retrieves a distinct list of the IDs that can be retrieved, perhaps something like:

    SELECT DISTINCT GroupName, GroupID
    FROM table
    WHERE [criteria]

    Set the available values to use this query with GroupName (or whatever user-friendly field you choose for your dataset) as the label and the id as the value. The label field should be something the the target report user can easily identify the correct group using.

    If you don't want to or can't use a multivalue parameter for some reason then you will need to manipulate your parameter value prior to consuming it in your query. When you add the parameter to the TSql query and save the dataset, SSRS adds it to the Parameters property of the dataset. Open the dataset properties and select the parameters tab. You should see your parameter in the list. click the expression builder (fx) button next to the value and enter this expression:

    =Split(@id,",")

    Note that the second element of the expression is the delimiter. If your text input has comma-space as a delimiter (1, 2, 3 vs 1,2,3) then that element must include ", ". My example just has comma so if you use that with a string "1, 2, 3"then the resulting array will still have 3 elements but the 2nd and 3rd elements of the array will have a preceding space which will cause your dataset to error with the same error. As long as there are no non-numeric characters in any of the elements, the split6 will create the text array, SSRS will dynamically generate the correct "IN"syntax (comma separated) and SQL will convert the elements from VARCHAR to INT on the fly.


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Thursday, April 03, 2014 4:53 PM
  • Tim,

    The split function for some reason wont work. Since I have too many distinct id's I wanted to avoided selecting them from a drop down. But it turns out to be the only way by creating another dataset and having the distinct id's selected to retreive data for multiple ids. Is it possible to not just select values from a dropdown but we can type?


    • Edited by actdba Thursday, April 03, 2014 6:26 PM
    Thursday, April 03, 2014 6:09 PM
  • What does your split function look like? Where are you putting it? what does the parameter string look like?

    Split has to work. I have used it before. In fact I just validated it so I guess what I was trying to describe wasn't clear enough and there is something wrong with your implementation. Here is a more detailed account:

    I created a simple dataset that gets all rows from a table:

    SELECT
      DimArea.AreaSK
      ,DimArea.AreaName
      ,DimArea.AreaGUID
      ,DimArea.ParentAreaGUID
      ,DimArea.AreaPath
      ,DimArea.[Depth]
      ,DimArea.ForwardingID
      ,DimArea.ProjectGUID
      ,DimArea.ParentAreaSK
      ,DimArea.LastUpdatedDateTime
    FROM
      DimArea
    WHERE
      DimArea.AreaSK IN (@id)

    Note the WHERE clause. I saved the dataset which automatically created an id parameter for me. It is of type text. For convenience I set the default value to "1,2,3" so I don't have to set anything when I run the report. I then opened the dataset properties by double-clicking on it, then chose the parameters tab (left column).

    I clicked the expression builder (fx) button next to the value for the id parameter (right column) and entered the formula:

    =Split(Parameters!id.Value,",")

    Then clicked OK until I get back to the report designer. Run the report and I get only 3 rows returned where I had hundreds before, the 3 rows with AreaSK values of 1, 2, and 3.

    Is this how you implemented it?


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Thursday, April 03, 2014 7:19 PM
  • Yes. The same way. 
    • Edited by actdba Thursday, April 03, 2014 8:53 PM
    Thursday, April 03, 2014 8:47 PM
  • Ok I think I had the type selected for this variable as Integer and changed it to text and it works. Thanks
    Thursday, April 03, 2014 8:54 PM
  • Glad to be of help.

    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Thursday, April 03, 2014 9:33 PM