.. 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.
* $$NODATA … NODATA$$$ - A special block to output if the report query returns
no data.
* $$HTMLHEADER … HTMLHEADER$$$ - Override the report template HTML header and
supply a new one instead.
* $$HTMLFOOTER … HTMLFOOTER$$$ - Override the report template HTML footer and
supply a new one instead.
For example, to group on the OwnerName field::
$$GROUP_OwnerName
$$HEAD
$OwnerName
$$FOOT
Number of owners with name '$OwnerName': {COUNT.OwnerName}
GROUP$$
ASM also offers a number of calculation instructions you may use in group
$$FOOT blocks and the main $$FOOTER block. These are:
* {SUM.[.round]} - Calculates the total of a given field for a group.
Optionally, you can specify the number of decimal places to round to.
* {COUNT.[.distinct]} - Returns the number of records in the group.
If the optional distinct parameter is set, returns the number of unique
values of within the group.
* {AVG.[.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.} - Shows the smallest value for a given field in a group
* {MAX.} - Shows the largest value for a given field in a group
* {FIRST.} - Returns the first value for a given field in a group
* {IMAGE.[.seq]} – (also valid in $$BODY) Returns a URL to the
preferred image for the given animal ID. Animal IDs can be a $field from the
selected data. If the optional sequence number is given, then a link to image
seq (where seq is a 1-based count) for the animal is output instead. If seq
is invalid, or the animal doesn't have a preferred image the default system
nopic.jpg file is used instead.
* {CHIPMANUFACTURER.} - (also valid in $$BODY) Returns the name of
the microchip manufacturer of the given chip number.
* {QR.[.size]} – (also valid in $$BODY) Returns a URL to the google
charting api to generate a QR code with a link to this animal's record for
mobile devices. If size is not set, then 150x150 is used. Eg:
{QR.$ID.200x200}
* {LAST.} - Returns the last value for a given field in a group
* {PCT..[.round]} - Returns the percentage of rows in the group
where field matches 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.} - 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..[.]} - 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:: You can nest reports to an unlimited depth. You can pass an unlimited number of fields to a subreport, accessible as $PARENTARGX$ where X is the number of the argument you want to access in the subreport query.
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::
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
$$HEADER
HEADER$$
$$GROUP_LocationName
$$HEAD
$LocationName
Total at $LocationName: {COUNT.AnimalName}
GROUP$$
$$BODY
$AnimalName |
$ShelterCode |
|
BODY$$
$$FOOTER
FOOTER$$
Charts
------
.. image:: images/reports_chart.png
Charts work just like the reports. The difference is that instead of generating HTML, you just choose a chart type from the type dropdown.
ASM will dissect the results of the query in the following way to generate a graph, depending on whether your query returns two or three columns.
Two column variant
^^^^^^^^^^^^^^^^^^
1. Each row in your result data becomes a point on the X axis
2. The first column in each row is assumed to contain the name used for the
label on the X axis. Only string data should be used for the first column.
3. Each subsequent column is assumed to be a piece of data at that point on the
X axis (and thus gives the Y axis its scale). Only numeric data can appear
in columns after the first one.
4. The fieldname of each column after the label column is used to denote the
data on the legend.
For example, this SQL will produce a graph that shows the amount of donations
from animal movements each year. Following our scheme, the Year column being
first is the X axis label, TotalAmount is the first datapoint for that item on
the X axis::
SELECT YEAR(DateReceived) AS Year,
SUM(ownerdonation.Donation) AS TotalAmount
FROM ownerdonation
WHERE MovementID > 0
GROUP BY YEAR(DateReceived)
ORDER BY Year
Would produce data that might look something like:
==== ===========
Year TotalAmount
==== ===========
2004 59840
2005 61893
2006 51039
2007 55984
==== ===========
.. note:: Graphs use a relative scale, interpreted from the data. If all of your column data is the same value, then the graph will not display because it has no variance.
Three column variant
^^^^^^^^^^^^^^^^^^^^
1. Each row in your result data becomes a point on the X axis
2. The first column in each row is assumed to contain the name used for a
series.
3. The second column in each row contains the X axis label.
4. The third column contains the value for the Y axis and must be numeric.
For example, this SQL produces a graph that shows the number of donations made
for over a year by internal location. Each location becomes a series with a set
of data points for each month of the year::
SELECT i.LocationName,
MONTH(ad.MovementDate) AS bimonth,
COUNT(ad.Donation) AS total
FROM adoption ad
INNER JOIN animal a ON ad.AnimalID = a.ID
INNER JOIN internallocation i ON i.ID = a.ShelterLocation
WHERE YEAR(ad.MovementDate) = $ASK STRING Which year to display for?$
AND ad.MovementType = 1
GROUP BY i.LocationName, MONTH(ad.MovementDate)
Maps
----
Maps are just like the reports. The difference is that instead of generating
HTML, they will output map markers from a LatLong field.
ASM expects map queries to return a resultset containing at least two columns. The first
is the LatLong marker for the map, and the second is the text to display in the
popup when the marker is clicked.
For example, this SQL will produce a map that shows the location of every
person on file, with their address when the marker is clicked::
SELECT LatLong, OwnerAddress FROM owner
If there are more than two columns in the query results, the report engine will
concatenate together the values of each subsequent column.
This SQL will produce a map with every person on file, but includes a clickable
link to the person record when the marker is clicked::
SELECT LatLong,
'',
OwnerName,
''
FROM owner ORDER BY ownername
Automatic updating of repository reports
----------------------------------------
When you install a report from the repository, some reports have a revision
number. This number will be shown in the list on the reports editing screen
next to the report's title.
If we update the repository with a newer version of that report, it will get a new
revision number. Each time you visit the home page, the system will automatically
check for newer versions of reports. If it finds any, it will update the SQL and HTML
of your copy of the report to the latest version. It will only change the SQL/HTML
and update any subreports, it will not change the description if you updated it.
If you change the title or category on your copy of a report, this will prevent
it being updated by the auto updater. You can use this to lock a report to a
particular version.