CODEWITHSUNDEEP
sql-serverreporting-servicesreport
Lisa Rose
Lisa Rose

Reputation: 67

Filter Not Working in SQL Server Report Builder

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.

Upvotes: 0

Views: 3984

Answers (1)

PickledMisfit
PickledMisfit

Reputation: 21

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)

Upvotes: 1

Related Questions