How do I use Round with DataView RowFilter?

Question

I'm using DataView.RowFilter to filter the DataView and I'd like to compare rounded double values rather than the full double values. E.g., the values in the Value column are doubles with lots of precision, whereas the values I'm comparing against are only 2 decimal places.

dataView.RowFilter = "Value IN (1.76, 1.92, 2.44)"

does not work. I'd like to do something like:

dataView.RowFilter = "Round(Value, 2) IN (1.76, 1.92, 2.44)"

but this doesn't work (Round() isn't recognised)

Is this even possible with RowFilter? If not, any ideas about how I might achieve this?

Answer 1

Hey, I am also interested in this, but I think that I've found that it's not possible (at least in the current set of ASP Net functionality). The most useful RowFilter resource that I found is here: http://www.csharp-examples.net/dataview-rowfilter/

Within that site, the most useful tidbit is that you can do SUM, COUNT, MIN, MAX, AVG (average), STDEV (statistical standard deviation) and VAR; as well as CONVERT, LEN, ISNULL, IIF, TRIM, SUBSTRING.

My issue with the filter is when you've got an underlying value of 1.23456, and you're only displaying the first sig digits (so it'd display 1.23), and then trying to filter where the value = 1.23 gives no results. Ideally the ROUND functionality would allow me to round the row values to the sig-digs that I'm interested in, but I've also been unable to find a sane way of accomplishing this.

Good luck, I'll let you know if I stumble upon anything.

EDIT: Actually, after I wrote this I thought about a less janky way of doing this. One way that does work and isn't entirely terrible is to multiply both values by 10^x, and then convert to integers. Sample code below:

int mult = Convert.ToInt32(Math.Pow(10.0, _defaultDecimalSignificantDigits)); //in your example 2
            dv.RowFilter = String.Format("CONVERT({0}*{3},System.Int32){1}CONVERT({2}*{3},System.Int32)", name, expression, value, mult); //where "name" is your column name, "expression" is =, <>, etc, and "value" is the entered row filter value

The values are not multiplied in the display, only in the filter logic. So if your score was 14.998, you multiply your filter by 1000 (giving your filter and value a value of 14,998).

This obviously has limitations based on how many decimal spaces you're looking to round, but in your example of round by 2, you'd only have to multiply both by 100. With large enough numbers and/or enough decimal spaces rounded, you may also overflow Int32 boundaries, and Int64 might be necessary.