Advanced Report Usage Notes: Always Include a Date Filter

To avoid creating a report that might try to work on the entire database you should always include some kind of date filter.

The recommended option is to use the built-in report variables $REPORT_PERIOD_START and $REPORT_PERIOD_END to filter data. If an advanced report contains these variables, the web UI will include date pickers when the user runs the report. A standard filter to find all transfers that were active at any time during the report period would look like the following:

SELECT DISTINCT
	ts.id
	, ts.contact
	, ts.bytes_transferred

FROM
	$TBL_TRANSFER_SESSIONS ts

WHERE
	ts.started_at < '$REPORT_PERIOD_END'
	AND (
		ts.stopped_at >= '$REPORT_PERIOD_START'
		OR ts.stopped_at IS NULL
	);

Note the clause OR ts.stopped_at IS NULL. Without this, the report would exclude any transfers that were still running at the time the report was run. Depending on the intended purpose of the report, you might need to prorate data for transfers that were active for only part of the reporting period, such as cases 2, 3, and 4 in the following:

As an alternative, you can avoid the use of $REPORT_PERIOD_START and $REPORT_PERIOD_END if you are creating a report that always looks at the last X hours:

SET @min_start = DATE_SUB(NOW(), INTERVAL 24 HOUR);
CREATE TABLE $FINAL_RESULT_TABLE  
SELECT DISTINCT ts.*
FROM
	$TBL_TRANSFER_SESSIONS ts
WHERE
	ts.started_at >= @min_start
;
Note: As of Console 1.6 there is a bug in the report engine that causes the creation of Excel/CSV files to fail if you do not reference $REPORT_PERIOD_START and $REPORT_PERIOD_END at all. To work around this, include a dummy reference in the report SQL. For example:
SET @dummy = '$REPORT_PERIOD_START';

When running the report, users are then asked for report period dates, but they will be ignored.