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,
    MakeFlag
FROM 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