Reports

Reports provide agregated view on data. In contrast to graphs reports provide text representation. Report is an HTML document base on a SQL query results. Report definition is a template for such a document. Connection between SQL query result and HTML document are report macros.

This example clears the technology.

The query sums the estimated effort, actual effort and the difference by issuetype.
In the template is defined the table with its header, column names and bottom sums and averages.

And this is the result:

Report
Issue type	Estimated effort	Actual effort	Difference
New feature	15	6	9
Enhancement	10	2	8
Bug	8	2	6
Sum:	33	10	23
Average:	11	3,33	7,67

Report Macros (custom tags):

<Datarecord> represents a single step repeated for each row of SQL select result. In the body is the processing. This macro needs a closing macro </Datarecord>
<Field Name="column name" [NullValue="some text for null value"] [MasterField="1"]/> inserts the column value.
<Index> is a simple counter. It starts with 1.
<Count> insert number of rows.
<Sum column name> inserts the sum of column 'name'
<Avg column name> inserts the average of column 'name'
<Date> inserts the current date
<Time> inserts the current time
<UserName> insert the logged (creator) user name
<UserShortname> insert logged (creator) user shortname (login)
<UserEmail> insert logged (creator) user email address
<UserPhone> insert logged (creator) user phone number
@Paramname inserts the parameter value into report

Reports are good not only for the client console user, but also for admin.

Alternative usage for reports

I made for me a report with all choicefields.

Select: select c.id as cid, c.name cname, i.id iid, i.name as iname from choicefield c, issuefield i where i.id=c.issuefield order by 3,1

Template:

<html>
   <body>
     <table border=1>
       <thead>
          <tr>
            <td colspan="4" align="center" bgcolor="#C0C0C0"><b>Report</b></td>
          </tr>
          <tr>
            <td>Issue field ID</td><td>Issue field name</td><td>Choice field ID</td><td>Choice field Name</td></tr>
       </thead>
       <Datarecord>
         <tr>
            <td><Field Name="IID"/></td>
            <td><Field Name="INAME"/></td>
            <td><Field Name="CID"/></td>
            <td><Field Name="CNAME"/></td>
         </tr>
       </Datarecord>
     </table>
   </body>
</html>

And I get this nice list, which helps me design the database.

Conditional output

It is possible to conditional report processing. Supported syntax:


<Condition [Template="Template"] SimpleCondition="FIELD_NAME=VALUE">some body</Condition>

%body in the template represents the tag body. If the condition is true the tag body will be used otherwise the template.

If you want to display ISSUETYPE=1 in bold, so you can use this snipet:
<Condition SimpleCondition="ISSUETYPE=1" Template="<b>%body</b>"> <Field Name="ISSUETYPE"/></Condition>

Master-detail

Why do you need it? I have one practical case. You want to print out your issues and all notes with them. So what do you need for it.
1. The data: select i.id, i.name, n.text from issue i left outer join note n on n.issue=i.id order by i.id
2. A good template:

<html>
	<body>
     	<table border="1">
     		<datarecord>
	     		<Condition NewMasterFields="1">
		     		<tr>
		     			<td><Field Name="ID" MasterField="1"/></td>
		     			<td><Field Name="NAME" MasterField="1"/></td>
		     		</tr>
	     		</Condition>
	     		<Condition SimpleCondition="TEXT=">
		     		<tr>
		     		<td><DetailIndex>/<DetailCount></td>
		     		<td><Field Name="TEXT"/></td>
		     		</tr>
	     		</Condition>
     		</datarecord>
    	</table>
	</body>
</html>

3. Some explanation:
MasterField="1" marks the master datarecord fields
<Condition NewMasterFields="1"> is true in case of new master datarecord, which in turn means this condition returns true wenn the concatation of all master field string representation changes. In our example the join returns the issue name and id for each issue's note.
<Condition SimpleCondition="TEXT="> this prevents from generating of empty table line for issues withou note.
<DetailIndex>Iterates through a group
<DetailCount>Number of items in the group

Feel free to make reports with issues per user, module or whatever you need.

See also:
Reports in tutorial
Queries
Parameters
Stylesheets