Q:

SharePoint list as data source for jQuery DataTables plugin

I trying to create a table using a SharePoint list as source. I followed this tutorial and it worked perfectly. But this method would not work if you need to reload the data using, let's say a drop down list. This is what I have tried so far:
JS:
theYear = $('#dropdown').val(); // Selected Year
theRO = $('#dropdownRO').val(); // Selected RO
oTable = $('#example').dataTable({
"sAjaxSource": "https:xxx-xxxx/_api/web/lists/getByTitle('Consolidated%20LC%20Report')/items()?$filter=Fiscal_x0020_Year%20eq%20'" + theYear + "' and Regional_x0020_Office eq '" + theRO + "'&$orderby=Id&$select=Id,Title,Fiscal_x0020_Year,Notices_x0020_Received,Declined_x0020_Participation,Selected_x0020_Field_x0020_Revie,Selected_x0020_File_x0020_Review,Pending,Pending_x0020_Previous_x0020_Yea,Controversial,GFP_x0020_Reviews,NAD_x0020_Appeals,Mediation_x0020_Cases,Monthly_x0020_Cost_x0020_Savings,Monthly_x0020_Expenditure,Regional_x0020_Office,Month_Number",
    "sAjaxDataProp": "data",
    "aoColumns": [{
    "mData": "Id",
    "bVisible": false
}, //Invisible column
{
    "mData": "Title"
}, {
    "mData": "Notices_x0020_Received"
}, {
    "mData": "Declined_x0020_Participation"
}, {
    "mData": "Selected_x0020_Field_x0020_Revie"
}, {
    "mData": "Selected_x0020_File_x0020_Review"
}, {
    "mData": "Pending"
}, {
    "mData": "Pending_x0020_Previous_x0020_Yea"
}, {
    "mData": "Controversial"
}, {
    "mData": "GFP_x0020_Reviews"
}, {
    "mData": "NAD_x0020_Appeals"
}, {
    "mData": "Mediation_x0020_Cases"
}, {
    "mData": "Monthly_x0020_Cost_x0020_Savings",
    "fnRender": function (obj, val) {
        return accounting.formatMoney(val);
    }
}, {
    "mData": "Monthly_x0020_Expenditure",
    "fnRender": function (obj, val) {
        return accounting.formatMoney(val);
    }
}],
    "aLengthMenu": [
    [25, 50, 100, 200, -1],
    [25, 50, 100, 200, "All"]
],
    "iDisplayLength": -1, //Number of rows by default. -1 means All Records
"sPaginationType": "full_numbers",
    "aaData": data.d.results,
    "bJQueryUI": false,
    "bProcessing": true,
"bServerSide": true,
    "bDeferRender": true,
"bRetrieve": true,
"bInfo": true,
"bAutoWidth": true,
"bDestroy": true,
"bFilter": true,
    "drawCallback": function (settings) {
    alert('DataTables has redrawn the table');
},
    "sDom": 'T&;"clear"&;frtip',
    "oTableTools": {
    "aButtons": ["xls"],
        "sSwfPath": "../../Style Library/js/datatables/TableTools/media/swf/copy_csv_xls_pdf.swf",
},
    "sSearch": "Filter",
    "fnDrawCallback": function () {
    //Add totals row
    var Columns = $("#example > tbody").find("> tr:first > td").length;
    $('#example tr:last').after('<tr><td  class="total"></td><td  class="total"></td><td  class="total"></td><td  class="total"></td><td  class="total"></td><td  class="total"></td><td  class="total"></td><td  class="total"></td><td  class="total"></td><td  class="total"></td><td  class="total"></td><td  class="total"></td><td  class="total"></td><td  class="total"></td></tr>');
    //Formating the Total row number to no decimals
    $("#example tr:last td:not(:first,:last)").text(function (i) {
        var t = 0;
        $(this).parent().prevAll().find("td:nth-child(" + (i + 2) + ")").each(function () {
            t += parseFloat($(this).text().replace(/[\$,]/g, '') * 1);
        });
        return parseInt(t * 100, 10) / 100;
    });
    //Format the monthly expenditure and savings to currency formatFormating the currency
    var cell = new Array();
    cell[0] = $('#example tr:last td:nth-child(12)').text();
    cell[1] = $('#example tr:last td:nth-child(13)').text();
    $('#example tr:last').find('td:nth-child(12)').html(accounting.formatMoney(cell[0]));
    $('#example tr:last').find('td:nth-child(13)').html(accounting.formatMoney(cell[1]));
    $('#example tr:last').find('td:last').hide();
} //hides extra td that was showing
}); //End of Datatable()

Any ideas? Thanks!

A:

I was able to get something working with the following (you will need to adapt to your situation).  The key thing was the .destroy(), which allows you to reinitialize the DataTable.
HTML:
<select id='ddlStatus'>
    <option value='Inserted'>Inserted</option>
    <option value='Approved'>Approved</option>
    <option value='Shipped'>Shipped</option>
    <option value='Completed'>Completed</option>
</select>
<table id="example" class="display" cellspacing="0" width="100%">
    <thead>
        <tr>
            <th>Order</th>
            <th>Order ID</th>
            <th>Order Status</th>
            <th>Customer ID</th>
        </tr>
    </thead>
    <tfoot>
        <tr>
            <th>Order</th>
            <th>Order ID</th>
            <th>Order Status</th>
            <th>Customer ID</th>
        </tr>
    </tfoot>
</table>

JavaScript:
var dataTableExample = 'undefined';
$(document).ready(function () {
    $('#ddlStatus').change(function (event) {
        loadDataTable();
        event.preventDefault();
    });
    loadDataTable();
});

function loadDataTable() {
    var status = $('#ddlStatus').val();
    var oDataUrl = "<site>/_api/web/lists/getbytitle('OrdersLibrary')/items?$select=Title,OrderID,OrderStatus,CustomerID&$filter=OrderStatus eq '" + status + "'";
    $.ajax({
        url: oDataUrl,
        type: "GET",
        dataType: "json",
        headers: {
            "accept": "application/json;odata=verbose"
        },
        success: mySuccHandler,
        error: myErrHandler
    });
}

function mySuccHandler(data) {
    if(dataTableExample != 'undefined') {
        dataTableExample.destroy();
    }
    dataTableExample = $('#example').DataTable({
        "aaData": data.d.results,
            "aoColumns": [{
            "mData": "Title"
        }, {
            "mData": "OrderID"
        }, {
            "mData": "OrderStatus"
        }, {
            "mData": "CustomerID"
        }]
    });
}
function myErrHandler(data, errCode, errMessage) {
    alert("Error: " + errMessage);
}

