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.