Search Results for

    Show / Hide Table of Contents

    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.
    
    In This Article
    Back to top FlexCel Studio for VCL and FireMonkey v7.24
    © 2002 - 2025 tmssoftware.com