Our total calculation is off when using a filter! Any ideas?

DoctahSeuss

We're using DataTables for reporting on some finance items, and I've come across the following issue:

We are bringing in the entire recordset via a basic SQL query. This can be filtered by date, or searched. All of that works fine. The issue is with the filtered results not providing an accurate total as the total is the applicable value of the entire recordset. Now the page total works, but that is not the total that we need at the bottom of the data table.

Should we be recoding the report to only use the applicable dates in the SQL query or is there a way to get DataTables to use the filtered results (even if multi-page)?

This would also apply to the CSV and Copy extensions.

Thanks!