Master detail report on one table (Delphi)
Note
This demo is available in your FlexCel installation at <FlexCel Install Folder>\Demo\Delphi\Modules\20.Reports\93.Master Detail on one Table and also at https://github.com/tmssoftware/TMS-FlexCel.VCL-demos/tree/master/Delphi/Modules/20.Reports/93.Master Detail on one Table
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.