A report is simply a SQLite database query with special functions and output formatting appropriate for web presentation.
The basic idea is that a SQL query is run and each resulting row is formatted as a separate row in an HTML table. Table headers are determined dynamically from column names and, in a few cases, special columns get their own interpretations (to specify a row background color, or identify a column as containing linkable ticket numbers).
A report query can be written against most tables in the CVSTrac database and must contain a single SELECT statement. Subqueries are not allowed.
Viewing Reports
The Reports link brings up a list of all available reports. Each report can be viewed simply by selecting the title. The raw (unformatted) data can be seen (and downloaded) from within a report view using the Raw Data link.
The SQL used to generate the report can also be viewed at any time. This is useful if you wanted to write custom queries outside of CVSTrac against the SQLite database. Keep in mind, however, that some functions are only available inside CVSTrac. And, of course, you'll lose the meaning of some of the "special" columns.
Reports may also be embedded into wiki pages using the {report: rn} syntax, where rn is the report number as listed in the report URL (not the same number in the report list).
Creating a report
You can create a new report directly (following the appropriate link) or by making a copy of an existing report (particularly one that does almost what you need). In order to create or edit a report, a user must have the query permission set.
Each report should have a unique and self-explanatory title.
Tables
Most tables in the CVSTrac database are available for use in reports. However, keep in mind that the usual access restrictions are applied to all queries. In other words, users without checkout permissions will not be able to see the contents of the chng table.
Special Column Names
- bgcolor indicates an HTML table background color which will be used for the entire row. The column itself will now be displayed.
- A column named # indicates that the column contains ticket numbers. Numbers in that column will be represented as links to the appropriate ticket and an extra edit column will be shown for users with ticket write permissions.
- Columns named with a leading _ will be shown by themselves as separate rows. The row contents are assumed to be wiki formatted. This is useful for things like ticket remarks, descriptions, check-in comments, attachment descriptions, etc.
The wiki(), tkt() and chng() functions also give some control over column
formatting.
Available SQL Functions
See the SQLite documentation for the standard functions.
sdate()converts an integer which is the number of seconds since 1970 into a short date or time description. For recent dates (within the past 24 hours) just the time is shown. (ex: 14:23) For dates within the past year, the month and day are shown. (ex: Apr09). For dates more than a year old, only the year is shown.ldate()converts an integer which is the number of seconds since 1970 into a full date and time description.now()takes no arguments and returns the current time in seconds since 1970.now()is useful mostly for calculating relative cutoff times in queries.user()takes no arguments and returns the user ID of the current user.aux()takes a single argument which is a parameter name. It then returns the value of that parameter. The user is able to enter the parameter on a form. If a second parameter is provided, the value of that parameter will be the initial return value of theaux()function.aux()allows creation of reports with extra query capabilities, such as:
SELECT
cn as 'Change #',
ldate(date) as 'Time',
message as 'Comment'
FROM chng
WHERE date>=now()-2592000 AND user=aux('User',user())
ORDER BY date DESC
which allows a user to enter a userid and get back a list of check-ins made within the last 30 days.
option()takes a parameter name as an argument and, as with aux(), returns the value of that parameter. The user is able to select an option value from a drop down box. The second argument is a SQLite query which returns one or two columns (the second column being a value description). For example:
SELECT
cn as 'Change #',
ldate(date) as 'Time',
message as 'Comment'
FROM chng
WHERE date>=now()-2592000
AND user=option('User','SELECT id FROM user')
ORDER BY date DESC
cgi()returns the value of a CGI parameter (or the second argument if the CGI parameter isn't set). This is mostly useful in embedded reports.parsedate()converts an ISO8601 date/time string into the number of seconds since 1970. It would be useful along with theaux()function for creating queries with variable time.search()takes a (space separated) keyword pattern and a target text and returns an integer score which increases as more of the keywords are found in the text. The following scoring pattern is used:
| 0 | No sign of the word was found in the text |
| 6 | The word was found but not on a word boundry |
| 8 | The word was found with different capitalization |
| 10 | The word was found in the text exactly as given |
wiki()causes its argument to be rendered as wiki markup.tkt()causes its argument to be rendered as a ticket number. For example:
SELECT
tkt(tn) AS 'Tkt',
owner AS 'Owner',
FROM ticket
chng()causes its argument to be rendered as a check-in.path()is used to extract complete filename from FILE table. It takes 3 parameters: isdir, dir and base. For example:
SELECT path(isdir, dir, base) AS 'filename' FROM file
dirname()takes filename as only argument and extracts parent directory name from it.
SELECT dirname('/path/to/dir/') => '/path/to/'
SELECT dirname('/path/to/dir/file.c') => '/path/to/dir/'
basename()takes filename as only argument and extracts basename from it.
SELECT basename('/path/to/dir/') => 'dir'
SELECT basename('/path/to/dir/file.c') => 'file.c'