CODEWITHSUNDEEP
reporting-servicesfilterrow-number
WillyC
WillyC

Reputation: 4705

Type Problems with Filter Expression in SSRS

SQL Server 2008 R2, using BIDS to design the report.

I have a table and I am trying to only show a certain row. Maybe there are better ways to do this, but I am coming across an error with the filter expression and regardless of how I achieve my initial task, I'd like to understand the filtering.

I started with the filter expression (set to type "Integer"):

RowNumber(Nothing) = 1

This gave the error:

Cannot compare data of types System.String and System.Int32.

I found the solution to this is to change the 1 to "=1" as 1 is evaluated as a string.

So I then had:

RowNumber(Nothing) = =1

That changed nothing, I got the same error.

Then I tried to do that to the first part of the expression:

=RowNumber(Nothing) = =1

This changed the error to a deployment problem (still builds, which is frustrating):

Error pvInvalidDefinition : The definition of the report '/ReportName' is invalid.

I then tried using CInt on RowNumber: CInt(RowNumber(Nothing) = =1

Then I can deploy it, but the error just changes back to the first one:

Cannot compare data of types System.String and System.Int32.

It seems no matter what I try here I either can't deploy the report or I get an error that I'm comparing a string to an int.

RowNumber returns an integer, so it seems like this should work. I've tried using the name of the dataset in place of "Nothing" but that doesn't change what I'm seeing.

I realize there are many ways to solve my initial problem, but I am curious as to why the filter expression is invalid.

Upvotes: 2

Views: 10397

Answers (2)

WillyC
WillyC

Reputation: 4705

RowNumber is not available to use in a Tablix Filter.

Using RowNumber(Nothing) <> 1 as a Row visibility property fixed the issue.

Using BIDS you are not given any error that indicates what the problem is, but importing the report to Report Builder and deploying it from there will give a more descriptive error that, in the end, helped me to solve my problem.

Upvotes: 1

Akarsh Kolanu
Akarsh Kolanu

Reputation: 146

Its better to hide a row with visibilty property. Just click on any text box and go to visibily tab . You can now click on show or hode and go to expression.

That default to Hide . So write an expression there to hide the row. =IIf(NOT(RowNumber = 1),TRUE,FALSE)

Let me know if you get any error

Upvotes: 1

Related Questions