Hi Naomi

>For SQL Server 2005 and up it's a piece of cake.
>
>See recursive CTE.

Thanks for the pointer, so does FireBird. FB 2.1 supports this, SQL-PT code:

WITH RECURSIVE Hier(iID, iPK) AS
   (SELECT p.iID, p.iID AS iPK FROM mItems p WHERE p.iID IN (SELECT ID
         FROM gttItemsSelected
         WHERE TRIM(JobID) = ?{vp_JobID})
   UNION ALL
   SELECT p2.iID, p2.iPID AS iPK FROM mItems p2
   JOIN Hier on p2.iPID = Hier.iID)
SELECT 2 AS iOrder, sBOMIItem.iID AS iPID, tBOMI.iID as iGID, tBOMI.tDt AS tDt,
      Hier.iPK AS iItemID, CAST(0 AS DOUBLE PRECISION) AS bAdd, sBOMIItem.bQty AS bLess, '!' AS cSource,
      'B.M.R. Issue no. ' || CASE WHEN tBOMI.cBK = '' THEN '' ELSE TRIM(tBOMI.cBK) || '/' END || TRIM(CAST(tBOMI.iNo AS CHARACTER(10))) || ' dtd. '
         || CAST (EXTRACT(DAY from tBOMI.tDt) AS CHAR(2)) || '/' || CAST (EXTRACT(MONTH from tBOMI.tDt) AS CHAR(2)) || '/'
         || CAST (EXTRACT(YEAR from tBOMI.tDt) AS CHAR(4)) AS mNarration
   FROM tBOMIssue tBOMI
      JOIN sBOMIssueItem sBOMIItem
         ON sBOMIItem.iPID = tBOMI.iID
      JOIN Hier
         ON sBOMIItem.iItemID = Hier.iID
WHERE tBOMI.tDt BETWEEN ?{ptFromDt} AND ?{ptToDt} ORDER BY tBOMI.tDt, tBOMI.cBk, tBOMI.iNo

My only issue now is that the Hier.iID contains the actual IDs of the items. Heir.iPK can only have 2 levels of hierarchy return the same ID for reporting purposes, grand-child and lower will always return their respective parent's ID. Actually I was hoping that all the returned transactions have the same ID for the items so I could separate 2 or more top-most-level IDs for grouping / ordering purposes in the report. If you have any insights into this problem of mine please let me know.

Kind regards.