Skip to main content

Reports 📜

Reporting for Admins​

Each hub has a list of standard reports which can be run and downloaded via the Reports page in the Admin Dashboard.

Some clients require custom reports which are run and integrated based on their requirements.

Reports page in admin dashboard

Reporting for Team Managers​

If the "Manager Reports" feature flag is enabled, team managers can also access team-specific reports via the My teams page in the hub, by selecting the team they want to check the reports on.

Team reports are a selection of all the reports available in the reports dashboard.

Team reports page in hub

Team reports in the reports table

If a report is supposed to be available for managers as a team report, the report must be stored in the reports table in the database with the manager_report field set to 1.

Additionally the method_vars field for the report must also contain a team_filter, as manager reports must filter by team.

Overview of differing use of fields depending on report audience:

Report audiencemethod_varsmanger_report
Team managermust contain team_filter1
Adminoptional1 or 0

Reports in the database​

All reports are stored in the database in the reports table.

The required fields differ, depending on the technical report type (SQL or Dare).

Overview of differing use of fields depending on technical report type:

Report typemethodmethod_varsquery
SQLrequiredoptionalNULL
DareNULLoptionalrequired

Dare reports​

Simple reports written in Dare are found in the reports folder under tools/db-migrate/reports. The queries for these reports are stored in the database in the reports table in the respective query column.

Adding a Dare report​

To add a new Dare report, the method is added under tools/db-migrate/reports.

Then a migration is added which inserts the new report into the reports table, referencing the report's query and any further information.

Example migration for adding a report:

const Runner = require('../runner');

const queries = {
	up: `INSERT INTO reports (name, query) values
('Security Audit', '${newReportQuery}')
`,
	down: `DELETE FROM reports WHERE name = 'Security Audit'`,
};

module.exports = new Runner(queries);

Updating a Dare report​

To update a one of these reports, make the required changes within the yml file.

Then a migration must be written and run in order to update the report in the database.

Example migration for updating a report:

const Runner = require('../runner');
const reportsUpdate = require('../reports');

  const q = reportsUpdate('User_Sessions');

  module.exports = new Runner(q);

SQL & Custom reports​

Custom reports and more complex reports which would take too long to run when written in Dare or which are too complex are written in SQL and stored under api/db/methods/reports.

The queries for these reports are not stored in the database. Instead, the report method is referenced in the respective method column and the required method variables are stored in the method_var column.

Adding a SQL report​

To add a new SQL report, the method is added and then exported via [api/db/methods/reports/index.js](https://github.com/5app/hub/tree/main/api/db/methods/reports/index.js). The exported name is then used in a [migration](/docusaurus/cd898962d0395e426bc810b3e8e614746118b5ba/docs/dev-docs/database/migrations) adding the new report to the reports` table, referencing the report's method and any further information.

Example migration for adding a report:

const Runner = require('../runner');

const queries = {
	up: `INSERT INTO reports (name, description, category, method, method_vars) values
('Security Audit', 'Audit of all attempted log in and log out events', 'Miscellaneous','security_events', 'start_date,end_date')
`,
	down: `DELETE FROM reports WHERE name = 'Security Audit'`,
};

module.exports = new Runner(queries);

Converting a Dare report to a SQL report​

When converting a Dare report to a SQL report, the new method is added and the old Dare report is removed. Then a migration updating the reports table entry must be added, which clears the report's querycolumn and updates themethod and method_var columns.

Example migration for updating and converting a Dare report to SQL:

const Runner = require('../runner');

# New Asset list report method to be found under `api/db/methods/reports/assetList.js`

const oldReportQuery = `
table: commonAsset
query:
  fields:
    Asset ID: id
    Name: name
  join:
    rating:
      rated_like: true
  filter:
    type: ['upload', 'web', 'zip', 'scorm']
  orderby: name
  groupby: id
  includeDeletedUsers: true
`;

const q = [
	{
		up: `
			UPDATE reports
			SET	method = 'asset_list', query = NULL
      WHERE	name = 'Asset list'
		`,
		down: `
			UPDATE reports
			SET	method = NULL, query = '${oldReportQuery}'
			WHERE	name = 'Asset list'
		`,
	},
];

module.exports = new Runner(q);

Updating a SQL report​

To update the report method, the method file itself is edited.

A migration only needs to be added if the method variables or other fields referenced in the respective reports table are changed. Pure method changes require no migration as the database merely references the method and does not contain the actual query (as it is the case with the Dare reports).

Further resources​