locked
How to handle a comma delimited list parameter where the value contains comma? RRS feed

  • Question

  • User-1560896750 posted

    I have a problem splitting a parameter separated by comma but the value contains comma also.

    @Var1 = 'organization 1,organization 2,organization 3,organization 4,organization 5, inc.'

    The problem is Organization 5, inc. The comma before the word inc. is causing me problem. How do you handle this guys?

    BTW, the parameter is passed from SSRS rdl parameters to SQL Stored procedures.

    Tuesday, June 25, 2013 1:05 AM

Answers

  • User281315223 posted

    You would require some kind of additional delimiter in order to determine where you should perform your Split() method at. (This documentation is for C# / Visual Basic, but the basic functionality is the same)

    Perhaps when you are actually building this single string consider adding in some value that will not be contained within your strings to use as a delimiter such as an asterisk : 

    organization 1*organization 2*organization 3*organization 4*organization 5, inc.

    The Split() method syntax functions a bit different within SRSS (as seen in this Stack Overflow discussion) but would roughly resemble the following : 

    //Accesses the first value ("organization 1")
    =(Split(Fields!Var1.Value, ",")).Value(0)
    //Accesses the second value ("organization 2")
    =(Split(Fields!Var1.Value, ",")).Value(1)

    However, as previously mentioned when you are building this string you'll need to use some other type of delimiter and append it between each of your values within your string.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 25, 2013 11:42 PM

All replies

  • User-1629691846 posted

    in that case you have to split parameters by semicolon (;) or other special characters.

    Tuesday, June 25, 2013 2:50 AM
  • User-1560896750 posted

    Rohit,

    Is that possible in SSRS? I've been looking on how to do that in SSRS report designer but failed.  

    Tuesday, June 25, 2013 11:29 PM
  • User281315223 posted

    You would require some kind of additional delimiter in order to determine where you should perform your Split() method at. (This documentation is for C# / Visual Basic, but the basic functionality is the same)

    Perhaps when you are actually building this single string consider adding in some value that will not be contained within your strings to use as a delimiter such as an asterisk : 

    organization 1*organization 2*organization 3*organization 4*organization 5, inc.

    The Split() method syntax functions a bit different within SRSS (as seen in this Stack Overflow discussion) but would roughly resemble the following : 

    //Accesses the first value ("organization 1")
    =(Split(Fields!Var1.Value, ",")).Value(0)
    //Accesses the second value ("organization 2")
    =(Split(Fields!Var1.Value, ",")).Value(1)

    However, as previously mentioned when you are building this string you'll need to use some other type of delimiter and append it between each of your values within your string.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 25, 2013 11:42 PM
  • User-1560896750 posted

    Rion,

    I already have the Split function in SQL Server. I think what's needed is the way SSRS concatenate the selected parameter values so that it will not use the comma ( , ) delimited but something like a semicolon ; character.

    Wednesday, June 26, 2013 2:49 AM
  • User-1620892269 posted

    Hi,

    Try the Join function.

    Join(Parameters!MyParameter.Value, ";")


    Thanks.

    Tuesday, September 24, 2013 3:49 PM