Record count (Delphi)

Note

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

Overview

FlexCel Reports need to know in advance how much records to insert. But some DataSet components in Delphi don't return the correct RecordCount, and we need to supply the number of rows to it. For more information read the section About Record Count in DataSets in the Report developer's guide.

Concepts

• We will be using ADO with a CursorType set to ctOpenForwardOnly. In this mode the record count returned is -1 (the other demos in this section use the default CursorType)

• We are showing 2 different ways to count the records:

  1. By using a __FLEXCELCOUNT field.

  2. By setting the parameter recordCountMode to SlowCount when calling TFlexCelReport.AddTable or TFlexCelReport.AddConnection.

Note

In this specific case setting SlowCount to true isn't strictly necessary. As RecordCount is -1, FlexCel can figure out that the number is wrong and do the slow count automatically. You need to specify SlowCount for those components that return the number of records fetched, like for example 25 because in those cases FlexCel can't know that this number isn't the real record count.

Files

DataFlexCelCount.pas

unit DataFlexCelCount;

interface

uses
  SysUtils, Classes, DB, ADODB;

type
  TTablesFlexCelCount = class(TDataModule)
    ADOConnection: TADOConnection;
    Categories: TADODataSet;
    Products: TADODataSet;
    procedure DataModuleCreate(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  TablesFlexCelCount: TTablesFlexCelCount;

implementation
uses IOUtils;
{%CLASSGROUP 'System.Classes.TPersistent'}

{$R *.dfm}

function DBFile: string;
begin
  Result := TPath.Combine(TPath.GetDirectoryName(ParamStr(0)), '..\..\..\SharedData\Northwind.mdb');
end;

procedure TTablesFlexCelCount.DataModuleCreate(Sender: TObject);
begin
  ADOConnection.ConnectionString := StringReplace(ADOConnection.ConnectionString, 'Northwind.mdb', DbFile, []);
end;

end.

DataSlowCount.pas

unit DataSlowCount;

interface

uses
  SysUtils, Classes, DB, ADODB;

type
  TTablesSlowCount = class(TDataModule)
    ADOConnection: TADOConnection;
    Categories: TADODataSet;
    Products: TADODataSet;
    DsCategories: TDataSource;
    procedure DataModuleCreate(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  TablesSlowCount: TTablesSlowCount;

implementation
uses IOUtils;
{%CLASSGROUP 'System.Classes.TPersistent'}

{$R *.dfm}

function DBFile: string;
begin
  Result := TPath.Combine(TPath.GetDirectoryName(ParamStr(0)), '..\..\..\SharedData\Northwind.mdb');
end;

procedure TTablesSlowCount.DataModuleCreate(Sender: TObject);
begin
  ADOConnection.ConnectionString := StringReplace(ADOConnection.ConnectionString, 'Northwind.mdb', DbFile, []);
end;

end.

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;
    CountOption: TRadioGroup;
    Label1: TLabel;
    procedure btnCancelClick(Sender: TObject);
    procedure btnGoClick(Sender: TObject);
  private
    procedure RunReport;
    function GetDataPath: string;
    procedure DoSlowCount;
    procedure DoFlexCelCount;
    { Private declarations }
  public
    { Public declarations }
  end;

var
  MainForm: TMainForm;

implementation
uses IOUtils, DataSlowCount, DataFlexCelCount;

{$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;
begin
  if not SaveDialog.Execute then exit;

  if CountOption.ItemIndex = 1 then DoSlowCount else DoFlexCelCount;

  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;

procedure TMainForm.DoSlowCount;
var
  Report: TFlexCelReport;
begin
  Report := TFlexCelReport.Create(true);
  try
    Report.AddTable(TablesSlowCount, TRecordCountMode.normal);
    Report.SetValue('Date', Now);
    Report.Run(
      TPath.Combine(GetDataPath, 'RecordCount.template.xls'),
      SaveDialog.FileName);
  finally
    Report.Free;
  end
end;

procedure TMainForm.DoFlexCelCount;
var
  Report: TFlexCelReport;
begin
  Report := TFlexCelReport.Create(true);
  try
    //the categories table in this data module change the sql to include the count.
    //The orders table doesn't need a FlexCelCount parameter, because it is related
    //with a FlexCel Relationship with categories. Tables that are filtered in the template,
    //or sorted, or have relationships added with AddRelationship will calculate the count automatically.
    Report.AddTable(TablesFlexCelCount);

    //for this example we will use a master detail relationship defined in FlexCel, instead of using one defined with mastersources.
    //note that there aren't datasources in the datamodule, Categories and Products aren't linked.
    Report.AddRelationship('Categories', 'Products', 'CategoryID', 'CategoryID');
    Report.SetValue('Date', Now);
    Report.Run(
      TPath.Combine(GetDataPath, 'RecordCount.template.xls'),
      SaveDialog.FileName);
  finally
    Report.Free;
  end
end;


end.