Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Split String Help

Status
Not open for further replies.

eastwestk

Programmer
Jun 10, 2009
39
US
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.



 
Try this:

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

-LB
 
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.
 
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
 
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')}



 
What would you enter at the prompt to get:

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

...for a single value?

-LB
 
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
 
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')}
 
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:

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

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

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

-LB
 
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')}


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

-LB
 
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

 

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


 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top