Bidirectional reports (Delphi)

Note

This demo is available in your FlexCel installation at <FlexCel Install Folder>\Demo\Delphi\Modules\20.Reports\42.Bidirectional Reports and also at https:​//​github.​com/​tmssoftware/​TMS-​FlexCel.​VCL-​demos/​tree/​master/​Delphi/​Modules/​20.​Reports/​42.​Bidirectional Reports

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.