I rarely use filters in my SSRS reports. However, this was a client requirement. When we attempted to use the filter to our surprise it did not work. The actual problem involved using a table that contained a column that was of a CHAR data type. After a little digging we realized that the cause of the problem was the column data type.
Assume you are using the following query as the source for your report, which can be run against the AdventureWorks sample database:
SELECT Name, ProductNumber, CAST (CASE WHEN Color IS NULL THEN ‘NA’ ELSE Color END AS CHAR(10)) Color, ReorderPoint, ListPrice, MakeFlagFROM Production.Product
Noticed that I forced the Color column to a CHAR data type to simulate the actual scenario. Then after creating the report I opened the properties window of the data set and added a filter on the color column:
Denoting “Black” as my filter value. When I ran the report to my surprise nothing was returned.
Why not? I thought for a few minutes and I started to search the web, but I decided just for grins to pad my filter with a few spaces. I actually padded it up to the number that was specified for the data type length. In the case of this example it is 5 additional spaces. I then reran the report and results were retuned:
Why? The short answer is because CHAR is a fixed length data type and it appears as though SSRS returns the value with padded spaces. There are a couple of ways to solve this problem. You could use the TRIM function on the DataSet Properties window.
Instead of simply specifying the column name as the Expression, in our case Color, you would specify an expression =TRIM(Fields!Color.Value). Then rerun your report. This is a simple solution. In addition, you could include a CAST in your query that changes the data type to a VARCHAR. Both of these can be done in your query and report design and does not require any schema changes. You could also take a very intrusive approach and request that your DBA change the data type to varchar. Regardless of the choice, either should solve the problem.
Talk to you soon,
Patrick LeBlanc, MVP, founder SQL Lunch
This is cool, Patrick. Thanks
I use VARCHAR in most situations, but this is a useful tidbit of knowledge for those situations when a CHAR is in play.
Also, I am curious. The secion above you have highlighted in yellow. I would have written it like:
CAST(ISNULL(Color,'N/A') AS CHAR(10)) Color,
Did you have a reason for using the CASE statement, or is that just the way it came out? There are always several ways to write the same thing and I like to learn why others make the choices they do.
I did not have a reason for using the CASE statement. I actually did not think about it. Thank.