SQL editor

Open Reports > New report and use the Query tab. A template can supply a starting query.

Find datasets and columns

The Datasets list shows synced datasets and their columns. Use Search…, select a dataset to expand it, then use its insert button to put its exact name in the editor. For example, Course Access inserts as CourseAccess. Columns marked PII contain personal information. If a needed dataset is not synced, select it and run a sync.

Open larger image

Write and run a query

Type a query, then select Run on the bar above results or press Ctrl+Enter. For example:

SELECT OrgUnitId, COUNT(DISTINCT UserId) AS Visitors
FROM CourseAccess
GROUP BY OrgUnitId

Results appear below the editor with a row count and duration. While the query is running, Run changes to a greyed Running… with Cancel beside it. Select that Cancel to stop the query. If results were showing before the run, they return with their row count and duration. On the Chart tab, these controls appear under the query progress box. They disappear when the query finishes, even if chart ideas or the chart preview are still loading. Check the results before publishing; running does not publish or save the query. If a successful run removes a chart that no longer fits the query, that removal is saved in your private draft. Query edits autosave independently.

The table loads rows as you scroll. Use Search… to match the displayed text across all columns in all result rows, a column heading to sort, and Columns to change which columns are shown. Enter text in Search…, then select Search or press Enter. To clear an applied search, empty the field and select Search or press Enter again. Select the same heading again for descending order, then again to clear the sort. The row count shows matching rows out of the total when a search narrows results. Scroll through the table to load more rows in the current search and sort. Search and sort use the same progress card, busy retry, failure and Cancel states as report tables, with dimmed rows and disabled column sort buttons. In Export, select Download CSV (all rows) to download every row in the current view, with the completed search and sort applied and only the visible columns.

Large results use the preparation progress and recovery controls. Numbers display as stored, keeping decimal scale, such as 12.30, without thousands separators. Dates and times are saved once in the Organization timezone and shown as saved; result downloads use the same saved values as the grid. Charts use the whole result even when the table has a search applied.

If Run returns no rows, a plain sentence appears above the empty result. If the Run used an input ({{param}}), it says No rows match the inputs you chose. Try different inputs. If the Run used no input, it says This report has no rows in the synced data. A Run with rows shows no sentence.

Your report results can include records and columns that a share hides from someone else. Check the report data overrides and course boundary, then use Test as a person to check a recipient’s access. Widget numbers follow their metric and course rules, with report column and personal column restrictions still able to narrow them.

Open larger image

What a query may contain

Use one statement starting with SELECT or WITH, reading synced datasets as tables. File paths, web addresses and system functions are refused. Use {{name}} for a changeable parameter. Include OrgUnitId in a report intended for an insight widget so results can be tied to a course.

For a widget that shows each viewer’s own records, the report also needs UserId.

When a query fails

Read the reason under the editor and correct the query or input it names. Go to line moves to a located SQL error; the editor also underlines it. Use the dataset browser to insert a misspelled table or column name. If the dataset is unavailable, select and sync it before running again. A timeout calls for a narrower date range or course; a busy or interrupted run offers Run again. Only a successful run supplies results to publish.

Coming from SQL Server

Rowseta SQL is not T-SQL and cannot be switched to it. When moving a query from SQL Server:

• Use one statement starting with SELECT or WITH.
• Do not use temp tables (#temp), DROP or SELECT INTO. Use a WITH step instead.
• Return one result per report. Split multiple result sets into separate reports.

SQL Server	Rowseta SQL
SELECT TOP 10	Put LIMIT 10 at the end of the query.
WHERE rn = 1 after ROW_NUMBER()	Use QUALIFY ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) = 1 on the same query.
OUTER APPLY (SELECT TOP 1 ... WHERE child.key = outer.key ORDER BY ...)	Rank the child rows in a WITH step using QUALIFY ROW_NUMBER() OVER (PARTITION BY key ORDER BY ...) = 1, then LEFT JOIN that step to the outer table ON the key.
CROSS APPLY / OUTER APPLY	Use a JOIN (or LEFT JOIN) on a WITH step. For a correlated top-one lookup, rank the child rows first as above.
a + b for text	a || b
ISNULL(x, 0)	coalesce(x, 0)
CHARINDEX('@', Email)	strpos(Email, '@')
LEFT(Email, CHARINDEX('@', Email) - 1)	split_part(Email, '@', 1)
nvarchar(100)	VARCHAR
IsActive = 1 on a bit column	CAST(IsActive AS BOOLEAN), or IsActive = true when the column is already true/false.
[Org Unit]	"Org Unit"
GETDATE()	now()
DATEADD(day, 7, d)	d + INTERVAL 7 DAY
DATEDIFF(day, a, b)	date_diff('day', a, b)
FORMAT(d, 'yyyy-MM-dd')	strftime(d, '%Y-%m-%d')
IIF(x > 0, 'Yes', 'No')	CASE WHEN x > 0 THEN 'Yes' ELSE 'No' END
TRY_CONVERT(int, x)	TRY_CAST(x AS INTEGER)

For a correlated lookup, use the same key in PARTITION BY and the join condition. For example, rank CourseAccess rows by DayAccessed DESC within each UserId, then join to Users on UserId. LEFT JOIN keeps users with no access row, with NULL child values; JOIN drops them. If two outer rows share a key, both keep the one ranked child row. Add another ordering column when dates can tie and you need a specific row.

split_part returns the whole value when it has no @. The SQL Server LEFT form above fails for that value.

Example: a temp table becomes a WITH step

SQL Server version:

SELECT UserId, OrgUnitId, DayAccessed
INTO #recent
FROM CourseAccess
WHERE DayAccessed >= '2026-10-01';

SELECT recent.OrgUnitId, recent.UserId, users.FirstName, recent.DayAccessed
FROM #recent AS recent
JOIN Users AS users ON users.UserId = recent.UserId
ORDER BY recent.OrgUnitId, recent.UserId, recent.DayAccessed;

Rowseta SQL version:

WITH recent AS (
  SELECT UserId, OrgUnitId, DayAccessed
  FROM CourseAccess
  WHERE DayAccessed >= DATE '2026-10-01'
)
SELECT recent.OrgUnitId, recent.UserId, users.FirstName, recent.DayAccessed
FROM recent
JOIN Users AS users ON users.UserId = recent.UserId
ORDER BY recent.OrgUnitId, recent.UserId, recent.DayAccessed

Coming from MySQL

MySQL	Rowseta SQL
`name`	"name"
DATE_FORMAT(d, '%Y-%m-%d')	strftime(d, '%Y-%m-%d')
DATE_ADD(d, INTERVAL 7 DAY)	d + INTERVAL 7 DAY
IFNULL(x, 0)	Works as is, or use coalesce(x, 0).
GROUP BY with columns missing from the list	List every non-aggregated column, or use GROUP BY ALL.
LIMIT 20, 10	LIMIT 10 OFFSET 20

PostgreSQL queries mostly work as they are.

Related

Report builder and run a report.