CODEWITHSUNDEEP
reporting-servicessql-server-2008-r2parameter-passingcase-insensitive
Hans
Hans

Reputation: 79

ssrs parameter values case sensitive

I have a link to a report (SSRS 2008 R2):

http://reportserver/pages/reportviewer.aspx?/folder1/report1&hostname=SERVER2

This report has a (drop-down list) parameter which contains hostnames. The drop-down list has these entries:

Problem is that sometimes the hostname is passed in uppercase (i.e. SERVER2) and thus the hostname is not selected in the drop-down list does not match the URL parameter. This is because SSRS is case-sensitive with regards to parameter passing.

I do not want the case-sensitivity.

I tried the setting on the Dataset (Dataset Properties - Options - Case sensitivity), but that did not work.

Is it possible ?

The values in the drop-down box or the URL, I cannot modify. Best solution would be case-insensitive parameter VALUE passing.

Upvotes: 2

Views: 9985

Answers (1)

Mike D.
Mike D.

Reputation: 4104

Use the LOWER() function in your SQL or the LCASE() function in SSRS. This function converts the string to all lowercase and if you do it to both sides then effectively case doesn't matter.

So either:

WHERE LOWER(fieldName) = LOWER(@parameter)

or

=LCASE(Fields!fieldName.Value) = LCASE(Parameters!selection.Value)

More info:

Update

I slightly misunderstood your question. So you're accessing the report by manually generating the URL with the parameter value in it? This value needs to be passed in a consistent manner or this isn't going to work. SSRS is case sensitive and you can ensure that the available values in the report parameter are formatted consistently using the functions above.

But the generated URL also needs to be consistent. By doing it this way you are explicitly setting the value of the parameter and if it isn't a valid, available value it isn't going to work. The issue here isn't really SSRS it is the fact that the source of the URL does not generate the parameter value in a consistent way.

Fix your URL construction.

Upvotes: 3

Related Questions