Filter Not Working in SQL Server Report Builder

Question

I'm attempting to create a filter on a tablix in Report Builder 3.0. I click on the Tablix, choose Properties and then click on Filters. Clicked on Add and created my filter:

Expression [SYSTEM_CODE]
Operator =
Value 1005

I don't get an error message but when I run the report, nothing is showing when I know there's valid data for that system code. It doesn't matter what value I choose, I don't see anything when I run the report. It also doesn't matter if I enclose the value in single or double quotes. There is no grouping on this report so there's no filter there either.

I'm pulling my hair out because this should fairly simple.

A couple of quick questions: Does that column include any null values? What's the data type of the column (int or string) and is the filter's data type set appropriately in its drop-down? — bitnine
No null values and the data type automatically picks Text in the drop down and is grayed out as soon as I select the system_code field. — Lisa Rose
Do the system codes have any leading or trailing spaces? Another thing you might try is to refresh the cached dataset. — Bostaevski
A quick way to test for things like whitespace is to change the filter to Like *1005*. You can actually start with Like * to see if there's something about the filter that's broken full stop, then add characters until it filters out more than you're expecting. — bitnine

PickledMisfit PickledMisfit · Accepted Answer · 2021-03-01T16:58:10

I had a similar issue and maybe what I found will work for you. I created a simple query to show you what I found (note the Convert statement is intentional here):

Select getdate() as "MyDate", CONVERT(Decimal(12,0), 1005) As "System_Code"

I then created a filter for the System_Code value. Note the Float value in the Type field that you cannot change.

Screen shot of disabled Type field

When I ran the report, I got this error:

The processing of FilterExpression for the tablix ‘Tablix1’ cannot be performed. Cannot compare data of types System.Decimal and System.Double. Please check the data type returned by the FilterExpression.

I know, it says Decimal to Double (I blame Microsoft as this baffled me too). This is happening because this type of parameter can only process Integer and Float values. Report Builder doesn't let you change them as it infers this type from the dataset (based on my own experience). If you set the query to return an Integer, the filter will work:

Select getdate() as "MyDate", CONVERT(Integer, 1005) As "System_Code"

However, checking the Type field will still be disabled, and may show as float. Selecting another value in the drop down expression field then selecting back to the System Code value will change the Type value from Float to Integer.

The funny thing is, if you try another Convert data type value, i.e. BigInt, you will get that you cannot convert from Int64 to Float (/sigh Microsoft).

The closest I could get to seeing why this is happening, after many hours of searching, was this page on Microsoft's site which discusses that the value expected is an Integer or a Float. Even though I am not performing a Sum function, the effect appears the same.

Sum Function (Report Builder)

Filter Not Working in SQL Server Report Builder

1 Answers