Header & Child Report

From SimIT Wiki

This tutorial guide you create a official document like Invoice/DO which have lot of information at header, and detail. The source data may come from quite a few record.

Prepare Database

1. Let's prepare 3 table which will use to generate for generate a official Invoice
2. Create 3 table as below:

  INVOICE
  invoice_id INT
  invoice_no varchar(20)
  customer_id INT
  description TEXT
  invoice_date date
  terms varchar(20)
  totalamt decimal(12,2)

  INVOICELINE
  invoiceline_id INT
  invoice_id INT
  product_name INT
  product_desc TEXT
  unitprice decimal(12,2)
  qty date
  uom varchar(10)
  lineamt decimal(12,2)

  CUSTOMER
  customer_id INT
  customer_no varchar(20)
  customer_name varchar(60)
  tel varchar(20)
  fax varchar(20)
  address text
  personincharge varchar(40)

3. With PHPMyAdmin, insert 1 customer record into table CUSTOMER(assume customer_id=1), 1 record into table INVOICE, make sure customer_id=1(I assume invoice_id =1 too).
4. Insert 3 record into table INVOICELINE, make sure the column invoice_id=1.
5. Now all 3 records is linked and we can join them together with SQL:

  SELECT c.*,i.*,il.* FROM INVOICE i
  INNER JOIN INVOICELINE il on il.invoice_id=i.invoice_id
  INNER JOIN CUSTOMER c on c.customer_id=i.customer_id
  WHERE invoice_id=1

6. Test SQL above from PHPMyAdmin and make sure there is valid record display in the screen.

Create Report

1. Base on previous tutorial, create a blank report
2. Create a parameter 'invoice_id' with default value=0
3. Modify SQL above, change 'WHERE invoice_id=1' to 'WHERE invoice_id=$P{invoice_id}'
4. Remove all bands at the report, leave 'Page Header', 'Detail 1', 'Page Footer', add Band Background
5. At the Report Inspector, drag field customer_no, cusomter_name,tel,fax,address,personincharge,terms,invoice_no,invoice_date into Page Header.
6. Make sure address text field height is 1 line height, but change it's property 'Stretch With Overflow' to true so it can grow during run time.
7. Format the text field at header properly, and place more static field at Page Header to make it more nicely.
8. From Report Inspector, drag field product_name,product_description, unitprice,qty,uom,lineamt into 'Detail 1' band, arrange and resize properly.
9. Place some static text at Page Header to explain data in Detail band.
10. Select all text field at Detail band, change 'Stretch With Overflow' property to true so that multi line data can extend nicely
11.Remain the selection, change Stretch Type to 'Relative To Band Height'.
12.Resize Page Header, Footer band to suit your need.
13.Make sure your detail band is as small as possible.
14.Drag description and totalamount into Page Footer, put in some static text to make the report look more nicely.
15.In Report Inspector, maximize the Background band and put in some Rectangle & Line in this band so that it show nice table for Invoice Detail.
16.Preview the report and submit 1 to parameter 'invoice_id'.
17.If report look nicely you can integrate it to PHP, do further tuning if you need.
18.You can refer report sample1-sample3 from the demo as a case study too.

Discussion

1. In most of the cases, using single SQL able to handle Header/Detail report very well.
2. If you need to perform calculation at run time under PHPJasperXML across multiple row. You can use SUM/AVERAGE/MAX and etc method via Variable method. You can refer official application of variable from iReport.
3. If you want to reformat the numbering/date/time format, you shall pre-format under SQL indeed of format at text field.
4. Eventhough address or description is multiline data, you shall draw the text field as single row and it will grow during run time.