Recently I've came across another strange issue with reporting serivces.I'm hopping that all these stuff are well addressed in Yukon version ,or else :-) .I didn't make the database case sensitive collation in the setup and case sensitive collation was not also activated in any of the columns throughout the database. In one of my tables, there is a column called "Client_Type" which is of type Varchar and unfortunately there are some bad data sitting there (might be because of wrong UI validations or data conversion or whatever).Below is different chunks of data in that column.
PRIMARY
PRIMARY MAKER
SECONDARY
Primary
Primary Maker
Secondary
From database standpoint, there is no difference between "Primary" and "PRIMARY" (which is obvious as I didn't activate "case sensitive collation"),but when it comes to Reporting services it DOSE matter!! specifically in formulas and string manipulation functions. How come it is case sensitive in reporting services whilst is not in its backend database ?!!!
There is a possible workaround for this. Add ToString().ToUpper() to all field values used in comparisons. Of course changing the collation of the database is another possibility,but Just for your reference, specifying collation is usually done for the whole server in the installation of sql server and changing one database collation afterward is a bit tricky ,because if you have one database on a server which has a different collation to the server default, it will also have a different collation to TEMPDB. This can cause unexpected problems with sorting of results, and handling of #temp table data. Better, if you can, to get the whole server at the right collation.
If you've ever filtered the data within the report using reporting services GUI, you may have realized that “%” as an escape character (wildcard) won’t work and you need to replace it with asterisk(“*”) instead.Let’s say you want to filter a field of the dataset called “Fields!Employees_Name.Value” to only contain employees whose first names start with “Jo”. Obviously there are two ways to do so:
- Return the filtered data from the data source and just show it in the report
- Return the raw (non-filtered) data from the data source and just filter it out either on data source level or the container bound to the data source (In this case a Table)
Well, I usually go for the first option, but there are some cases that your data source is shared between couple of containers in your report and you cannot filter it from the scratch. In these circumstances you got to define custom filters on container you wish to show data in it.If you right click on the table object in the report designer, select properties and choose the “Filter Tab” ,you can define the filters there using different operators ( = ,<>,Like and etc) . In our example we expect something like
Expression Operator Value
Fields!Employees_Name.Value Like Jo% (WRONG)
Amazingly this doesn’t work.I don’t understand why the standard T-SQL escape characters are changed in reporting services for Like operator!? , but the right way of doing that is as follow:
Expression Operator Value
Fields!Employees_Name.Value Like “Jo*” (RIGHT)
Now the question is what if I want to filter all the names starting with “Jo” OR “Te”?.First thing you may do is to add another line, and then you are tapped because GUI by default “AND” the conditions instead of “OR” and the GUI doesn’t let you change “AND” to “OR”, because the operator “LIKE” requires exactly 1 and only 1 filter value.If you still want to have a wildcard in both statements, then I guess you cannot do it in that way (you’d better change your data source to return the filtered data), but if you know exactly what you are looking for then there is a workaround which is directly changing the RDL (XML –Formatted) code. You need to use operator “IN” instead of “Like” as follows:
<Filters>
<Filter>
<FilterExpression>=Fields!Employees_Name.Value</FilterExpression>
<Operator>In</Operator>
<FilterValues>
<FilterValue>John</FilterValue>
<FilterValue>Johan</FilterValue>
<FilterValue>Johnoton</FilterValue>
</FilterValues>
</Filter>
</Filters>