Join and Union (Delphi)

Note

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

Overview

Sometimes you want to run unrelated reports in columns. One option is to run them as "_" ranges instead of "__" (see the Balanced Columns example). But this will break merged cells and ranges might not expand (see Joining Tables in the Report Designers guide). So other option is to "join" those unrelated tables into a single table, and run a single "__" range.

Also shown in this example is how to do a Union of Tables.

Concepts

• How to join unrelated tables into a bigger table. In this example we join Categories, Products and Shippers into a single table (CSP) which we use to run the report.

• How to access the fields of the joined table. If the joined tables have different column names, you can access those fields directly. For example, we write "<#CSP.CategoryName>" and this will access the column CategoryName of the Categories table in the CSP table, as none of the other joined tables has a similar column. But when we want to access CategoryId, that column is present in both Categories and Products. So we need to explicitly write the table name after CSP and before CategoryID. In this example, <#CSP.Products.CategoryId> references the column CategoryId of the table Prodcuts, and not Categories.

• It might be a good idea to always prefix the table being accessed when using a joined table. In this example we write <#CSP.Shippers.CompanyName> even when CompanyName is not in the other tables and we could just write <#CSP.CompanyName>. But somehow writing the full name will make it more future proof, not breaking if we later add another table. As in this example we wanted to show both variants, we used both forms (the short one: <#CSP.CategoryName> and the long one: <#CSP.Shippers.CompanyName>) But depending on your case and if there are chances of column names being the same, you might want to just use only the long form.

• How to use the "UNION" function to append the records of one table after another. In this example we do a union of Suppliers, Shippers and Customers and write the phone numbers of all of them into a single table.

For more information in balanced columns please take a look at the sections Joining Tables and Union of Tables in the Report Designers guide.

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(DemoTables);
    Report.SetValue('Date', Now);
    Report.Run(
      TPath.Combine(GetDataPath, 'Join and Union.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.