Changes between Version 3 and Version 4 of TracReports


Ignore:
Timestamp:
Jan 3, 2024, 10:40:13 AM (9 months ago)
Author:
trac
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • TracReports

    v3 v4  
    55The Trac reports module provides a simple, yet powerful reporting facility to present information about tickets in the Trac database.
    66
    7 Rather than have its own report definition format, TracReports relies on standard SQL `SELECT` statements for custom report definition. 
     7Rather than have its own report definition format, TracReports relies on standard SQL `SELECT` statements for custom report definition.
    88
    99  '''Note:''' The report module is being phased out in its current form because it seriously limits the ability of the Trac team to make adjustments to the underlying database schema. We believe that the [wiki:TracQuery query module] is a good replacement that provides more flexibility and better usability. While there are certain reports that cannot yet be handled by the query module, we intend to further enhance it so that at some point the reports module can be completely removed. This also means that there will be no major enhancements to the report module anymore.
     
    1717
    1818A report consists of these basic parts:
    19  * '''ID''' — Unique (sequential) identifier 
     19 * '''ID''' — Unique (sequential) identifier
    2020 * '''Title''' — Descriptive title
    2121 * '''Description''' — A brief description of the report, in WikiFormatting text.
     
    2525== Changing Sort Order
    2626
    27 Simple reports - ungrouped reports to be specific - can be sorted by clicking the column header. 
     27Simple reports - ungrouped reports to be specific - can be sorted by clicking the column header.
    2828
    2929If a column header is a hyperlink (red), click the column to sort by it. Clicking the same header again reverses the sort order.
     
    3838
    3939In addition to the HTML view, reports can also be exported in a number of alternate formats.
    40 At the bottom of the report page, you will find a list of available data formats. Click the desired link to 
     40At the bottom of the report page, you will find a list of available data formats. Click the desired link to
    4141download the alternate format.
    4242
     
    7272 * changetime
    7373 * component
    74  * severity 
    75  * priority 
     74 * severity
     75 * priority
    7676 * owner
    7777 * reporter
     
    8989Example: '''All active tickets, sorted by priority and time'''
    9090{{{#!sql
    91 SELECT id AS ticket, status, severity, priority, owner, time AS created, summary 
    92 FROM ticket 
     91SELECT id AS ticket, status, severity, priority, owner, time AS created, summary
     92FROM ticket
    9393WHERE status IN ('new', 'assigned', 'reopened')
    9494ORDER BY priority, time
     
    9797== Advanced Reports: Dynamic Variables
    9898
    99 For more flexible reports, Trac supports the use of ''dynamic variables'' in report SQL statements. 
    100 In short, dynamic variables are ''special'' strings that are replaced by custom data before query execution. Dynamic variables can also be used in the report title and description //(since 1.1.1)//.
     99For more flexible reports, Trac supports the use of ''dynamic variables'' in report SQL statements.
     100In short, dynamic variables are ''special'' strings that are replaced by custom data before query execution. Dynamic variables are entered through the preferences form and the values are autocompleted //(Since 1.3.2)//.
    101101
    102102=== Using Variables in a Query
    103103
    104 The syntax for dynamic variables is simple, any upper case word beginning with '$' is considered a variable.
     104The syntax for dynamic variables is simple, any upper case word beginning with `$` is considered a variable.
    105105
    106106Example:
     
    109109}}}
    110110
    111 To assign a value to $PRIORITY when viewing the report, you must define it as an argument in the report URL, leaving out the leading '$':
     111The value of the dynamic variable can be assigned in the report preferences form.
     112
     113To assign a value to `$PRIORITY` in the URL for a report, leave out the leading `$`:
    112114{{{
    113  http://trac.edgewall.org/reports/14?PRIORITY=high
    114 }}}
    115 
    116 To use multiple variables, separate them with an '&':
     115 https://trac.edgewall.org/reports/14?PRIORITY=high
     116}}}
     117
     118To use multiple variables, separate them with an `&`:
    117119{{{
    118  http://trac.edgewall.org/reports/14?PRIORITY=high&SEVERITY=critical
    119 }}}
     120 https://trac.edgewall.org/reports/14?PRIORITY=high&SEVERITY=critical
     121}}}
     122
     123It is possible to assign a default value to the variable, within a SQL comment:
     124
     125{{{#!sql
     126-- PRIORITY = high
     127
     128SELECT id AS ticket,summary FROM ticket WHERE priority=$PRIORITY
     129}}}
     130
    120131
    121132=== !Special/Constant Variables
    122133
    123 There is one dynamic variable whose value is set automatically (the URL does not have to be changed) to allow practical reports. 
     134There is one dynamic variable whose value is set automatically (the URL does not have to be changed) to allow practical reports.
    124135
    125136 * $USER — Username of logged in user.
     
    140151=== Automatically formatted columns
    141152
    142  * '''ticket''' — Ticket ID number. Becomes a hyperlink to that ticket. 
     153 * '''ticket''' — Ticket ID number. Becomes a hyperlink to that ticket.
    143154 * '''id''' — same as '''ticket''' above when '''realm''' is not set
    144155 * '''realm''' — together with '''id''', can be used to create links to other resources than tickets (e.g. a realm of ''wiki'' and an ''id'' to a page name will create a link to that wiki page)
     
    149160'''Example:'''
    150161{{{#!sql
    151 SELECT id AS ticket, created, status, summary FROM ticket 
     162SELECT id AS ticket, created, status, summary FROM ticket
    152163}}}
    153164
     
    160171Columns whose names begin and end with 2 underscores (e.g. '''`__color__`''') are
    161172assumed to be ''formatting hints'', affecting the appearance of the row.
    162  
     173
    163174 * '''`__group__`''' — Group results based on values in this column. Each group will have its own header and table.
    164175 * '''`__grouplink__`''' — Make the header of each group a link to the specified URL. The URL is taken from the first row of each group.
    165176 * '''`__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.
    166177 {{{#!html
    167 <div style="margin-left:3em">Defaults: 
     178<div style="margin-left:3em">Defaults:
    168179<span style="border: none; color: #333; background: transparent;  font-size: 85%; background: #fdc; border-color: #e88; color: #a22">Color 1</span>
    169180<span style="border: none; color: #333; background: transparent;  font-size: 85%; background: #ffb; border-color: #eea; color: #880">Color 2</span>
     
    184195     t.id AS ticket, summary
    185196FROM ticket t,enum p
    186 WHERE t.status IN ('new', 'assigned', 'reopened') 
     197WHERE t.status IN ('new', 'assigned', 'reopened')
    187198  AND p.name = t.priority AND p.type = 'priority'
    188199ORDER BY t.milestone, p.value, t.severity, t.time
     
    207218SELECT p.value AS __color__,
    208219       t.milestone AS __group__,
    209        (CASE owner 
    210           WHEN 'daniel' THEN 'font-weight: bold; background: red;' 
     220       (CASE owner
     221          WHEN 'daniel' THEN 'font-weight: bold; background: red;'
    211222          ELSE '' END) AS __style__,
    212223       t.id AS ticket, summary AS summary_,             -- ## Break line here
     
    216227       changetime AS _changetime, reporter AS _reporter -- ## Hidden from HTML output
    217228FROM ticket t,enum p
    218 WHERE t.status IN ('new', 'assigned', 'reopened') 
     229WHERE t.status IN ('new', 'assigned', 'reopened')
    219230  AND p.name = t.priority AND p.type = 'priority'
    220231ORDER BY t.milestone, p.value, t.severity, t.time
     
    242253-- ## 4: Assigned, Active Tickets by Owner ## --
    243254
    244 -- 
     255--
    245256-- List assigned tickets, group by ticket owner, sorted by priority.
    246 -- 
     257--
    247258
    248259SELECT p.value AS __color__,
     
    307318
    308319----
    309 See also: TracTickets, TracQuery, [http://www.sqlite.org/lang_expr.html Query Language Understood by SQLite]
     320See also: TracTickets, TracQuery, [https://www.sqlite.org/lang_expr.html Query Language Understood by SQLite]