Draw 3d: Create New Report Format

Report Title:

Enter a complete SQL query statement against the "TICKET" table:
SELECT CASE WHEN status IN ('Open','Verified') THEN '#f2dcdc' WHEN status='Review' THEN '#e8e8e8' WHEN status='Fixed' THEN '#cfe8bd' WHEN status='Tested' THEN '#bde5d6' WHEN status='Deferred' THEN '#cacae5' ELSE '#c8c8c8' END AS 'bgcolor', substr(tkt_uuid,1,10) AS '#', datetime(tkt_mtime) AS 'mtime', type, status, subsystem, title FROM ticket

Enter an optional color key in the following box. (If blank, no color key is displayed.) Each line contains the text for a single entry in the key. The first token of each line is the background color for that line.
#ffffff Key: #f2dcdc Active #e8e8e8 Review #cfe8bd Fixed #bde5d6 Tested #cacae5 Deferred #c8c8c8 Closed

Optional human-readable description for this report
Markup style:

Make this the default report

TICKET Schema

CREATE TABLE ticket(
  -- Do not change any column that begins with tkt_
  tkt_id INTEGER PRIMARY KEY,
  tkt_uuid TEXT UNIQUE,
  tkt_mtime DATE,
  tkt_ctime DATE,
  -- Add as many fields as required below this line
  type TEXT,
  status TEXT,
  subsystem TEXT,
  priority TEXT,
  severity TEXT,
  foundin TEXT,
  private_contact TEXT,
  resolution TEXT,
  title TEXT,
  comment TEXT
)

Notes

The SQL must consist of a single SELECT statement
If a column of the result set is named "#" then that column is assumed to hold a ticket number. A hyperlink will be created from that column to a detailed view of the ticket.
If a column of the result set is named "bgcolor" then the content of that column determines the background color of the row.
The text of all columns prior to the first column whose name begins with underscore ("_") is shown character-for-character as it appears in the database. In other words, it is assumed to have a mimetype of text/plain.
The first column whose name begins with underscore ("_") and all subsequent columns are shown on their own rows in the table and with wiki formatting. In other words, such rows are shown with a mimetype of text/x-fossil-wiki. This is recommended for the "description" field of tickets.
The query can join other tables in the database besides TICKET.

Examples

In this example, the first column in the result set is named "bgcolor". The value of this column is not displayed. Instead, it selects the background color of each row based on the TICKET.STATUS field of the database. The color key at the right shows the various color codes.

new or active

review

fixed

tested

defer

closed

SELECT
  CASE WHEN status IN ('new','active') THEN '#f2dcdc'
       WHEN status='review' THEN '#e8e8bd'
       WHEN status='fixed' THEN '#cfe8bd'
       WHEN status='tested' THEN '#bde5d6'
       WHEN status='defer' THEN '#cacae5'
       ELSE '#c8c8c8' END as 'bgcolor',
  tn AS '#',
  type AS 'Type',
  status AS 'Status',
  sdate(origtime) AS 'Created',
  owner AS 'By',
  subsystem AS 'Subsys',
  sdate(changetime) AS 'Changed',
  assignedto AS 'Assigned',
  severity AS 'Svr',
  priority AS 'Pri',
  title AS 'Title'
FROM ticket

To base the background color on the TICKET.PRIORITY or TICKET.SEVERITY fields, substitute the following code for the first column of the query:

SELECT
  CASE priority WHEN 1 THEN '#f2dcdc'
       WHEN 2 THEN '#e8e8bd'
       WHEN 3 THEN '#cfe8bd'
       WHEN 4 THEN '#cacae5'
       ELSE '#c8c8c8' END as 'bgcolor',
...
FROM ticket

To see the TICKET.DESCRIPTION and TICKET.REMARKS fields, include them as the last two columns of the result set and given them names that begin with an underscore. Like this:

 SELECT
   tn AS '#',
   type AS 'Type',
   status AS 'Status',
   sdate(origtime) AS 'Created',
   owner AS 'By',
   subsystem AS 'Subsys',
   sdate(changetime) AS 'Changed',
   assignedto AS 'Assigned',
   severity AS 'Svr',
   priority AS 'Pri',
   title AS 'Title',
   description AS '_Description',  -- When the column name begins with '_'
   remarks AS '_Remarks'           -- content is rendered as wiki
 FROM ticket