SQLite Database Tutorial: master / detail reports

A master/detail report with LazReport can be constructed based on a dataset obtained from a "joined query".

To do this, an SQL query is needed which returns data from both tables.

Master / detail report

For this project, create a folder FPlaz\SQLite05, download SQLite05.zip and unzip to this folder.

1. Start Lazarus and open project SQLite05.
2. In comparison to our previous project, the component queryReport was added.
   The SQL returns fields from both tables:
   
   select * from members,guitars where guitars.memberid = members.id
   order by members.lastname

3. Note that frDBDataSet1 has its property DataSet set to queryReport.
4. Double-clicking on the frReport1 icon brings up the report designer, where you can see the structure of our report.
   To begin with, we have a Group header band, with its condition set to the queryReport.ID data field (to see this, right click the band and select Edit...).
   Next, we added fields from the first table, Members.Lastname and Members.Firstname.
   
   The "Group header" band is output only when the value changes of the field to which it is linked, i.e. once for each new Member.ID. Otherwise, the masterdata band (see below) is displayed.
5. After the Group header band, a Master data band is added with fields from the second table, e.g. Guitars.Brand, Guitars.Model, etc...
6. We show a report by clicking btnReport:
   
   
   procedure TForm1.btnReportClick(Sender: TObject);
begin
  queryReport.Active := True;
  frReport1.LoadFromFile('guitarclub.lrf');
  frReport1.ShowReport;
  queryReport.Active := False;
end;
7. A few cosmetic improvements were added to our report.
   
   The report title was set in a different font and we added a frame around it:
   We have drawn a line under the group header:
   
8. We encourage you to add other elements, such as a page header band with page numbers, insert a picture, and so on.