SQL

Queries

Reports with Datasets

This query returns all the Reports using Datasets in your Workspace, ordered by when the Report was created. You can also, among other things, filter by collection and creator of the Dataset or Report.

SELECT
  distinct r.id AS "report_id",
  r.name AS "report_name",
  r.URL AS "report_url",
  r.created_at_utc AS "report_created_at_utc",
  u.name AS "report_creator_name",
  u.email AS "report_creator_email",
  rc.name as "report_collection_name",
  d.name AS "dataset_name",
  dc.name as "dataset_collection_name",
  d.ID AS "dataset_id",
  d.source_type AS "dataset_source_type"
FROM
  mode.organization_usage.reports r
  JOIN mode.organization_usage.report_dataset rd ON r.id = rd.report_id
  JOIN mode.organization_usage.datasets d ON rd.dataset_id = d.id
  JOIN mode.organization_usage.users u ON r.creator_id = u.id
  JOIN mode.organization_usage.collections dc on d.collection_id = dc.id
  JOIN mode.organization_usage.collections rc on r.collection_id = rc.id
WHERE
  r.state = 'active'
  AND d.STATE = 'active'
  AND rd.deleted_at_utc is null
ORDER BY
  4 desc,1