Changes between Version 1 and Version 2 of TracReports

Timestamp:

Mar 11, 2004, 12:45:29 AM (20 years ago)

Author:

daniel

Comment:

—

TracReports

@@ -2 +2 @@
 The Trac reports module provides a simple, yet powerful reporting facility
 for presenting information about tickets from the Trac database.
+
+Rather than have its own report format, TracReports relies on standard SQL
+SELECT statements for custom report definition. 
 
 == Creating Custom Reports ==
@@ -15 +18 @@
 
 == Ticket columns ==
-The '''ticket'' table has the following columns:
+The ''ticket'' table has the following columns:
  * id
  * time
@@ -35 +38 @@
 See TracTickets for a detailed description of the column fields.
 
-== Special Columns ==
-To format the report properly, Trac needs to know the meaning of some result
-columns . This is a list of column names of special meaning to Trac:
- * '''ticket''' -- Ticket ID number. Will become a hyperlink to that ticket.
+'''all active tickets, sorted by priority and time'''
 
-''Note: In upcoming releases, there will be more special columns added, to create color-coded reports, grouping and other nifty features.''
+'''Example:''' ''All active tickets, sorted by priority and time''
+{{{
+SELECT id AS ticket, status, severity, priority, owner, 
+       time as created, summary FROM ticket 
+  WHERE status IN ('new', 'assigned', 'reopened')
+  ORDER BY priority, time
+}}}
 
 
+----
+
+== Advanced Reports: Custom Formatting ==
+Trac is also capable of more advanced reports, including custom layouts,
+result grouping and user-defined CSS styles. To create such reports, we'll use
+specialized SQL statements to control the output of the Trac report engine.
+
+== Special Columns ==
+To format reports, TracReports looks for 'magic' column names in the query
+result. These 'magic' names are processed and affect the layout and style of the 
+final report.
+
+=== Automatically formatted columns ===
+ * '''ticket''' -- Ticket ID number. Becomes a hyperlink to that ticket. 
+ * '''created, modified, date, time''' -- Format cell as a date and/or time.
+ * '''description''' -- Ticket description field, parsed through the wiki engine.
+
+'''Example:'''
+{{{
+SELECT id as ticket, created, status, summary FROM ticket 
+}}}
+
+=== Custom formatting columns ===
+Columns whose name begins and ends with '__' (Example: '''__color__''') are
+assumed to be ''formatting hints'', affecting the appearance of the row.
+ 
+ * '''___group___''' -- Group results based on values in this column. Each group will have its own header and table.
+ * '''___color___''' -- Should be a numeric value ranging from 1 to 5 to select a pre-defined row color. Typically used to color rows by issue priority.
+ * '''___style___''' --- A custom CSS style expression to use for the current row. 
+
+'''Example:''' ''List active tickets, grouped by milestone, colored by priority''
+{{{
+SELECT p.value AS __color__,
+     t.milestone AS __group__,
+     (CASE owner WHEN 'daniel' THEN 'font-weight: bold; background: red;' ELSE '' END) AS __style__,
+       t.id AS ticket, summary
+  FROM ticket t,enum p
+  WHERE t.status IN ('new', 'assigned', 'reopened') 
+    AND p.name=t.priority AND p.type='priority'
+  ORDER BY t.milestone, p.value, t.severity, t.time
+}}}
+
+'''Note:''' A table join is used to match ''ticket'' priorities with their
+numeric representation from the ''enum'' table.
+
+=== Changing layout of report rows ===
+By default, all columns on each row are display on a single row in the HTML
+report, possibly formatted according to the descriptions above. However, it's
+also possible to create multi-line report entries.
+
+ * '''column_''' -- ''Break row after this''. By appending an underscore ('_') to the column name, the remaining columns will be be continued on a second line.
+
+ * '''_column_''' -- ''Full row''. By adding an underscore ('_') both at the beginning and the end of a column name, the data will be shown on a separate row.
+
+ * '''_column'''  --  ''Hide data''. Prepending an underscore ('_') to a column name instructs Trac to hide the contents from the HTML output. This is useful fore information to be visible only if downloaded in other formats (like CSV or RSS/XML).
+
+'''Example:''' ''List active tickets, grouped by milestone, colored by priority, with  description and multi-line layout''
+
+{{{
+SELECT p.value AS __color__,
+       t.milestone AS __group__,
+       (CASE owner 
+          WHEN 'daniel' THEN 'font-weight: bold background: red;' 
+          ELSE '' END) AS __style__,
+       t.id AS ticket, summary AS summary_,             -- ## Break line here
+       component,version, severity, milestone, status, owner,
+       time AS created, changetime AS modified,         -- ## Dates are formatted
+       description AS _description_,                    -- ## Uses a full row
+       changetime AS _changetime, reporter AS _reporter -- ## Hidden from HTML output
+  FROM ticket t,enum p
+  WHERE t.status IN ('new', 'assigned', 'reopened') 
+    AND p.name=t.priority AND p.type='priority'
+  ORDER BY t.milestone, p.value, t.severity, t.time
+}}}
+
+
+
+----
 
 == Sample Reports ==