Home

Documentation

Project Support

Changes in Version 1 of TracReports

Author:
trac
Timestamp:
Wed Aug 23 17:34:43 2006

Legend:

Unmodified
Added
Removed
Modified

  • TracReports

    v0 v1
      1  = Trac Reports = 
      2  [[TracGuideToc]] 
      3   
      4  The Trac reports module provides a simple, yet powerful reporting facility 
      5  to present information about tickets in the Trac database. 
      6   
      7  Rather than have its own report definition format, TracReports relies on standard SQL 
      8  SELECT statements for custom report definition.  
      9   
      10  A 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   
      17   
      18  == 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.  
      20   
      21  If a column header is a hyperlink (red), click the column you would like to sort by. Clicking the same header again reverses the order. 
      22   
      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. 
      28   
      29  === Comma-delimited - CSV (Comma Separated Values) === 
      30  Export 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. 
      32   
      33  === Tab-delimited === 
      34  Like above, but uses tabs (\t) instead of comma. 
      35   
      36  === 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  ---- 
      40  == Creating Custom Reports == 
      41   
      42  ''Creating a custom report requires a comfortable knowledge of SQL.'' 
      43   
      44  A report is basically a single named SQL query, executed and presented by 
      45  Trac.  Reports can be viewed and created from a custom SQL expression directly 
      46  in from the web interface. 
      47   
      48  Typically, a report consists of a SELECT-expression from the 'ticket' table, 
      49  using the available columns and sorting the way you want it. 
      50   
      51  == Ticket columns == 
      52  The ''ticket'' table has the following columns: 
      53   * id 
      54   * time 
      55   * changetime 
      56   * component 
      57   * severity   
      58   * priority  
      59   * owner 
      60   * reporter 
      61   * cc 
      62   * url 
      63   * version 
      64   * milestone 
      65   * status 
      66   * resolution 
      67   * summary 
      68   * description 
      69   
      70  See TracTickets for a detailed description of the column fields. 
      71   
      72  '''all active tickets, sorted by priority and time''' 
      73   
      74  '''Example:''' ''All active tickets, sorted by priority and time'' 
      75  {{{ 
      76  SELECT id AS ticket, status, severity, priority, owner,  
      77         time as created, summary FROM ticket  
      78    WHERE status IN ('new', 'assigned', 'reopened') 
      79    ORDER BY priority, time 
      80  }}} 
      81   
      82   
      83  ---- 
      84   
      85   
      86  == Advanced Reports: Dynamic Variables == 
      87  For more flexible reports, Trac supports the use of ''dynamic variables'' in report SQL statements.  
      88  In short, dynamic variables are ''special'' strings that are replaced by custom data before query execution. 
      89   
      90  === Using Variables in a Query === 
      91  The syntax for dynamic variables is simple, any upper case word beginning with '$' is considered a variable. 
      92   
      93  Example: 
      94  {{{ 
      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 '$'. 
      99   
      100  Example: 
      101  {{{ 
      102   http://projects.edgewall.com/trac/reports/14?PRIORITY=high 
      103  }}} 
      104   
      105   
      106  === Special/Constant Variables === 
      107  There is one ''magic'' dynamic variable to allow practical reports, its value automatically set without having to change the URL.  
      108   
      109   * $USER -- Username of logged in user. 
      110   
      111  Example (''List all tickets assigned to me''): 
      112  {{{ 
      113  SELECT id AS ticket,summary FROM ticket WHERE owner='$USER' 
      114  }}} 
      115   
      116   
      117  ---- 
      118   
      119   
      120  == Advanced Reports: Custom Formatting == 
      121  Trac is also capable of more advanced reports, including custom layouts, 
      122  result grouping and user-defined CSS styles. To create such reports, we'll use 
      123  specialized SQL statements to control the output of the Trac report engine. 
      124   
      125  == Special Columns == 
      126  To 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  
      128  final report. 
      129   
      130  === Automatically formatted columns === 
      131   * '''ticket''' -- Ticket ID number. Becomes a hyperlink to that ticket.  
      132   * '''created, modified, date, time''' -- Format cell as a date and/or time. 
      133   
      134   * '''description''' -- Ticket description field, parsed through the wiki engine. 
      135   
      136  '''Example:''' 
      137  {{{ 
      138  SELECT id as ticket, created, status, summary FROM ticket  
      139  }}} 
      140   
      141  === Custom formatting columns === 
      142  Columns whose names begin and end with 2 underscores (Example: '''_''''''_color_''''''_''') are 
      143  assumed 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.  
      148   
      149  '''Example:''' ''List active tickets, grouped by milestone, colored by priority'' 
      150  {{{ 
      151  SELECT p.value AS __color__, 
      152       t.milestone AS __group__, 
      153       (CASE owner WHEN 'daniel' THEN 'font-weight: bold; background: red;' ELSE '' END) AS __style__, 
      154         t.id AS ticket, summary 
      155    FROM ticket t,enum p 
      156    WHERE t.status IN ('new', 'assigned', 'reopened')  
      157      AND p.name=t.priority AND p.type='priority' 
      158    ORDER BY t.milestone, p.value, t.severity, t.time 
      159  }}} 
      160   
      161  '''Note:''' A table join is used to match ''ticket'' priorities with their 
      162  numeric representation from the ''enum'' table. 
      163   
      164  === Changing layout of report rows === 
      165  By default, all columns on each row are display on a single row in the HTML 
      166  report, possibly formatted according to the descriptions above. However, it's 
      167  also possible to create multi-line report entries. 
      168   
      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). 
      174   
      175  '''Example:''' ''List active tickets, grouped by milestone, colored by priority, with  description and multi-line layout'' 
      176   
      177  {{{ 
      178  SELECT p.value AS __color__, 
      179         t.milestone AS __group__, 
      180         (CASE owner  
      181            WHEN 'daniel' THEN 'font-weight: bold; background: red;'  
      182            ELSE '' END) AS __style__, 
      183         t.id AS ticket, summary AS summary_,             -- ## Break line here 
      184         component,version, severity, milestone, status, owner, 
      185         time AS created, changetime AS modified,         -- ## Dates are formatted 
      186         description AS _description_,                    -- ## Uses a full row 
      187         changetime AS _changetime, reporter AS _reporter -- ## Hidden from HTML output 
      188    FROM ticket t,enum p 
      189    WHERE t.status IN ('new', 'assigned', 'reopened')  
      190      AND p.name=t.priority AND p.type='priority' 
      191    ORDER BY t.milestone, p.value, t.severity, t.time 
      192  }}} 
      193   
      194   
      195  ---- 
      196  See also: TracTickets, TracQuery, TracGuide