Create Report

Types of Report

There are two types of report, manual and scheduled - both can be run from the dashboard whenever you require, in addition: Scheduled reports will also be emailed to a list of recipients at a preset interval. Manual reports can utilise parameters which allow you to filter the report results before it runs.

Creating Report

  1. Click the 'Create New' button at the top of the main reports page.

  2. Enter the report name and description.

  3. Upload your report file, this must only be a Select query and must be a .sql file.

  4. Select the report type.

  5. If you selected manual, you can now click the Create button.

  6. If you selected scheduled a further section will appear - this enables configuring of the schedule and recipients.

  7. Configure the schedule (see configuration for more info)

  8. Enter recipients emails separated by commas.

  9. Click `Create`

Edit Report

  1. Click the actions dropdown against the report you need to edit and select Edit.

  2. Make the changes you require - you can also deactivate the report.

  3. Click Update once you're done

SQL Format

Reports must be written in MySQL compatible SQL and only include SELECT queries. Reports can utilise dynamic parameters (manual reports only)

SQL Parameters

Parameters can only be present in WHERE clauses and must be wrapped in a double percentage as below:

SELECT
    SUM(quoteTotal) AS "Quote Value"
FROM
    quoteHeaders
WHERE
    quoteStatus = %%quoteHeaders.quoteStatus%%
    AND quoteTotal > %%quoteHeaders.quoteTotal%%

Parameters should take up the format of %%TABLENAME.FIELDNAME%% where this is the table/field used in the clause. At runtime, this filter is used to give the user a list of available options.

Schedule Configuration

The schedule system utilises cron syntax to set schedules, this system creates a easy to use layer over this to allow schedules to be created easily.

The use of the interval field varies depending on the frequency field, if the frequency is hour, the interval is how many hours between runs.

If the frequency is days - the interval becomes day of week with 0 being Sunday, 1 being Monday, 2 being Tuesday and so on - Selecing days also requires a time of day to be selected, this is the time on the selected day of week that the report will run.

If the frequency is month - the interval becomes the day of month with the time of day remaining the same as days.

Note that while the result may say something like every 2 hours - it'll actually run every second hour after midnight i.e 2am, 4am etc So to create a report running daily at 5:00 - select hour and set interval to 17.

Notes

  • Only system admins can create reports for security reasons and creating reports requires knowledge of SQL programming language.

  • When Editing a report, the report type cannot be changed.

PreviousModulesNextRun Reports

Last updated