Generic reports Part 2 (Delphi)
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.