Bidirectional reports (Delphi)
Overview
In this example we will create a report that grows both horizontally and
vertically.
Concepts
Normally ranges in FlexCel reports can't intercept. But there is an
exception: If you define a range in the shape of a cross, FlexCel
will do a bidirectional report.
In Bidirectional reports the row range acts as the master, and
the column range as the detail. You can have more than one
column range for the same row, but not more than one row range
for the same column.
In this example we have two different column reports using the same
row report. In the first column we show which employees have
orders for a given customer, and in the second we show the orders
for that customer.
Those two columns are conceptually different: In the first we use a
column and row dataset which are not related. This will create a
square with all the records of the column dataset for the column,
and all the records of the row dataset for the rows. In the second
column, the column dataset (orders) is related to the row dataset
(customers). So the number of columns will be different for each
row, depending in how many orders that customer has. FlexCel will
insert as many columns as the maximum numbers of orders for any
customer.
You can look at the User Defined Functions example for another
demo on how bidirectional reports work.
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;
type
TMainForm = class(TForm)
btnCancel: TButton;
btnGo: TButton;
SaveDialog: TSaveDialog;
Label1: TLabel;
procedure btnCancelClick(Sender: TObject);
procedure btnGoClick(Sender: TObject);
private
procedure RunReport;
function GetDataPath: string;
{ Private declarations }
public
{ Public declarations }
end;
var
MainForm: TMainForm;
implementation
uses IOUtils, DemoOrders;
{$R *.dfm}
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('Employees', DemoTables.Employees);
Report.AddTable('Customers', DemoTables.Customers);
Report.AddTable('Orders', DemoTables.Orders);
//Orders here has 2 master-detail relationships: With Employees and Customer.
//We can't represent that with normal delphi master-detail relationships as each
//detail dataset can have only one master. So we will use FlexCel relationships instead.
Report.AddRelationship('Employees', 'Orders', 'EmployeeId', 'EmployeeId');
Report.AddRelationship('Customers', 'Orders', 'CustomerId', 'CustomerId');
Report.Run(
TPath.Combine(GetDataPath, 'Bidirectional Reports.template.xlsx'),
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.