September 27, 2013 at 1:24 pm
I have a report generated from a stored procedure. There is a code called "cip" with different lengths of code. 2-digit is a higher level than 4-digit, etc. In the stored procedure, I wrote the WHERE statement as "cipcode like @cip+'%'" and if I set @cip = '51', I get all cip codes starting with 51, regardless of length. This is what I want.
This works in SSRS up to a point. Since most people do not have cip codes memorized, this needs to be a drop down list. When '51' is selected from the drop down list (really the label is selected), the LIKE part doesn't seem to work anymore, and I just get the one row result of 51, instead of multiple rows for 5102, 510201, etc.
I've tried multiple things, and nothing quite works. Can anyone help? Thank you.
September 27, 2013 at 2:19 pm
Just to be clear, are you passing the value from the report parameter to the parameters property of the dataset based on your stored proc? Or did you set up a separate dataset filter based on the parameter?
September 27, 2013 at 2:44 pm
The report data is populated from a stored procedure. But in order to populate the drop down list, I set up another dataset from a query.
SELECT DISTINCT cipcode, ciptitle
FROM dbo.q_Outcomes
WHERE LEN(cipcode) = 2
ORDER BY cipcode;
Then set the @cip parameter "Available Values" to pull from this query. This works, but the LIKE part of the query in the original procedure goes away, and only one value is returned.
I hope this answered the question.
September 27, 2013 at 8:15 pm
Although I cannot tell you why the procedure you are using does not work -- I would have done the same thing more than likely -- I can suggest an alternate way to code the WHERE clause in the stored procedure to eliminate the LIKE comparison from the mix. Try the following:
WHERE LEFT(cipcode, LEN(@cip)) = @cip
Using this code if a person chooses a two-digit option, it will match the left two digits of the cipcode column to the @cip variable. If they select a four-digit code, it will match the left four digits, and so forth. (I know you are only providing two-digit options in the report parameter, but this approach will allow for other options should you want to allow longer parts of the code to match.)
This approach should be functionally identical to the LIKE comparison you used, but perhaps it will work better in this context.
September 30, 2013 at 7:38 am
Geoff,
To be perfectly honest, I'm not really sure why you're solution worked, but it did. Wait, I think I got it, but I don't know if I'd ever have come up with that. Very clever. Hopefully I'll remember this. Thank you so much!
Amy
October 1, 2013 at 9:39 am
geoff5 (9/27/2013)
Although I cannot tell you why the procedure you are using does not work -- I would have done the same thing more than likely -- I can suggest an alternate way to code the WHERE clause in the stored procedure to eliminate the LIKE comparison from the mix. Try the following:
WHERE LEFT(cipcode, LEN(@cip)) = @cipUsing this code if a person chooses a two-digit option, it will match the left two digits of the cipcode column to the @cip variable. If they select a four-digit code, it will match the left four digits, and so forth. (I know you are only providing two-digit options in the report parameter, but this approach will allow for other options should you want to allow longer parts of the code to match.)
This approach should be functionally identical to the LIKE comparison you used, but perhaps it will work better in this context.
This code will have a negative effect on performance if the OP's table is very large. Typically, a query like this over a large table will be supported by an index on [cipcode], but wrapping the column [cipcode] in the LEFT() function in a WHERE clause will prevent SQL Server from performing a seek on this index. Instead, it will have to scan the entire index to evaluate the result of the LEFT() function for every row to filter out those that don't satisfy the WHERE condition. This may not be a big deal of the OP's table is not very large (SQL Server will opt to scan small tables even when it could perform a seek because scans can be less expensive than seeks on small tables), but scanning a larger table every time the query runs could be disastrous.
Back to the original problem, is it possible that the [cipcode] values you select to populate the parameter drop-down list have trailing spaces, Amy.G? This is very likely if the [cipcode] column in the database is a char() type with a length greater than 2. For example, if [cipcode] is a char(5) type, the value '51' will be padded with 3 trailing blanks. The LEN function still returns 2 for the value '51 ', but the expression @cip + '%' in your WHERE clause would evaluate as '51 ' + '%' (i.e., those blanks would be included in the resulting string) and the LIKE comparison would fail for values such as '51542' and '5154'. The LIKE comparison still works for '51 ', which is why you get that row only. The same thing will happen if the parameter @cip is specified as a char() type with length greater than 2. Geoff5's code works because the LEFT strips off the trailing blanks from [cipcode], with the major drawback identified above, and the equality operator disregards any trailing blanks or spaces in @cip.
Try this instead: WHERE cipcode LIKE RTRIM(@cip) + '%'
RTRIM strips those troublesome trailing blanks from @cip (any trailing blanks in [cipcode] don't matter because they will not affect the outcome of the LIKE comparison) and applying it to the variable rather than the column value avoids the problem explained above.
Jason Wolfkill
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply