RowNumber() in Filter

[Dan White]

I want to create a report that just shows the top 5 records for use in a dashboard. (I'll link to another page with the full results.) I want the number top number (5 inthis case) to be configurable, so I created a report parameter, RecLimit, which is an Integer with a default value of =5. To limit the records I created a filter on the report and used the Top N operator, so my filter looks like this:
    Expression: =RowNumber()
    Operator:    Top N
    Value:         =Parameters.RecLimit

When I preview the report, one of two things happens. If the report parameter UI.Visible property is set to True then I get the error "Filter cannot be evaluated. Value should evaluate to Integer." If the visibility is set to False then the report displays with all the records. The Top N filter is ignored.

If I run in debug I see this stack trace:

 

System.InvalidOperationException: Filter cannot be evaluated. Value should evaluate to Integer.

at Telerik.Reporting.Processing.Data.TopBottomFilterOperator.Create(IEnumerable`1 filters, Object context)

at Telerik.Reporting.Processing.Data.RuntimeDataSource..ctor(Object dataSource, String dataMember, IEnumerable`1 filters, IEnumerable`1 sorting, Object expressionContext, String name)

at Telerik.Reporting.Processing.Data.RuntimeDataSource..ctor(IDataSource dataSrc, Object expressionContext)

at Telerik.Reporting.Processing.ReportDataSource..ctor(IDataSource1D dataSource, Boolean detailGroup, Object expressionContext)

at Telerik.Reporting.Processing.Report.ProcessItem()

at Telerik.Reporting.Processing.ReportItemBase.Process(DataMember data)

 

Doesn't RowNumber() return an Integer?
What am I doing wrong here?

I created the report with Reporting Q2 2009 (version 3.1.9.807).

[Steve]

Hi Dan,

The RowNumber() is not yet available when the report is processed and that is why it would show all records. The TOP N operator is a ranking function and if you feed it with a field, it should do the job just fine i.e.:

  Expression: =Fields.MyField
    Operator:    Top N
    Value:         = CInt(Parameters.RecLimit)

The parameter should return integer and we would research why it does not get recognized currently, please use CInt() for the time being.

All the best,
Steve
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Dan White]

Thanks for the explanation, Steve. That makes sense if the RowNumber() is not available yet at the time the filter is being processed. I had actually used a field previously instead of the RowNumber() function, but that wasn't giving me quite what I wanted either. (It did not give me this error though, so I guess it was closer.) The problem with using a field with the TOP N operator is that it takes the TOP N unique values in that field. I don't want that. I want to limit the number of records displayed--I don't care if all the values are different or the same. In my specific situation, I'm sorting by a date field descending to get the 5 most recent records. If two of the records have the same date (and time actually) I end up with 6 records. If there are more repeated values I could end up with many more records than the 5 I want. Is there another way around this? As a workaround at this point, I'm having to change my database query to limit to a certain number of rows and not use the filter in the report. But I'd like to do the filter based on a report parameter if possible.

I had used the CInt() function at one point to try to avoid the Integer error, but I can't remember if that was before or after I switched from a field to RowNumber(). I'll use it again if necessary.

[Steve]

Hi Dan,

The "Top N" operator works exactly as its SQL counterpart and this is by design, so yes is would work with the unique values only. It seems that you are not looking for a ranking function but simply want to push the number of records returned, no matter unique or not. In this case your solution of limiting the query directly is best, as this way you only pull the desired number of records, while when you filter through the report, the whole data is pulled from the database and then filtered on report level. Nevertheless, you can still filter on report level by adding a helper column which you would use only for the filter and would not worry about whether values are unique.

All the best,
Steve
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.