# wiki

name: TracCgi
author: trac
ipnr: 127.0.0.1
text:
  = Installing Trac as CGI =

  {{{
  #!div class=important
    ''Please note that using Trac via CGI is the slowest deployment method available. It is slower than [TracModPython mod_python], [TracFastCgi FastCGI] and even [trac:TracOnWindowsIisAjp IIS/AJP] on Windows.''
  }}}

  CGI script is the entrypoint that web-server calls when a web-request to an application is made. To generate the `trac.cgi` script run:
  {{{
  trac-admin /path/to/env deploy /path/to/www/trac
  }}}
  `trac.cgi` will be in the `cgi-bin` folder inside the given path. ''Make sure it is executable by your web server''. This command also copies `static resource` files to a `htdocs` directory of a given destination.

  == Apache web-server configuration ==

  In [http://httpd.apache.org/ Apache] there are two ways to run Trac as CGI:

   1. Use a `ScriptAlias` directive that maps an URL to the `trac.cgi` script (recommended)
   2. Copy the `trac.cgi` file into the directory for CGI executables used by your web server (commonly named `cgi-bin`). You can also create a symbolic link, but in that case make sure that the `FollowSymLinks` option is enabled for the `cgi-bin` directory.

  To make Trac available at `http://yourhost.example.org/trac` add `ScriptAlias` directive to Apache configuration file, changing `trac.cgi` path to match your installation:
  {{{
  ScriptAlias /trac /path/to/www/trac/cgi-bin/trac.cgi
  }}}

   ''Note that this directive requires enabled `mod_alias` module.''

  If you're using Trac with a single project you need to set its location using the `TRAC_ENV` environment variable:
  {{{
  <Location "/trac">
    SetEnv TRAC_ENV "/path/to/projectenv"
  </Location>
  }}}

  Or to use multiple projects you can specify their common parent directory using the `TRAC_ENV_PARENT_DIR` variable:
  {{{
  <Location "/trac">
    SetEnv TRAC_ENV_PARENT_DIR "/path/to/project/parent/dir"
  </Location>
  }}}

   ''Note that the `SetEnv` directive requires enabled `mod_env` module. It is also possible to set TRAC_ENV in trac.cgi. Just add the following code between "try:" and "from trac.web ...":''

  {{{
      import os
      os.environ['TRAC_ENV'] = "/path/to/projectenv"
  }}}

   '' Or for TRAC_ENV_PARENT_DIR: ''

  {{{
      import os
      os.environ['TRAC_ENV_PARENT_DIR'] = "/path/to/project/parent/dir"
  }}}

  If you are using the [http://httpd.apache.org/docs/suexec.html Apache suEXEC] feature please see [http://trac.edgewall.org/wiki/ApacheSuexec].

  On some systems, you ''may'' need to edit the shebang line in the `trac.cgi` file to point to your real Python installation path. On a Windows system you may need to configure Windows to know how to execute a .cgi file (Explorer -> Tools -> Folder Options -> File Types -> CGI).

  === Using WSGI ===

  You can run a [http://henry.precheur.org/python/how_to_serve_cgi WSGI handler] [http://pythonweb.org/projects/webmodules/doc/0.5.3/html_multipage/lib/example-webserver-web-wsgi-simple-cgi.html under CGI].  You can [wiki:TracModWSGI#Thetrac.wsgiscript write your own application function], or use the deployed trac.wsgi's application.

  == Mapping Static Resources ==

  See TracInstall#MappingStaticResources.

  == Adding Authentication ==

  See TracInstall#ConfiguringAuthentication.

  ----
  See also:  TracGuide, TracInstall, [wiki:TracModWSGI], TracFastCgi, TracModPython


name: TracReports
author: trac
ipnr: 127.0.0.1
text:
  = Trac Reports =
  [[TracGuideToc]]

  The Trac reports module provides a simple, yet powerful reporting facility
  to present information about tickets in the Trac database.

  Rather than have its own report definition format, TracReports relies on standard SQL
  `SELECT` statements for custom report definition. 

    '''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.''

    ''You can already completely replace the reports module by the query module simply by disabling the former in [wiki:TracIni trac.ini]:''
    {{{
    [components]
    trac.ticket.report.* = disabled
    }}}
    ''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.''

  A report consists of these basic parts:
   * '''ID''' — Unique (sequential) identifier 
   * '''Title''' — Descriptive title
   * '''Description''' — A brief description of the report, in WikiFormatting text.
   * '''Report Body''' — List of results from report query, formatted according to the methods described below.
   * '''Footer''' — Links to alternative download formats for this report.

  == Changing Sort Order ==
  Simple reports - ungrouped reports to be specific - can be changed to be sorted by any column simply by clicking the column header. 

  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.

  == Changing Report Numbering ==
  There may be instances where you need to change the ID of the report, perhaps to organize the reports better. At present this requires changes to the trac database. The ''report'' table has the following schema ''(since 0.10)'':
   * id integer PRIMARY KEY
   * author text
   * title text
   * query text
   * description text
  Changing the ID changes the shown order and number in the ''Available Reports'' list and the report's perma-link. This is done by running something like:
  {{{
  update report set id=5 where id=3;
  }}}
  Keep in mind that the integrity has to be maintained (i.e., ID has to be unique, and you don't want to exceed the max, since that's managed by SQLite someplace).

  You may also need to update or remove the report number stored in the report or query.

  == Navigating Tickets ==
  Clicking 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.

  You 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)''

  == Alternative Download Formats ==
  Aside from the default HTML view, reports can also be exported in a number of alternative formats.
  At the bottom of the report page, you will find a list of available data formats. Click the desired link to 
  download the alternative report format.

  === Comma-delimited - CSV (Comma Separated Values) ===
  Export the report as plain text, each row on its own line, columns separated by a single comma (',').
  '''Note:''' The output is fully escaped so carriage returns, line feeds, and commas will be preserved in the output.

  === Tab-delimited ===
  Like above, but uses tabs (\t) instead of comma.

  === RSS - XML Content Syndication ===
  All 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.

  ----

  == Creating Custom Reports ==

  ''Creating a custom report requires a comfortable knowledge of SQL.''

  '''Note that you need to set up [TracPermissions#Reports permissions] in order to see the buttons for adding or editing reports.'''

  A report is basically a single named SQL query, executed and presented by
  Trac.  Reports can be viewed and created from a custom SQL expression directly
  in the web interface.

  Typically, a report consists of a SELECT-expression from the 'ticket' table,
  using the available columns and sorting the way you want it.

  == Ticket columns ==
  The ''ticket'' table has the following columns:
   * id
   * type
   * time
   * changetime
   * component
   * severity  
   * priority 
   * owner
   * reporter
   * cc
   * version
   * milestone
   * status
   * resolution
   * summary
   * description
   * keywords

  See TracTickets for a detailed description of the column fields.

  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: Dynamic Variables ==
  For more flexible reports, Trac supports the use of ''dynamic variables'' in report SQL statements. 
  In short, dynamic variables are ''special'' strings that are replaced by custom data before query execution.

  === Using Variables in a Query ===
  The syntax for dynamic variables is simple, any upper case word beginning with '$' is considered a variable.

  Example:
  {{{
  SELECT id AS ticket,summary FROM ticket WHERE priority=$PRIORITY
  }}}

  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 '$'.

  Example:
  {{{
   http://trac.edgewall.org/reports/14?PRIORITY=high
  }}}

  To use multiple variables, separate them with an '&'.

  Example:
  {{{
   http://trac.edgewall.org/reports/14?PRIORITY=high&SEVERITY=critical
  }}}

  Dynamic variables can also be used in the report title and description (since 1.1.1).

  === !Special/Constant Variables ===
  There is one dynamic variable whose value is set automatically (the URL does not have to be changed) to allow practical reports. 

   * $USER — Username of logged in user.

  Example (''List all tickets assigned to me''):
  {{{
  SELECT id AS ticket,summary FROM ticket WHERE owner=$USER
  }}}



  == 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. 
   * '''id''' — same as '''ticket''' above when '''realm''' is not set
   * '''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)
   * '''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 
  }}}

  Those columns can also be defined but marked as hidden, see [#column-syntax below].

  See trac:wiki/CookBook/Configuration/Reports for some example of creating reports for realms other than ''ticket''.

  === Custom formatting columns ===
  Columns whose names begin and end with 2 underscores (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.
   * '''`__grouplink__`''' — Make the header of each group a link to the specified URL. The URL is taken from the first row of each group.
   * '''`__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.
  {{{
  #!html
  <div style="margin-left:7.5em">Defaults: 
  <span style="border: none; color: #333; background: transparent;  font-size: 85%; background: #fdc; border-color: #e88; color: #a22">Color 1</span>
  <span style="border: none; color: #333; background: transparent;  font-size: 85%; background: #ffb; border-color: #eea; color: #880">Color 2</span>
  <span style="border: none; color: #333; background: transparent;  font-size: 85%; background: #fbfbfb; border-color: #ddd; color: #444">Color 3</span>
  <span style="border: none; color: #333; background: transparent; font-size: 85%; background: #e7ffff; border-color: #cee; color: #099">Color 4</span>
  <span style="border: none; color: #333; background: transparent;  font-size: 85%; background: #e7eeff; border-color: #cde; color: #469">Color 5</span>
  </div>
  }}}
   * '''`__style__`''' — A custom CSS style expression to use on the `<tr>` element of the current row.
   * '''`__class__`''' — Zero or more space-separated CSS class names to be set on the `<tr>` element of the current row. These classes are added to the class name derived from `__color__` and the odd / even indicator.

  '''Example:''' ''List active tickets, grouped by milestone, group header linked to milestone page, colored by priority''
  {{{
  SELECT p.value AS __color__,
       t.milestone AS __group__,
       '../milestone/' || t.milestone AS __grouplink__,
       (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 === #column-syntax
  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 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 for information to be visible only if downloaded in other formats (like CSV or RSS/XML).
     This can be used to hide any kind of column, even important ones required for identifying the resource, e.g. `id as _id` will hide the '''Id''' column but the link to the ticket will be present.

  '''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
  }}}

  === Reporting on custom fields ===

  If 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.

  If 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 [trac:TracIniReportCustomFieldSample TracIniReportCustomFieldSample] for some examples.

  === A note about SQL rewriting #rewriting

  Beyond the relatively trivial replacement of dynamic variables, the SQL query is also altered in order to support two features of the reports:
   1. [#sort-order changing the sort order]
   2. pagination support (limitation of the number of result rows displayed on each page)
  In order to support the first feature, the sort column is inserted in the `ORDER BY` clause in the first position or in the second position if a `__group__` column is specified (an `ORDER BY` clause is created if needed). In order to support pagination, a `LIMIT ... OFFSET ...` clause is appended.
  The query might be too complex for the automatic rewrite to work correctly, resulting in an erroneous query. In this case you still have the possibility to control exactly how the rewrite is done by manually inserting the following tokens:
   - `@SORT_COLUMN@`, the place where the name of the selected sort column will be inserted,
   - `@LIMIT_OFFSET@`, the place where the pagination support clause will be added
  Note that if you write them after an SQL comment, `--`, you'll effectively disable rewriting if this is what you want!

  Let's take an example, consider the following SQL query:
  {{{
  -- ## 4: Assigned, Active Tickets by Owner ## --

  -- 
  -- List assigned tickets, group by ticket owner, sorted by priority.
  -- 

  SELECT p.value AS __color__,
     owner AS __group__,
     id AS ticket, summary, component, milestone, t.type AS type, severity, time AS created,
     changetime AS _changetime, description AS _description,
     reporter AS _reporter
    FROM ticket t,enum p
    WHERE status = 'assigned'
  AND p.name=t.priority AND p.type='priority'
    ORDER BY __group__, p.value, severity, time
  }}}

  The automatic rewrite will be the following (4 rows per page, page 2, sorted by `component`):
  {{{
  SELECT p.value AS __color__,
     owner AS __group__,
     id AS ticket, summary, component, milestone, t.type AS type, severity, time AS created,
     changetime AS _changetime, description AS _description,
     reporter AS _reporter
    FROM ticket t,enum p
    WHERE status = 'assigned'
  AND p.name=t.priority AND p.type='priority'
    ORDER BY __group__ ASC, `component` ASC,  __group__, p.value, severity, time
   LIMIT 4 OFFSET 4
  }}}

  The equivalent SQL query with the rewrite tokens would have been:
  {{{
  SELECT p.value AS __color__,
     owner AS __group__,
     id AS ticket, summary, component, milestone, t.type AS type, severity, time AS created,
     changetime AS _changetime, description AS _description,
     reporter AS _reporter
    FROM ticket t,enum p
    WHERE status = 'assigned'
  AND p.name=t.priority AND p.type='priority'
    ORDER BY __group__, @SORT_COLUMN@, p.value, severity, time
  @LIMIT_OFFSET@
  }}}

  If you want to always sort first by priority and only then by the user selected sort column, simply use the following `ORDER BY` clause:
  {{{
    ORDER BY __group__, p.value, @SORT_COLUMN@, severity, time
  }}}

  ----
  See also: TracTickets, TracQuery, TracGuide, [http://www.sqlite.org/lang_expr.html Query Language Understood by SQLite]


name: TracQuery
author: trac
ipnr: 127.0.0.1
text:
  = Trac Ticket Queries =
  [[TracGuideToc]]

  In addition to [wiki:TracReports reports], Trac provides support for ''custom ticket queries'', used to display lists of tickets meeting a specified set of criteria. 

  To configure and execute a custom query, switch to the ''View Tickets'' module from the navigation bar, and select the ''Custom Query'' link.

  == Filters ==

  When you first go to the query page the default filter will display tickets relevant to you:
   * If logged in then all open tickets it will display open tickets assigned to you.
   * If not logged in but you have specified a name or email address in the preferences then it will display all open tickets where your email (or name if email not defined) is in the CC list.
   * If not logged and no name/email defined in the preferences then all open issues are displayed.

  Current filters can be removed by clicking the button to the left with the minus sign on the label.  New filters are added from the pulldown lists at the bottom corners of the filters box ('And' conditions on the left, 'Or' conditions on the right).  Filters with either a text box or a pulldown menu of options can be added multiple times to perform an ''or'' of the criteria.

  You can use the fields just below the filters box to group the results based on a field, or display the full description for each ticket.

  Once you've edited your filters click the ''Update'' button to refresh your results.

  == Navigating Tickets ==
  Clicking on one of the query 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 Query'' link to return to the query page.  

  You can safely edit any of the tickets and continue to navigate through the results using the ''!Next/Previous/Back to Query'' links after saving your results.  When you return to the query ''any tickets which were edited'' will be displayed with italicized text.  If one of the tickets was edited such that [[html(<span style="color: grey">it no longer matches the query criteria </span>)]] the text will also be greyed. Lastly, if '''a new ticket matching the query criteria has been created''', it will be shown in bold. 

  The query results can be refreshed and cleared of these status indicators by clicking the ''Update'' button again.

  == Saving Queries ==

  Trac allows you to save the query as a named query accessible from the reports module. To save a query ensure that you have ''Updated'' the view and then click the ''Save query'' button displayed beneath the results.
  You can also save references to queries in Wiki content, as described below.

  ''Note:'' one way to easily build queries like the ones below, you can build and test the queries in the Custom report module and when ready - click ''Save query''. This will build the query string for you. All you need to do is remove the extra line breaks.

  ''Note:'' you must have the '''REPORT_CREATE''' permission in order to save queries to the list of default reports. The ''Save query'' button will only appear if you are logged in as a user that has been granted this permission. If your account does not have permission to create reports, you can still use the methods below to save a query.


  === Using TracLinks ===

  You may want to save some queries so that you can come back to them later.  You can do this by making a link to the query from any Wiki page.
  {{{
  [query:status=new|assigned|reopened&version=1.0 Active tickets against 1.0]
  }}}

  Which is displayed as:
    [query:status=new|assigned|reopened&version=1.0 Active tickets against 1.0]

  This uses a very simple query language to specify the criteria (see [wiki:TracQuery#QueryLanguage Query Language]).

  Alternatively, you can copy the query string of a query and paste that into the Wiki link, including the leading `?` character:
  {{{
  [query:?status=new&status=assigned&status=reopened&group=owner Assigned tickets by owner]
  }}}

  Which is displayed as:
    [query:?status=new&status=assigned&status=reopened&group=owner Assigned tickets by owner]

  === Using the `[[TicketQuery]]` Macro ===

  The [trac:TicketQuery TicketQuery] macro lets you display lists of tickets matching certain criteria anywhere you can use WikiFormatting.

  Example:
  {{{
  [[TicketQuery(version=0.6|0.7&resolution=duplicate)]]
  }}}

  This is displayed as:
    [[TicketQuery(version=0.6|0.7&resolution=duplicate)]]

  Just like the [wiki:TracQuery#UsingTracLinks query: wiki links], the parameter of this macro expects a query string formatted according to the rules of the simple [wiki:TracQuery#QueryLanguage ticket query language]. This also allows displaying the link and description of a single ticket:
  {{{
  [[TicketQuery(id=123)]]
  }}}

  This is displayed as:
    [[TicketQuery(id=123)]]

  A more compact representation without the ticket summaries is also available:
  {{{
  [[TicketQuery(version=0.6|0.7&resolution=duplicate, compact)]]
  }}}

  This is displayed as:
    [[TicketQuery(version=0.6|0.7&resolution=duplicate, compact)]]

  Finally, if you wish to receive only the number of defects that match the query, use the ``count`` parameter.

  {{{
  [[TicketQuery(version=0.6|0.7&resolution=duplicate, count)]]
  }}}

  This is displayed as:
    [[TicketQuery(version=0.6|0.7&resolution=duplicate, count)]]

  === Customizing the ''table'' format ===
  You can also customize the columns displayed in the table format (''format=table'') by using ''col=<field>'' - you can specify multiple fields and what order they are displayed by placing pipes (`|`) between the columns like below:

  {{{
  [[TicketQuery(max=3,status=closed,order=id,desc=1,format=table,col=resolution|summary|owner|reporter)]]
  }}}

  This is displayed as:
  [[TicketQuery(max=3,status=closed,order=id,desc=1,format=table,col=resolution|summary|owner|reporter)]]

  ==== Full rows ====
  In ''table'' format you can also have full rows by using ''rows=<field>'' like below:

  {{{
  [[TicketQuery(max=3,status=closed,order=id,desc=1,format=table,col=resolution|summary|owner|reporter,rows=description)]]
  }}}

  This is displayed as:
  [[TicketQuery(max=3,status=closed,order=id,desc=1,format=table,col=resolution|summary|owner|reporter,rows=description)]]


  === Query Language ===

  `query:` TracLinks and the `[[TicketQuery]]` macro both use a mini “query language” for specifying query filters. Basically, the filters are separated by ampersands (`&`). Each filter then consists of the ticket field name, an operator, and one or more values. More than one value are separated by a pipe (`|`), meaning that the filter matches any of the values. To include a literal `&` or `|` in a value, escape the character with a backslash (`\`).

  The available operators are:
  || '''`=`''' || the field content exactly matches one of the values ||
  || '''`~=`''' || the field content contains one or more of the values ||
  || '''`^=`''' || the field content starts with one of the values ||
  || '''`$=`''' || the field content ends with one of the values ||

  All of these operators can also be negated:
  || '''`!=`''' || the field content matches none of the values ||
  || '''`!~=`''' || the field content does not contain any of the values ||
  || '''`!^=`''' || the field content does not start with any of the values ||
  || '''`!$=`''' || the field content does not end with any of the values ||

  The date fields `created` and `modified` can be constrained by using the `=` operator and specifying a value containing two dates separated by two dots (`..`). Either end of the date range can be left empty, meaning that the corresponding end of the range is open. The date parser understands a few natural date specifications like "3 weeks ago", "last month" and "now", as well as Bugzilla-style date specifications like "1d", "2w", "3m" or "4y" for 1 day, 2 weeks, 3 months and 4 years, respectively. Spaces in date specifications can be left out to avoid having to quote the query string. 
  || '''`created=2007-01-01..2008-01-01`''' || query tickets created in 2007 ||
  || '''`created=lastmonth..thismonth`''' || query tickets created during the previous month ||
  || '''`modified=1weekago..`''' || query tickets that have been modified in the last week ||
  || '''`modified=..30daysago`''' || query tickets that have been inactive for the last 30 days ||

  ----
  See also: TracTickets, TracReports, TracGuide


# report

title: Active Tickets
description:
  * List all active tickets by priority.
  * Color each row based on priority.

query:
  SELECT p.value AS __color__,
     id AS ticket, summary, component, version, milestone, t.type AS type,
     owner, status,
     time AS created,
     changetime AS _changetime, description AS _description,
     reporter AS _reporter
    FROM ticket t
    LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
    WHERE status <> 'closed'
    ORDER BY CAST(p.value AS integer), milestone, t.type, time


title: Active Tickets by Version
query:
  SELECT p.value AS __color__,
     version AS __group__,
     id AS ticket, summary, component, version, t.type AS type,
     owner, status,
     time AS created,
     changetime AS _changetime, description AS _description,
     reporter AS _reporter
    FROM ticket t
    LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
    WHERE status <> 'closed'
    ORDER BY (version IS NULL),version, CAST(p.value AS integer), t.type, time

description:
  This report shows how to color results by priority,
  while grouping results by version.

  Last modification time, description and reporter are included as hidden fields
  for useful RSS export.


title: Active Tickets by Milestone
query:
  SELECT p.value AS __color__,
     'Milestone '||milestone AS __group__,
     id AS ticket, summary, component, version, t.type AS type,
     owner, status,
     time AS created,
     changetime AS _changetime, description AS _description,
     reporter AS _reporter
    FROM ticket t
    LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
    WHERE status <> 'closed'
    ORDER BY (milestone IS NULL),milestone, CAST(p.value AS integer), t.type, time

description:
  This report shows how to color results by priority,
  while grouping results by milestone.

  Last modification time, description and reporter are included as hidden fields
  for useful RSS export.


title: Accepted, Active Tickets by Owner
description: List accepted tickets, group by ticket owner, sorted by priority.
query:
  SELECT p.value AS __color__,
     owner AS __group__,
     id AS ticket, summary, component, milestone, t.type AS type, time AS created,
     changetime AS _changetime, description AS _description,
     reporter AS _reporter
    FROM ticket t
    LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
    WHERE status = 'accepted'
    ORDER BY owner, CAST(p.value AS integer), t.type, time


title: Accepted, Active Tickets by Owner (Full Description)
query:
  SELECT p.value AS __color__,
     owner AS __group__,
     id AS ticket, summary, component, milestone, t.type AS type, time AS created,
     description AS _description_,
     changetime AS _changetime, reporter AS _reporter
    FROM ticket t
    LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
    WHERE status = 'accepted'
    ORDER BY owner, CAST(p.value AS integer), t.type, time

description:
  List tickets accepted, group by ticket owner.
  This report demonstrates the use of full-row display.


title: All Tickets By Milestone (Including closed)
description: A more complex example to show how to make advanced reports.
query:
  SELECT p.value AS __color__,
     t.milestone AS __group__,
     (CASE status
        WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;'
        ELSE
          (CASE owner WHEN $USER THEN 'font-weight: bold' END)
      END) AS __style__,
     id AS ticket, summary, component, status,
     resolution,version, t.type AS type, priority, owner,
     changetime AS modified,
     time AS _time,reporter AS _reporter
    FROM ticket t
    LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
    ORDER BY (milestone IS NULL), milestone DESC, (status = 'closed'),
          (CASE status WHEN 'closed' THEN changetime ELSE (-1) * CAST(p.value AS integer) END) DESC


title: My Tickets
query:
  SELECT DISTINCT
         p.value AS __color__,
         (CASE
           WHEN owner = $USER AND status = 'accepted' THEN 'Accepted'
           WHEN owner = $USER THEN 'Owned'
           WHEN reporter = $USER THEN 'Reported'
           ELSE 'Commented' END) AS __group__,
         t.id AS ticket, summary, component, version, milestone,
         t.type AS type, priority, t.time AS created,
         t.changetime AS _changetime, description AS _description,
         reporter AS _reporter
    FROM ticket t
    LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
    LEFT JOIN ticket_change tc ON tc.ticket = t.id AND tc.author = $USER
                                  AND tc.field = 'comment'
    WHERE t.status <> 'closed'
          AND (owner = $USER OR reporter = $USER OR author = $USER)
    ORDER BY (owner = $USER AND status = 'accepted') DESC,
             owner = $USER DESC, reporter = $USER DESC,
             CAST(p.value AS integer), milestone, t.type, t.time

description:
  This report demonstrates the use of the automatically set
  USER dynamic variable, replaced with the username of the
  logged in user when executed.


title: Active Tickets, Mine first
query:
  SELECT p.value AS __color__,
     (CASE owner
       WHEN $USER THEN 'My Tickets'
       ELSE 'Active Tickets'
      END) AS __group__,
     id AS ticket, summary, component, version, milestone, t.type AS type,
     owner, status,
     time AS created,
     changetime AS _changetime, description AS _description,
     reporter AS _reporter
    FROM ticket t
    LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
    WHERE status <> 'closed'
    ORDER BY (COALESCE(owner, '') = $USER) DESC, CAST(p.value AS integer), milestone, t.type, time

description:
   * List all active tickets by priority.
   * Show all tickets owned by the logged in user in a group first.


