Split String Help

[eastwestk]

I have a join formula as below in my crystal report ( the parameter is multi-value parameter).

Join({?testparam - Productname},",")

which will return the result as follows

'Product 1,Product 2,Product 3'

But I want the result as follows

'Product 1','Product 2','Product 3'

Please advise.

 

[lbass]

Try this:

"'"+join(({?testparam - Productname},"','")+"'"

-LB

 

[eastwestk]

I forgot to mention that I have a subreport. I linked the below Join formula in the main report to the stored procedure parameter in the subreport, inorder to pass a multi-value parameter to the stored procedure.

//Formula: @JoinFormula
Join({?testparam - Productname},",")

Will your suggestion still works.

 

[lbass]

I guess it depends upon how the stored procedure is set up to accept it. Why not try it? If I were doing this in a command, I would set it up like this:

table.field in {?ProductName}

I would change the formula to:

"('"+join(({?testparam - Productname},"','")+"')"

-LB

 

[eastwestk]

I just tried & it didn't work, when I say show sql query in subreport I see the following:

For multiple values:
{CALL "xyz"."dbo"."storedprocedurename";1('(''Product 1 '',''Product 2'')')}

For single value:
{CALL "xyz"."dbo"."storedprocedurename";1('(''Product 1 '')')}


where as it should be as follows:

for multiple values:
{CALL "xyz"."dbo"."storedprocedurename";1('Product 1', 'Product 2')}


for single value:
{CALL "xyz"."dbo"."storedprocedurename";1('Product 1')}

 

[lbass]

What would you enter at the prompt to get:

{CALL "xyz"."dbo"."storedprocedurename";1('Product 1')}

...for a single value?

-LB

 

[eastwestk]

I am displaying all the available parameter values to the user in the crystal report and the
user will select either a single value or multi value as below.

Single Value
Product 1

Multiple Value
Product 1
Product 2

 

[lbass]

That wasn't what I was asking.

-LB

 

[eastwestk]

Product 1

 

[eastwestk]

In crystal reports XI R2 go to Database menu and click Show SQL Query, you will see the following statement.

{CALL "xyz"."dbo"."storedprocedurename";1('Product 1')}

 

[RTag]

I am not clear why do you want to add apostrophe to the values. If you are trying to use the value of the parameter in a dynamic SQL keep in mind that this will be a huge security risk.

for example sql like this :
'SELECT * FROM TableName WHERE ColumnName in ' + parameterValue
will be transformed to:
SELECT * FROM TableName WHERE ColumnName in ('Product 1', 'Product 2')

but if the name of the third product is: '); DROP TABLE TableName--'

The executed SQL will be :
SELECT * FROM TableName WHERE ColumnName in ('Product 1', 'Product 2',''); DROP TABLE TableName--')
... and you table will be deleted (classic SQL injection).

It will be safer if you pass the values in a comma separated string and convert it to a table. Check this article for details and sql code that may be helpful:

http://r-tag.com/MultivalueParameters.aspx

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[lbass]

Try this:

join({?testparam - Productname},"','")

...which has ',' within double quotes, in case you can't tell.

-LB

 

[eastwestk]

I just tried, it worked if I select single value but did not worked with multiple values selection.

when I say show sql query for subreport I see the following:

{CALL "xyz"."dbo"."storedprocedurename";1('Product 1 '',''Product 2')}

 

[lbass]

Please show the exact formula you are using (copy and paste).

-LB

 

[eastwestk]

join({?testparam - Productname},"','")

 

[lbass]

What do you see in the SQL query when you use :

join({?testparam - Productname},",")

I'm guessing I can't help, as I don't have any stored procedures to test.

-LB

 

[eastwestk]

{CALL "xyz"."dbo"."storedprocedurename";1('Product 1,Product 2')}

 

[lbass]

Sorry, I don't think I can help.

-LB

 

[eastwestk]

Thank you Ibass

 

[CR85user]

What result from:

replace(Join({?testparam - Productname},","),',','','')

compared to:

"'"+join(({?testparam - Productname},"','")+"'"


Try writing out the question again perhaps giving as much info and examples as possible.

'J

CR8.5 / CRXI - Discovering the impossible