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:

  1. 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.

  2. 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.