How to use different values for sort and display in DataTables 1.10

Question

I've upgraded to DataTables 1.10 and am having trouble using column.data or column.render to use different values for sort and display.

The data looks like:

[
    {
      "title":"Overview Report: (2014-07-12 11:49 - 2014-07-12 23:49)",
      "reportDateRangeMilliseconds":43200000,
      "DateRange":"12 hours"
   },
   {
      "title":"User Overview Report: (2014-07-12)",
      "reportDateRangeMilliseconds":86400000,
      "DateRange":"1 day"
   },
   {
      "title":"Activity Report: (2014-07-31 23:00 - 2014-08-03 00:00)",
      "reportDateRangeMilliseconds":176400000,
      "DateRange":"2 days, and 1 hour"
   }
]

I want to create one column that displays DateRange and sorts using reportDateRangeMilliseconds

I've tried:

$('#reportList').dataTable({
    "data" : reportData,    
    "columns" : [
        { "title" : "Report Name",
         "data" : "title"
        },
        { "title" : "Date Range",
          "data" : "reportDateRangeMilliseconds",
          "render" : {
             "display" : "DateRange"
          }
        }
    ]
})

But it returns the error:

DataTables warning: table id=reportList - Requested unknown parameter 'reportDateRangeMilliseconds' for row 0. For more information about this error, please see http://datatables.net/tn/4

See http://jsfiddle.net/scottglew/pmpj9uyb/1/

I've also tried:

$('#reportList').dataTable({
    "data" : reportData,    
    "columns" : [
        { "title" : "Report Name",
         "data" : "title"
        },
        { "title" : "Date Range",
          "data" : {
              "sort" : "reportDateRangeMilliseconds",
              "display" : "DateRange"
          }
        }
    ]
})

Which doesn't return an error, but also doesn't sort correctly using the milliseconds value. See http://jsfiddle.net/scottglew/jrnou3p3/2/

I've also tried a range of other combinations but haven't had any joy. Can anyone save my sanity?

Answer 1

I finally found a way to achieve this, by creating another hidden column for the milliseconds value, and then I pointed the orderData property of the 'Date Range' column to the hidden column.

$('#reportList').dataTable({
    "data" : reportData,    
    "columns" : [
        { "title" : "Report Name",
         "data" : "title"
        },
        { "title" : "Range In milliseconds",
          "data" : "reportDateRangeMilliseconds",
          "visible" : false
        },
        { "title" : "Date Range",
          "data" : "DateRange",
          "orderData" : [1]
        },
    ]
});

see http://jsfiddle.net/vxshL3ju/1/

But doesn't this defeat the purpose of the new "sort" and "display" properties introduced in DataTables 1.10?

Answer 2

For my needs in a haml file in a Rails app, this is how I achieved viewing and sorting on a human readable distance (feet/meters when near, and miles/kilometers when > 1 mile) via a hidden distance column:

$('.water_supply table').DataTable({
  "order": [[3, "asc"]],
  "paging": true,
  "pageLength": 20,
  // Have the visible distance column (2) sort actually use the
  // (hidden) monotonic distance_in_miles column data (3rd)
  "columnDefs": [
      { "targets": [2],
        "visible": true,
        "orderData": [3]
      },
      { "targets": [3],
        "visible": false,
      }
    ]
});