Generic reports Part 2 (Delphi)

Note

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

Overview

In the previous example we saw how to use <#DataTable.*> and <#DataTable.**> to create generic reports. While this works in most common cases, there are times when you might need a more complex but also more powerful approach.

Concepts

You can create a generic report by doing a report that grows both in columns and in rows.
You can use Columns(Dataset) table definition in the config sheet to have a datasource that contains the columns of the dataset. You can then use this new datasource as a source for the horizontal report (with an I_range_I), so every column in the Excel file will have one entry in the datasource.
Different from "*" reports, here you can filter this column datasource and only show some columns, or order them.
You can use <#dbvalue> tag to get the value for a column or a row. Here we use it inside a report expression in the config sheet so we don't need to write the full dbvalue tag every time.
Different from "*" reports, columns here are not overwritten, they are inserted.
A problem with this type of reports is formatting. You will want to format all dates as dates, not numbers, and you might want to format numbers as currency and so on. Here we will use a simple user-defined function to return the datatype of the value, and if it is a date then format it as date.

Files

UDataTypeImp.pas

unit UDataTypeImp;

interface
uses FlexCel.Core, FlexCel.Report;

type
TDataTypeImp = class(TFlexCelUserFunction)
public
  function Evaluate(const parameters: TFormulaValueArray): TReportValue; override;
end;

implementation
uses SysUtils;

{ TDataTypeImp }

function TDataTypeImp.Evaluate(
  const parameters: TFormulaValueArray): TReportValue;
begin
  if length(parameters) <> 1 then raise Exception.Create('DataType must be called with one parameter.');
  if (parameters[0].IsDateTime) then exit('datetime');
  if (parameters[0].IsNumber) then exit('double');
  Result := '';
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, DB, ADODB,
  Grids, DBGrids, ComCtrls, ToolWin, ActnList, ImgList;

type
  TMainForm = class(TForm)
    SaveDialog: TSaveDialog;
    ToolBar1: TToolBar;
    ToolButton1: TToolButton;
    ToolButton2: TToolButton;
    ToolButton3: TToolButton;
    ToolButton4: TToolButton;
    DBGrid1: TDBGrid;
    ADOConnection: TADOConnection;
    Table: TADODataSet;
    Actions: TActionList;
    ActionQuery: TAction;
    ActionExportToExcel: TAction;
    ActionClose: TAction;
    DsTable: TDataSource;
    ToolbarImages: TImageList;
    ToolbarImages_300Scale: TImageList;
    ToolbarImages_100Scale: TImageList;
    procedure FormCreate(Sender: TObject);
    procedure ActionCloseExecute(Sender: TObject);
    procedure ActionQueryExecute(Sender: TObject);
    procedure ActionExportToExcelExecute(Sender: TObject);
  private
    procedure RunReport;
    function GetDataPath: string;
    { Private declarations }
  public
    { Public declarations }
  end;

var
  MainForm: TMainForm;

implementation
uses IOUtils, USQLDialog, UFlexCelHDPI, UDataTypeImp;

{$R *.dfm}

procedure TMainForm.ActionCloseExecute(Sender: TObject);
begin
  Close;
end;


procedure TMainForm.ActionExportToExcelExecute(Sender: TObject);
begin
  RunReport;
end;

procedure TMainForm.ActionQueryExecute(Sender: TObject);
begin
  if EnterSQL.ShowModal <> mrOk then exit;
  Table.Active := false;
  Table.CommandText := EnterSQL.SQL;
  Table.Active := true;
end;

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, []);
  Table.CommandText := 'select * from orders';
  Table.Active := true;
  RegisterForHDPI(Self, nil);
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('Table', Table);

    Report.SetValue('Date', Now);
    Report.SetValue('ReportCaption', Table.CommandText);
    Report.SetUserFunction('datatype', TDataTypeImp.Create);
    Report.Run(
      TPath.Combine(GetDataPath, 'Generic Reports 2.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.

USQLDialog.pas

unit USQLDialog;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics,
  Controls, Forms, Dialogs, StdCtrls;

type
  TEnterSQL = class(TForm)
    edSQL: TMemo;
    Label1: TLabel;
    btnCancel: TButton;
    btnOk: TButton;
  private
    { Private declarations }
  public
    function SQL: string;
    { Public declarations }
  end;

var
  EnterSQL: TEnterSQL;

implementation

{$R *.dfm}

{ TEnterSQL }

function TEnterSQL.SQL: string;
begin
  Result := edSQL.Text;
end;

end.

Table of Contents