Report Grouping and Calculations

In addition, ASM allows you to do a number of transformations to the output HTML. You define a number of ``blocks'' to your reports. Anyone who has used a reporting tool (eg: Microsoft Access or Crystal) will recognise these.

• $$HEADER ... HEADER$$ - The topmost section of the report should be entered here.
• $$FOOTER ... FOOTER$$ - The bottom most section of the report should be entered here.
• $$BODY ... BODY$$ - The main section of your report where the detail goes.
• $$GROUP_<field> .. GROUP$$ - Optional grouping level. If you want to group by a particular field in the formatted output, you should add a group block. Within the group block you must also define a $$HEAD and $$FOOT section.

For example, to group on the OwnerName field:

$GROUP_OwnerName

$HEAD

<h2>$OwnerName</h2>

$FOOT

<p>Number of owners with name '$OwnerName': {COUNT.OwnerName}</p>

ASM also offers a number of calculation instructions you may use in group $$FOOT blocks and the main $$FOOTER block. These are:

• {SUM.<field>[.round]} - Calculates the total of a given field for a group. Optionally, you can specify the number of decimal places to round to.
• {COUNT.<field>} - Calculates the number of records in a group (the field is ignored)
• {AVG.<field>[.round]} - Calculates the average of a group, based on the total of the field and the number of records. Optionally, you can specify the number of decimal places to round to.
• {MIN.<field>} - Shows the smallest value for a given field in a group
• {MAX.<field>} - Shows the largest value for a given field in a group
• {FIRST.<field>} - Returns the first value for a given field in a group
• {IMAGE.<animalid>} - Returns the name of an image in the local cache (and retrieves it) for the picture for the given animal ID field. Animal IDs can be a $FIELD from the selected data. If no picture could be found for the animal, ``nopic.jpg'' is returned instead, so you can supply your own default picture.
• {LAST.<field>} - Returns the last value for a given field in a group
• {PCT.<field>.<value>[.round]} - Returns the percentage of rows in the group where field <field> matches value <value>. Optionally, you can specify a number of decimal places to round to. Eg: {PCT.Sex.1.2} Would show the percentage of male animals in the group, rounded to 2 decimal places.
• {SQL.<sql command>} - executes the SQL and outputs the value of the first field. Eg: {SQL.SELECT COUNT(*) FROM animal}. This tag is also available in the $$BODY block and can be used in conjunction with the field tags to produce a subquery. Eg: Suppose you wanted to show the number of movements attached to a particular animal without doing a group query with an inner join to adoption. You could just use {SQL.SELECT COUNT(*) FROM adoption WHERE AnimalID = $ID}
• {SUBREPORT.<title>.<parentkey>} - executes the custom report with [title] and with the [parentkey] being the name of a field from the current report data to pass down to the subreport. The report output is embedded in the current report. You can use the $PARENTKEY$ field in the subreport to access this value for filtering your data. Note that you can nest reports to an unlimited depth.

Here is an example report to show all animals currently on the shelter, with their picture, grouped by their internal location and giving totals for each.

SQL:

SELECT 

animal.ID, animal.AnimalName, animal.ShelterCode, 

internalLocation.LocationName

FROM animal

INNER JOIN internallocation ON 

    animal.ShelterLocation = internallocation.ID

WHERE animal.Archived = 0

ORDER BY internalLocation.LocationName

HTML:

$$HEADER

HEADER$$

 

$$GROUP_LocationName

$$HEAD

<h2>$LocationName</h2>

<table>

<tr>

<th>Name</th>

<th>Code</th>

<th>Picture</th>

</tr>

$$FOOT

</table>

<p><b>Total at $LocationName: {COUNT.AnimalName}</b></p>

GROUP$$

 

$$BODY

<tr>

<td>$AnimalName</td>

<td>$ShelterCode</td>

<td><img src={IMAGE.$ID} width=300 height=200 /></td>

</tr>

BODY$$

 

$$FOOTER

FOOTER$$