Master detail report on one table (Delphi)
Overview
On this demo we see how to "split" one table into a master and a
detail table, in order to group the data. If you have the choice on how
to supply the report data is better to read different tables for
master-report (to avoid duplicate data). But if you already have the
data joined into one table, the Distinct and Relationship tags can help
you get it back into different tables.
Look at Grouping Tables
in the Report designer's guide for
more information.
Concepts
How to use the "DISTINCT" filter to create a new master
dataset with the unique records on a table.
You can use the DISTINCT filter on more than one column, by
separating them with ";". Only records that have any of the
columns different will be included.
How to use the "RELATIONSHIP" tag on the configuration sheet
to relate two tables in master-detail. Note that different from
the relationships you can set on the Data Layer, the relationships
on the config sheet can be between tables on different datasets.
The Relationship syntax is a little more complex than distinct. You
need to write "RELATIONSHIP" on the table name, then
"Master->Detail" on source name (where Master is the parent
table and Detail the child). After that, you need to write the
field names that relate both tables on the "Filter" column. You
can relate more than one field, with the syntax:
MasterTableField1->CDetailTableField1;MasterTableField2->CDetailTableField2...etc.
How to use the <#Merge Range> tag to merge all the cells in
the country column.
Files
UMainForm.pas
unit UMainForm;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics,
FlexCel.VCLSupport, FlexCel.Core, FlexCel.XlsAdapter, FlexCel.Report, FlexCel.Render,
{$if CompilerVersion >= 23.0} System.UITypes, {$IFEND}
ShellApi,
Controls, Forms, Dialogs, StdCtrls, ExtCtrls, DB, ADODB;
type
TMainForm = class(TForm)
btnCancel: TButton;
btnGo: TButton;
SaveDialog: TSaveDialog;
Label1: TLabel;
Data: TADODataSet;
ADOConnection: TADOConnection;
procedure btnCancelClick(Sender: TObject);
procedure btnGoClick(Sender: TObject);
procedure FormCreate(Sender: TObject);
private
procedure RunReport;
function GetDataPath: string;
{ Private declarations }
public
{ Public declarations }
end;
var
MainForm: TMainForm;
implementation
uses IOUtils;
{$R *.dfm}
function DBFile: string;
begin
Result := TPath.Combine(TPath.GetDirectoryName(ParamStr(0)), '..\..\..\SharedData\Northwind.mdb');
end;
procedure TMainForm.FormCreate(Sender: TObject);
begin
ADOConnection.ConnectionString := StringReplace(ADOConnection.ConnectionString, 'Northwind.mdb', DbFile, []);
end;
procedure TMainForm.btnCancelClick(Sender: TObject);
begin
Close;
end;
procedure TMainForm.btnGoClick(Sender: TObject);
begin
RunReport;
end;
function TMainForm.GetDataPath: string;
begin
Result := TPath.Combine(TPath.GetDirectoryName(ParamStr(0)), '..\..');
end;
procedure TMainForm.RunReport;
var
Report: TFlexCelReport;
begin
if not SaveDialog.Execute then exit;
Report := TFlexCelReport.Create(true);
try
Report.AddTable('Data', Data);
Report.SetValue('Date', Now);
Report.SetValue('ReportCaption', 'Sales by year and country');
Report.Run(
TPath.Combine(GetDataPath, 'Master Detail on one Table.template.xls'),
SaveDialog.FileName);
finally
Report.Free;
end;
if MessageDlg('Do you want to open the generated file?', mtConfirmation, [mbYes, mbNo], 0) = mrYes then
begin
ShellExecute(0, 'open', PCHAR(SaveDialog.FileName), nil, nil, SW_SHOWNORMAL);
end;
end;
end.