01
About this report
Every active case whose status is not in a closed stage, grouped by status and priority. Cases are counted by days since the case started, in the bands 0-2 days, 3-7, 8-30 and over 30, with the total.
Click Open cases to open Open support cases filtered to that status and priority. Review the Over 30 days column weekly.
02
Sample preview
Fictional figures in the shape you will see. Yours come from your account.
| Status | Priority | 0-2 days | 3-7 days | 8-30 days | Over 30 days | Open cases |
|---|---|---|---|---|---|---|
| In Progress | Medium | 6 | 9 | 4 | 1 | 20 |
| Not Started | High | 3 | 1 | 0 | 0 | 4 |
| Escalated | High | 0 | 2 | 1 | 1 | 4 |
| Re-Opened | Low | 0 | 0 | 2 | 3 | 5 |
03
The SuiteQL
This is the query each report runs. Read it, copy it, or change it after import.
SELECT x.status,
x.priority,
SUM(CASE WHEN x.age <= 2 THEN 1 ELSE 0 END) AS age_0_2,
SUM(CASE WHEN x.age BETWEEN 3 AND 7 THEN 1 ELSE 0 END) AS age_3_7,
SUM(CASE WHEN x.age BETWEEN 8 AND 30 THEN 1 ELSE 0 END) AS age_8_30,
SUM(CASE WHEN x.age > 30 THEN 1 ELSE 0 END) AS age_over_30,
COUNT(*) AS open_cases
FROM (SELECT NVL(BUILTIN.DF(sc.status), '(none)') AS status,
NVL(BUILTIN.DF(sc.priority), '(none)') AS priority,
TRUNC(SYSDATE) - TRUNC(sc.startdate) AS age
FROM supportcase sc
JOIN supportcasestatus s ON s.id = sc.status
WHERE s.stage <> 'CLOSED'
AND sc.isinactive = 'F') x
GROUP BY x.status, x.priority
ORDER BY open_cases DESCSELECT sc.id AS case__id,
sc.casenumber AS case_number,
sc.title AS subject,
BUILTIN.DF(sc.company) AS company,
NVL(BUILTIN.DF(sc.status), '(none)') AS status,
NVL(BUILTIN.DF(sc.priority), '(none)') AS priority,
BUILTIN.DF(sc.assigned) AS assigned_to,
BUILTIN.DF(sc.origin) AS origin,
TO_CHAR(sc.startdate, 'YYYY-MM-DD') AS start_date,
TRUNC(SYSDATE) - TRUNC(sc.startdate) AS days_open,
TO_CHAR(sc.lastmodifieddate, 'YYYY-MM-DD') AS last_modified
FROM supportcase sc
JOIN supportcasestatus s ON s.id = sc.status
WHERE s.stage <> 'CLOSED'
AND sc.isinactive = 'F'
ORDER BY sc.startdate04