July 3, 2014 at 5:25 am
Hi,
I am trying to run a report based on a stored procedure. The report uses 3 parameters and one of them (@comp_name) has a multi-select option for the users.
When I run the report for one value, the report shows the corresonding data, however, when multiple values are selected, then it draws a blank.
I have used a split function to sort out this issue but this doesnt work.
Could someone please tell what needs to be changed in the stored procedure / ssrs report ?
Please find the stored procedure attached. Also, below are the sample values in the parameter:
"V" trade (germany) a/s
"V" trade (espana) ltd.
"V" trade (italia) srl
"V" trade (belgium) N.V.
Thanks.
July 3, 2014 at 8:22 am
Looking at the SP it looks like you were on the right track to fixing the issue with the dbo.Split() function. When passing multi-value parameters to an SP you have to split them in order for the query to return the results you want, particularly when they parameter is passing string values. In your case the value in @comp_name is something like:
'trade (germany) a/s, trade (espana) ltd.'
So SQL Server is treating that a single string value so the where clause SQL is evaluating is:
r1.comp_name IN ('trade (germany) a/s, trade (espana) ltd.' )
And what you want is:
r1.comp_name IN ('trade (germany) a/s', 'trade (espana) ltd.' )
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 3, 2014 at 9:14 am
Jack Corbett (7/3/2014)
Looking at the SP it looks like you were on the right track to fixing the issue with the dbo.Split() function. When passing multi-value parameters to an SP you have to split them in order for the query to return the results you want, particularly when they parameter is passing string values. In your case the value in @comp_name is something like:'trade (germany) a/s, trade (espana) ltd.'
So SQL Server is treating that a single string value so the where clause SQL is evaluating is:
r1.comp_name IN ('trade (germany) a/s, trade (espana) ltd.' )And what you want is:
r1.comp_name IN ('trade (germany) a/s', 'trade (espana) ltd.' )
Thanks,
As I mentioned, I tried using the split function to resolve it but still getting the same result. The report shows no data. I don't know what other option I have.
July 3, 2014 at 9:25 am
Can you share the Split code?
Have you verified that the split function is returning what you expect?
When you pass in a single value using the Split function do you get results returned?
What is the datatype of the Item returned by the Split function? Does it match the column datatype?
I've done exactly what you are doing with the Split function when working with multi-value parameters without issue, so I have to think there is something odd goign on with the split function.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 3, 2014 at 9:38 am
Jack Corbett (7/3/2014)
Can you share the Split code?Have you verified that the split function is returning what you expect?
When you pass in a single value using the Split function do you get results returned?
What is the datatype of the Item returned by the Split function? Does it match the column datatype?
I've done exactly what you are doing with the Split function when working with multi-value parameters without issue, so I have to think there is something odd goign on with the split function.
Thanks again.
Please find the split code attached.
I noticed that even if I select a single value, the data still doesnt get displayed on the report.
The datatype of the item is nvarchar(4000) and datatype of the comp_name column is nvarchar(255).
July 4, 2014 at 3:19 am
Hi,
I need a code for a split function that can also handle double quotes in values along with the usual commas. Could anyone please help in this regard ?
I need this function for a multi-select report parameter which has the following sample values:
"V" trade (germany) a/s
"V" trade (espana) ltd.
"V" trade (italia) srl
"V" trade (belgium) N.V.
Thanks.
July 4, 2014 at 5:29 am
Any help would be appreciated...
July 7, 2014 at 8:13 am
We have a function that we use to split out the parameters from Reporting Services. I am not sure how it would handle the quotes. You would have to test that.
July 7, 2014 at 8:30 am
pwalter83 (7/4/2014)
Hi,I need a code for a split function that can also handle double quotes in values along with the usual commas. Could anyone please help in this regard ?
I need this function for a multi-select report parameter which has the following sample values:
"V" trade (germany) a/s
"V" trade (espana) ltd.
"V" trade (italia) srl
"V" trade (belgium) N.V.
Thanks.
What do you mean by "handle" double-quotes?
When I use your split function is looks like the issue is that spaces are being removed and have nothing to do with the double-quotes.
I recommend using Jeff Moden's split functions as found in his article here on SSC, http://www.sqlservercentral.com/articles/Tally+Table/72993/. I have tested this function against your data and it splits it correctly.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply