User defined tables (Delphi)
Note
This demo is available in your FlexCel installation at <FlexCel Install Folder>\Demo\Delphi\Modules\20.Reports\91.User Tables and also at https://github.com/tmssoftware/TMS-FlexCel.VCL-demos/tree/master/Delphi/Modules/20.Reports/91.User Tables
Overview
User defined tables allow you to specify the datasets in the report part of the data layer. Different from Direct SQL, user tables work in a more controlled environment, where you can specifically deny or grant access to the data. User Tables also allow access not SQL databases.
Concepts
To use User Tables you need the following things:
On the config sheet, write "User Table(parameters)" on the "Source Name" column, and something else on the "Table Name" column. What you write on the "Table Name" column is really not important, but you need to write something so FlexCel knows that the row has information. Normally you would use this column to tell FlexCel what is the name of the datatable you want to add, but this is not a requirement.
On the code, you need to define a UserTable event that will actually add the tables to the report base on what you write on the "parameters" and "table name" columns.
On this example, we just use the parameter as the name of the table we want to load, and use the "table name" column as the name of the table we will insert on the report. We could also use the parameter string as parameter to an SQL, but when doing this, please make sure you validate the parameter string against a hashtable of possible values, to avoid SQL injections attacks.
Even when we do not show it here, you could pass many parameters on the "Parameters" string, using your own defined parameter separator, and process this string on the event.
Unrelated to user tables, on this demo we also show how you can delete the categories that have no products, using a <#delete range> tag inside a <#if(<#products.#Rowcount>0 ) = 0> tag. Important note:* As FlexCel already deletes products that have no records, this will interfere with the <#Delete range> tag that will delete whole categories, and it would in fact delete one more row that what we need. So it is important that you make* **genericReport.DeleteEmptyRanges = false on the code before running the report.
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;
ADOConnection: TADOConnection;
procedure btnCancelClick(Sender: TObject);
procedure btnGoClick(Sender: TObject);
procedure FormCreate(Sender: TObject);
private
procedure RunReport;
function GetDataPath: string;
procedure LoadUserTables(const sender: TObject;
const e: TUserTableEventArgs);
function GetDataSet(const TableName: string): TDataSet;
function Query(const sql: string): TDataSet;
{ 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.UserTable := LoadUserTables;
Report.DeleteEmptyRanges := false;
Report.Run(
TPath.Combine(GetDataPath, 'User Tables.template' + TPath.GetExtension(SaveDialog.FileName)),
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;
function TMainForm.Query(const sql: string): TDataSet;
begin
Result := TADODataSet.Create(nil);
try
(Result as TADODataSet).Connection := ADOConnection;
(Result as TADODataSet).CommandText := sql;
except
Result.free;
raise;
end;
end;
function TMainForm.GetDataSet(const TableName: string): TDataSet;
begin
if (TableName = 'SUPPLIERS') then exit (Query('select * from suppliers'));
if (TableName = 'CATEGORIES') then exit (Query('select * from categories'));
if (TableName = 'PRODUCTS') then exit (Query('select * from products'));
raise Exception.Create('Unknown user table: ' + TableName);
end;
procedure TMainForm.LoadUserTables(const sender: TObject; const e: TUserTableEventArgs);
var
TableName: string;
ds: TDataSet;
begin
//On this example we will just return the table with the name specified on parameters
//but you could return whatever you wanted here.
//As always, remember to *validate* what the user can enter on the parameters string.
TableName := UpperCase(e.TableName);
ds := GetDataSet(TableName);
(sender as TFlexCelReport).AddTable(e.TableName, ds, TRecordCountMode.Normal, TDisposeMode.DisposeAfterRun);
end;
end.