Search code examples
sqlssrs-2008

SSRS Sort a column by closest to a value

Working in SSRS, and I need to have a column that sorts the whole table based on a value closest to a number. Example

col1      col2
240       14
762       20
78        8
97        5

I need to have COL2 sorted by the number closest to 7. Below is what I expect to see.

col1      col2
78        8      (1 away from 7)
97        5      (2 away from 7)
240       14     (7 away from 7)
762       20     (13 away from 7)

I'm running a sql 2014 query on the backend to get my values. I dont really want to add another column to the query if I can help it.

Thanks for looking and I hope this makes sense.

Solution

  • In tablix properties select sorting and use this expression abs(7- Fields!col2.value).