| 51 | ---- |
| 52 | |
| 53 | == Advanced Reports: Custom Formatting == |
| 54 | Trac is also capable of more advanced reports, including custom layouts, |
| 55 | result grouping and user-defined CSS styles. To create such reports, we'll use |
| 56 | specialized SQL statements to control the output of the Trac report engine. |
| 57 | |
| 58 | == Special Columns == |
| 59 | To format reports, TracReports looks for 'magic' column names in the query |
| 60 | result. These 'magic' names are processed and affect the layout and style of the |
| 61 | final report. |
| 62 | |
| 63 | === Automatically formatted columns === |
| 64 | * '''ticket''' -- Ticket ID number. Becomes a hyperlink to that ticket. |
| 65 | * '''created, modified, date, time''' -- Format cell as a date and/or time. |
| 66 | * '''description''' -- Ticket description field, parsed through the wiki engine. |
| 67 | |
| 68 | '''Example:''' |
| 69 | {{{ |
| 70 | SELECT id as ticket, created, status, summary FROM ticket |
| 71 | }}} |
| 72 | |
| 73 | === Custom formatting columns === |
| 74 | Columns whose name begins and ends with '__' (Example: '''__color__''') are |
| 75 | assumed to be ''formatting hints'', affecting the appearance of the row. |
| 76 | |
| 77 | * '''___group___''' -- Group results based on values in this column. Each group will have its own header and table. |
| 78 | * '''___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. |
| 79 | * '''___style___''' --- A custom CSS style expression to use for the current row. |
| 80 | |
| 81 | '''Example:''' ''List active tickets, grouped by milestone, colored by priority'' |
| 82 | {{{ |
| 83 | SELECT p.value AS __color__, |
| 84 | t.milestone AS __group__, |
| 85 | (CASE owner WHEN 'daniel' THEN 'font-weight: bold; background: red;' ELSE '' END) AS __style__, |
| 86 | t.id AS ticket, summary |
| 87 | FROM ticket t,enum p |
| 88 | WHERE t.status IN ('new', 'assigned', 'reopened') |
| 89 | AND p.name=t.priority AND p.type='priority' |
| 90 | ORDER BY t.milestone, p.value, t.severity, t.time |
| 91 | }}} |
| 92 | |
| 93 | '''Note:''' A table join is used to match ''ticket'' priorities with their |
| 94 | numeric representation from the ''enum'' table. |
| 95 | |
| 96 | === Changing layout of report rows === |
| 97 | By default, all columns on each row are display on a single row in the HTML |
| 98 | report, possibly formatted according to the descriptions above. However, it's |
| 99 | also possible to create multi-line report entries. |
| 100 | |
| 101 | * '''column_''' -- ''Break row after this''. By appending an underscore ('_') to the column name, the remaining columns will be be continued on a second line. |
| 102 | |
| 103 | * '''_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. |
| 104 | |
| 105 | * '''_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). |
| 106 | |
| 107 | '''Example:''' ''List active tickets, grouped by milestone, colored by priority, with description and multi-line layout'' |
| 108 | |
| 109 | {{{ |
| 110 | SELECT p.value AS __color__, |
| 111 | t.milestone AS __group__, |
| 112 | (CASE owner |
| 113 | WHEN 'daniel' THEN 'font-weight: bold background: red;' |
| 114 | ELSE '' END) AS __style__, |
| 115 | t.id AS ticket, summary AS summary_, -- ## Break line here |
| 116 | component,version, severity, milestone, status, owner, |
| 117 | time AS created, changetime AS modified, -- ## Dates are formatted |
| 118 | description AS _description_, -- ## Uses a full row |
| 119 | changetime AS _changetime, reporter AS _reporter -- ## Hidden from HTML output |
| 120 | FROM ticket t,enum p |
| 121 | WHERE t.status IN ('new', 'assigned', 'reopened') |
| 122 | AND p.name=t.priority AND p.type='priority' |
| 123 | ORDER BY t.milestone, p.value, t.severity, t.time |
| 124 | }}} |
| 125 | |
| 126 | |
| 127 | |
| 128 | ---- |