ASM SUBREPORT NotebookSheet Movements

SQL

SELECT
  an.ID,
  IF (LENGTH (an.ActiveMovementDate), 
              DATE (an.ActiveMovementDate), 
              '(none)') AS ActiveMovementDate,
  IF (LENGTH ((SELECT MovementType FROM lksmovementtype WHERE ID = an.ActiveMovementType)),
              (SELECT MovementType FROM lksmovementtype WHERE ID = an.ActiveMovementType),
              '(unknown)') AS ActiveMovementType,
  IF (LENGTH (o.OwnerName), 
              CONCAT (o.OwnerName, '<br />',
                      o.OwnerAddress, '<br />',
                      o.OwnerPostcode, '<br />',
                      o.HomeTelephone), 
              '(None Listed)') AS CurrentOwner
FROM
  animal AS an
LEFT OUTER JOIN
  adoption ad ON 
    ad.ID = an.ActiveMovementID
LEFT OUTER JOIN
  owner o ON 
    o.ID = ad.OwnerID
WHERE
  an.ID=$PARENTKEY$

HTML

$$HEADER
<h3>Last Movement</h3>
<table rules="all" border="1" cellpadding="5" cellspacing="5">
<tr>
  <th>Movement Type</th>
  <th>Date</th>
  <th>Owner</th>
</tr>
HEADER$$

$$BODY
<tr>
  <td>$ActiveMovementType</td>
  <td>$ActiveMovementDate</td>
  <td>$CurrentOwner</td>
</tr>
BODY$$

$$FOOTER
</table>
FOOTER$$