This is meant to start a discussion on techniques and possibly feature
requests for the SQL reporting system in Trac.
I recently was asked to write a report for management to use that
would basically give all the tickets under some criteria, giving:
* Various status fields, e.g. created, modified, customer, owner,
type, status, resolution
* The Description
* All the comments
While trying to write this report, a few things I noticed which may be
bugs or become feature requests:
* A column named 'description' will be wiki processed (as
documented). However:
* If combined with the _column_ feature it does not.
* What if one is reporting on the comments instead? One would want
this wiki processing feature, but would not want to call the column
'description'.
* If one has but one column after a column_ named column, then this
one column will span the entire row, but if you have more than one the
additional columns join in width with the previous columns. Examples:
* Columns named: "One, Two, Three, Four_, Five" would yield
something like (needs to be viewed in fixed pitch font):
|| One || Two || Three || Four ||
|| Five ||
|| X || XX || XXX || XXXX ||
|| Here is some long data text ||
* Columns named: "One, Two, Three, Four_, Add, Five" would yield
something like
|| One || Two || Three || Four ||
|| Add || Five ||
|| X || XX || XXX || XXXX ||
|| +++ || Here is some long data text ||
* A column named 'date', 'time', 'datetime', 'created', or 'modified'
will take an integer (time_t) value and convert it to a readable date
or date-time as appropriate (as documented). However, what if the
value coming out of the SQL is already formated as a date? It
generates an error. Therefore I had to rename some of my columns to
'Date-Time', 'opened' and 'Last Changed'.
What I finally came up with was, assuming restring on customer:
{{{
#!sql
SELECT
id AS _id,
'Description' AS _recordType,
datetime( t.time, 'unixepoch', 'localtime' ) AS 'Date-Time',
'#' || id || ": " || summary AS __group__,
(CASE status
WHEN 'closed'
THEN 'color: #777; background: #ddd; border-color:
#ccc;'
ELSE ''
END) AS __style__,
datetime( t.time, 'unixepoch', 'localtime' ) AS opened,
datetime( t.changetime, 'unixepoch', 'localtime' ) AS 'Last
Changed',
c.value AS Customer,
owner,
t.type AS type,
status,
resolution AS resolution_,
description AS description
FROM ticket t
LEFT OUTER JOIN ticket_custom c ON (t.id = c.ticket AND c.name
= 'customer')
WHERE
c.value = $CUSTOMER
UNION
SELECT
id AS _id,
'Comment' AS _recordType,
datetime( tc.time, 'unixepoch', 'localtime' ) AS 'Date-Time',
'#' || id || ": " || summary AS __group__,
(CASE status
WHEN 'closed'
THEN 'color: #777; background: #ddd; border-color:
#ccc;'
ELSE ''
END) AS __style__,
NULL AS opened,
NULL AS 'Last Changed',
NULL AS Customer,
NULL AS owner,
NULL AS type,
NULL AS status,
NULL AS resolution_,
tc.newvalue AS description
FROM ticket t
LEFT OUTER JOIN ticket_custom c
ON (t.id = c.ticket AND c.name = 'customer')
LEFT OUTER JOIN ticket_change tc
ON (t.id = tc.ticket AND tc.field = 'comment')
WHERE tc.time IS NOT NULL
AND tc.newvalue <> ''
AND c.value = $CUSTOMER
ORDER BY 1,3
}}}
This results in a report the looks something like:
#20: Summory of ticket twenty
|| Date-Time || Opened || ... || Customer || Owner ||
Type ...
||
Description ...
|| 2010-03-17... || 2010-03-17... || ... || Acme || bob ||
defect...
|| Current Description of ticket number twenty with wiki
rendering. ...
|| 2010-03-17... || || || ||
|| ...
|| First comment on ticket with wiki
rendering. ...
|| 2010-03-18... || || || ||
|| ...
|| Second comment on
ticket. ...
#33: Summory of ticket thrity-three
|| Date-Time || Opened || ... || Customer || Owner ||
Type ...
||
Description ...
|| 2010-03-22... || 2010-03-22... || ... || HAL || tim ||
task ...
|| Current Description of ticket number twenty with wiki
rendering. ...
...
One of the tricks I played to get the report I wanted was to use a
UNION of two queries where the first gave all the status information
and the description, and the second NULL'ed out all of the status info
and instead gave all of the comments. A disadvantage of this is that
many changes that would be desired to make against this query has to
be made in two different places. Examples: What columns to report or
what conditions to restrict on.
Feature idea (not fully thought out): Some how enable a new feature
that, when reporting a series of records and using the column_ or
_column_ feature, if two records are identical in the first set of
field values (those before the row break) then suppress their
reporting. If I had this feature I could have then grouped by
something other than the ticket (say customer) but still had the
reporting of each ticket formated well. To do so I would have removed
the NULL'ing in the second query so that it would trip this feature.
Example columns to report on:
ticket, summary, opened, last-changed, owner, type, resolution_,
date-time, description/comment
--
You received this message because you are subscribed to the Google Groups "Trac
Users" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/trac-users?hl=en.
