<< Click to Display Table of Contents >> Navigation: Delphi HTML Report Library > Tables > Master-Detail |
Detail datasets nested to a DATAPACKET section has same format as top-level DATAPACKET but with tag name DETAIL. In detail packet SQL, values of master fields (current row) could be used as SQL parameters like :CUSTNO. For example:
select o.orderno, cast(saledate as date) saledate, cast(shipdate as date) shipdate,
paymentmethod, itemstotal, amountpaid, e.lastname
from orders o
join employee e on e.empno=o.empno
where custno=:custno
order by 1
Result:
You can use nested DATAPACKET in cell or row templates. In this case SQL parameters should be set via template variables, like {{master.CUSTNO}}
Example of a DATAPACKET embedded in cell
<DATAPACKET>
<METADATA>
<FIELDS>
<FIELD name="CUSTNO" caption="No"/>
<FIELD name="COMPANY" caption="Company"/>
<FIELD name="STATE" caption="State"/>
<FIELD name="CITY" caption="City" />
<FIELD name="Orders">
<template-cell>
<DATAPACKET width="700">
<METADATA>
<FIELDS>
<FIELD name="ORDERNO" caption="Order No" width="10%"/>
<FIELD name="SALEDATE" caption="Sale Date" width="10%"/>
<FIELD name="SHIPDATE" caption="Ship Date" width="10%"/>
<FIELD name="LASTNAME" caption="Employee" width="20%"/>
<FIELD name="PAYMENTMETHOD" caption="Payment Method" align="center" width="20%"/>
<FIELD name="ITEMSTOTAL" caption="Total" total="sum" width="15%"/>
<FIELD name="AMOUNTPAID" caption="Amount Paid" width="15%"/>
</FIELDS>
<SQL>
select o.orderno, cast(saledate as date) saledate, cast(shipdate as date) shipdate,
paymentmethod, itemstotal, amountpaid, e.lastname
from orders o
join employee e on e.empno=o.empno
where custno={{CUSTNO}}
order by 1
</SQL>
</METADATA>
</DATAPACKET>
</template-cell>
</FIELD>
</FIELDS>
<SQL>
select * from customer
</SQL>
</METADATA>
</DATAPACKET>
Result: