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.