Changes between Version 1 and Version 2 of TracReports


Ignore:
Timestamp:
01/24/08 16:32:57 (11 years ago)
Author:
trac
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • TracReports

    v1 v2  
    66 
    77Rather than have its own report definition format, TracReports relies on standard SQL 
    8 SELECT statements for custom report definition. 
     8`SELECT` statements for custom report definition.  
     9 
     10  '''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.'' 
     11 
     12  ''You can already completely replace the reports module by the query module simply by disabling the former in [wiki:TracIni trac.ini]:'' 
     13  {{{ 
     14  [components] 
     15  trac.ticket.report.* = disabled 
     16  }}} 
     17  ''This will make the query module the default handler for the “View Tickets” navigation item. We encourage you to try this configuration and report back what kind of features of reports you are missing, if any.'' 
     18 
     19  '''''You will almost definitely need to restart your httpd at this point.''''' 
    920 
    1021A report consists of these basic parts: 
    11  * ID -- Unique (sequential) identifier 
    12  * Title  -- Descriptive title 
    13  * Description  -- A brief description of the report, in WikiFormatting text. 
    14  * Report Body -- List of results from report query, formatted according to the methods described below. 
    15  * Footer -- Links to alternative download formats for this report. 
    16  
     22 * '''ID''' -- Unique (sequential) identifier  
     23 * '''Title'''  -- Descriptive title 
     24 * '''Description'''  -- A brief description of the report, in WikiFormatting text. 
     25 * '''Report Body''' -- List of results from report query, formatted according to the methods described below. 
     26 * '''Footer''' -- Links to alternative download formats for this report. 
    1727 
    1828== Changing Sort Order == 
    19 Simple reports - ungrouped reports to be specific - can be changed to be sorted by any column simply by clicking the column header. 
     29Simple reports - ungrouped reports to be specific - can be changed to be sorted by any column simply by clicking the column header.  
    2030 
    2131If a column header is a hyperlink (red), click the column you would like to sort by. Clicking the same header again reverses the order. 
    2232 
    23  
    24 == Alternate Download Formats == 
    25 Aside from the default HTML view, reports can also be exported in a number of alternate formats. 
    26 At the bottom of the report page, you will find a list of available data formats. Click the desired link to 
    27 download the alternate report format. 
     33== Navigating Tickets == 
     34Clicking on one of the report results will take you to that ticket. You can navigate through the results by clicking the ''Next Ticket'' or ''Previous Ticket'' links just below the main menu bar, or click the ''Back to Report'' link to return to the report page. 
     35 
     36You can safely edit any of the tickets and continue to navigate through the results using the Next/Previous/Back to Report links after saving your results, but when you return to the report, there will be no hint about what has changed, as would happen if you were navigating a list of tickets obtained from a query (see TracQuery#NavigatingTickets). ''(since 0.11)'' 
     37 
     38== Alternative Download Formats == 
     39Aside from the default HTML view, reports can also be exported in a number of alternative formats. 
     40At the bottom of the report page, you will find a list of available data formats. Click the desired link to  
     41download the alternative report format. 
    2842 
    2943=== Comma-delimited - CSV (Comma Separated Values) === 
    3044Export the report as plain text, each row on its own line, columns separated by a single comma (','). 
    31 '''Note:''' Column data is stripped from carriage returns, line feeds and commas to preserve structure. 
     45'''Note:''' Carriage returns, line feeds, and commas are stripped from column data to preserve the CSV structure. 
    3246 
    3347=== Tab-delimited === 
     
    3549 
    3650=== RSS - XML Content Syndication === 
    37 All reports support syndication using XML/RSS 2.0. To subscribe to a , click the the orange 'XML' icon at the bottom of the page. See TracRss for general information on RSS support in Trac. 
    38  
    39 ---- 
     51All reports support syndication using XML/RSS 2.0. To subscribe to an RSS feed, click the orange 'XML' icon at the bottom of the page. See TracRss for general information on RSS support in Trac. 
     52 
     53---- 
     54 
    4055== Creating Custom Reports == 
    4156 
     
    5267The ''ticket'' table has the following columns: 
    5368 * id 
     69 * type 
    5470 * time 
    5571 * changetime 
    5672 * component 
    57  * severity 
    58  * priority 
     73 * severity   
     74 * priority  
    5975 * owner 
    6076 * reporter 
    6177 * cc 
    62  * url 
    6378 * version 
    6479 * milestone 
     
    6782 * summary 
    6883 * description 
     84 * keywords 
    6985 
    7086See TracTickets for a detailed description of the column fields. 
     
    7490'''Example:''' ''All active tickets, sorted by priority and time'' 
    7591{{{ 
    76 SELECT id AS ticket, status, severity, priority, owner, 
    77        time as created, summary FROM ticket 
     92SELECT id AS ticket, status, severity, priority, owner,  
     93       time as created, summary FROM ticket  
    7894  WHERE status IN ('new', 'assigned', 'reopened') 
    7995  ORDER BY priority, time 
     
    85101 
    86102== Advanced Reports: Dynamic Variables == 
    87 For more flexible reports, Trac supports the use of ''dynamic variables'' in report SQL statements. 
     103For more flexible reports, Trac supports the use of ''dynamic variables'' in report SQL statements.  
    88104In short, dynamic variables are ''special'' strings that are replaced by custom data before query execution. 
    89105 
     
    93109Example: 
    94110{{{ 
    95 SELECT id AS ticket,summary FROM ticket WHERE priority='$PRIORITY' 
    96 }}} 
    97  
    98 To assign a value to $PRIORITY when viewing the report, you must define it as an argument in the report URL, leaving out the the leading '$'. 
     111SELECT id AS ticket,summary FROM ticket WHERE priority=$PRIORITY 
     112}}} 
     113 
     114To assign a value to $PRIORITY when viewing the report, you must define it as an argument in the report URL, leaving out the leading '$'. 
    99115 
    100116Example: 
    101117{{{ 
    102  http://projects.edgewall.com/trac/reports/14?PRIORITY=high 
     118 http://trac.edgewall.org/reports/14?PRIORITY=high 
     119}}} 
     120 
     121To use multiple variables, separate them with an '&'. 
     122 
     123Example: 
     124{{{ 
     125 http://trac.edgewall.org/reports/14?PRIORITY=high&SEVERITY=critical 
    103126}}} 
    104127 
    105128 
    106129=== Special/Constant Variables === 
    107 There is one ''magic'' dynamic variable to allow practical reports, its value automatically set without having to change the URL. 
     130There is one ''magic'' dynamic variable to allow practical reports, its value automatically set without having to change the URL.  
    108131 
    109132 * $USER -- Username of logged in user. 
     
    111134Example (''List all tickets assigned to me''): 
    112135{{{ 
    113 SELECT id AS ticket,summary FROM ticket WHERE owner='$USER' 
     136SELECT id AS ticket,summary FROM ticket WHERE owner=$USER 
    114137}}} 
    115138 
     
    125148== Special Columns == 
    126149To format reports, TracReports looks for 'magic' column names in the query 
    127 result. These 'magic' names are processed and affect the layout and style of the 
     150result. These 'magic' names are processed and affect the layout and style of the  
    128151final report. 
    129152 
    130153=== Automatically formatted columns === 
    131  * '''ticket''' -- Ticket ID number. Becomes a hyperlink to that ticket. 
     154 * '''ticket''' -- Ticket ID number. Becomes a hyperlink to that ticket.  
    132155 * '''created, modified, date, time''' -- Format cell as a date and/or time. 
    133156 
     
    136159'''Example:''' 
    137160{{{ 
    138 SELECT id as ticket, created, status, summary FROM ticket 
     161SELECT id as ticket, created, status, summary FROM ticket  
    139162}}} 
    140163 
    141164=== Custom formatting columns === 
    142 Columns whose names begin and end with 2 underscores (Example: '''_''''''_color_''''''_''') are 
     165Columns whose names begin and end with 2 underscores (Example: '''`__color__`''') are 
    143166assumed to be ''formatting hints'', affecting the appearance of the row. 
    144  
    145  * '''_''''''_group_''''''_''' -- Group results based on values in this column. Each group will have its own header and table. 
    146  * '''_''''''_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. 
    147  * '''_''''''_style_''''''_''' -- A custom CSS style expression to use for the current row. 
     167  
     168 * '''`__group__`''' -- Group results based on values in this column. Each group will have its own header and table. 
     169 * '''`__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. 
     170{{{ 
     171#!html 
     172<div style="margin-left:7.5em">Defaults:  
     173<span style="border: none; color: #333; background: transparent;  font-size: 85%; background: #fdc; border-color: #e88; color: #a22">Color 1</span> 
     174<span style="border: none; color: #333; background: transparent;  font-size: 85%; background: #ffb; border-color: #eea; color: #880">Color 2</span> 
     175<span style="border: none; color: #333; background: transparent;  font-size: 85%; background: #fbfbfb; border-color: #ddd; color: #444">Color 3</span> 
     176<span style="border: none; color: #333; background: transparent; font-size: 85%; background: #e7ffff; border-color: #cee; color: #099">Color 4</span> 
     177<span style="border: none; color: #333; background: transparent;  font-size: 85%; background: #e7eeff; border-color: #cde; color: #469">Color 5</span> 
     178</div> 
     179}}} 
     180 * '''`__style__`''' -- A custom CSS style expression to use for the current row.  
    148181 
    149182'''Example:''' ''List active tickets, grouped by milestone, colored by priority'' 
     
    154187       t.id AS ticket, summary 
    155188  FROM ticket t,enum p 
    156   WHERE t.status IN ('new', 'assigned', 'reopened') 
     189  WHERE t.status IN ('new', 'assigned', 'reopened')  
    157190    AND p.name=t.priority AND p.type='priority' 
    158191  ORDER BY t.milestone, p.value, t.severity, t.time 
     
    167200also possible to create multi-line report entries. 
    168201 
    169  * '''column_''' -- ''Break row after this''. By appending an underscore ('_') to the column name, the remaining columns will be be continued on a second line. 
    170  
    171  * '''_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. 
    172  
    173  * '''_column'''  --  ''Hide data''. Prepending an underscore ('_') to a column name instructs Trac to hide the contents from the HTML output. This is useful for information to be visible only if downloaded in other formats (like CSV or RSS/XML). 
     202 * '''`column_`''' -- ''Break row after this''. By appending an underscore ('_') to the column name, the remaining columns will be be continued on a second line. 
     203 
     204 * '''`_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. 
     205 
     206 * '''`_column`'''  --  ''Hide data''. Prepending an underscore ('_') to a column name instructs Trac to hide the contents from the HTML output. This is useful for information to be visible only if downloaded in other formats (like CSV or RSS/XML). 
    174207 
    175208'''Example:''' ''List active tickets, grouped by milestone, colored by priority, with  description and multi-line layout'' 
     
    178211SELECT p.value AS __color__, 
    179212       t.milestone AS __group__, 
    180        (CASE owner 
    181           WHEN 'daniel' THEN 'font-weight: bold; background: red;' 
     213       (CASE owner  
     214          WHEN 'daniel' THEN 'font-weight: bold; background: red;'  
    182215          ELSE '' END) AS __style__, 
    183216       t.id AS ticket, summary AS summary_,             -- ## Break line here 
     
    187220       changetime AS _changetime, reporter AS _reporter -- ## Hidden from HTML output 
    188221  FROM ticket t,enum p 
    189   WHERE t.status IN ('new', 'assigned', 'reopened') 
     222  WHERE t.status IN ('new', 'assigned', 'reopened')  
    190223    AND p.name=t.priority AND p.type='priority' 
    191224  ORDER BY t.milestone, p.value, t.severity, t.time 
    192225}}} 
    193226 
    194  
    195 ---- 
    196 See also: TracTickets, TracQuery, TracGuide 
     227=== Reporting on custom fields === 
     228 
     229If you have added custom fields to your tickets (a feature since v0.8, see TracTicketsCustomFields), you can write a SQL query to cover them. You'll need to make a join on the ticket_custom table, but this isn't especially easy. 
     230 
     231If you have tickets in the database ''before'' you declare the extra fields in trac.ini, there will be no associated data in the ticket_custom table. To get around this, use SQL's "LEFT OUTER JOIN" clauses. See TracIniReportCustomFieldSample for some examples. 
     232 
     233'''Note that you need to set up permissions in order to see the buttons for adding or editing reports.''' 
     234 
     235---- 
     236See also: TracTickets, TracQuery, TracGuide, [http://www.sqlite.org/lang_expr.html Query Language Understood by SQLite]