YOUSEFUL / DELPHI / COMPONENTS

`TAtlasPivot`

Summarizes a TAtlasTable by two dimensions into a new table with row, column and grand totals.

Non-visual component Palette: Atlas Unit Atlas.Pivot · Inherits TComponent · Product Atlas VCL

Description

TAtlasPivot (unit Atlas.Pivot) summarizes one value column of a TAtlasTable by two dimension columns and returns the result as a new table, ready for a TAtlasGrid or TAtlasPrintDocument. The component has no properties; Summarize does the work. It reads the source's current view, so filter the source first to summarize a subset.

The result has one row per distinct value of the row dimension plus a final Grand total row. Its columns are Rows: <name>, one number column per distinct value of the column dimension headed 1: <value>, 2: <value>, ... (numbered so that empty or look-alike keys stay unique), and a final Grand total column. Keys are compared exactly, so East and east are two groups, and sorted with the default ordinal string comparison. All result columns are read-only. The result is a snapshot: summarize again after the source changes. It is not an interactive pivot grid: there is no drag-and-drop of fields, no drill-down and no more than two dimensions.

The returned table has no owner: free it, or give it one with InsertComponent.

Members

Member	Kind	What it does
`Summarize`	method	Groups the view of `Source` by the columns `RowDimension` and `ColumnDimension` and aggregates `ValueColumn` with `Aggregate` (TAtlasAggregate) into group cells, row totals, column totals and a grand total.

Methods

`Summarize` method

function Summarize(Source: TAtlasTable; RowDimension, ColumnDimension, ValueColumn: Integer; Aggregate: TAtlasAggregate): TAtlasTable;

Groups the view of Source by the columns RowDimension and ColumnDimension and aggregates ValueColumn with Aggregate (TAtlasAggregate) into group cells, row totals, column totals and a grand total. Averages are computed from the original values, not from group averages. For every aggregate except aaCount, empty values are skipped and any other non-numeric value raises EAtlasDataError (Pivot aggregation requires finite numeric values), so pass a number column. Combinations without values show 0 for aaCount and aaSum and an empty cell otherwise. Numbers use the invariant format (111200.5). Raises EAtlasDataError when Source is nil or an index is out of range. The caller owns the returned table.

Summary := Pivot.Summarize(Sales, 0, 1, 2, aaSum);
try
  ...
finally
  Summary.Free;
end;

Examples

Print a summary

Prints the header Rows: Region 1: Q1 2: Q2 Grand total and the rows East 120 150 270, West 80 0 80, Grand total 200 150 350 (padded into columns).

uses System.SysUtils, Atlas.Data, Atlas.Pivot;

procedure PrintQuarterlySales;
var
  Sales, Summary: TAtlasTable;
  Pivot: TAtlasPivot;
  Row, Col: Integer;
  Line: string;
begin
  Sales := TAtlasTable.Create(nil);
  Pivot := TAtlasPivot.Create(nil);
  try
    Sales.AddColumn('Region');
    Sales.AddColumn('Quarter');
    Sales.AddColumn('Sales', ackNumber);
    Sales.AddRow(['East', 'Q1', '100']);
    Sales.AddRow(['East', 'Q2', '150']);
    Sales.AddRow(['West', 'Q1', '80']);
    Sales.AddRow(['East', 'Q1', '20']);
    Summary := Pivot.Summarize(Sales, 0, 1, 2, aaSum);
    try
      for Row := -1 to Summary.RowCount - 1 do
      begin
        Line := '';
        for Col := 0 to Summary.ColumnCount - 1 do
          if Row < 0 then
            Line := Line + Summary.Columns[Col].Name.PadRight(14)
          else
            Line := Line + Summary.Cell(Col, Row).PadRight(14);
        Writeln(Line.TrimRight);
      end;
    finally
      Summary.Free; // the caller owns the result
    end;
  finally
    Pivot.Free;
    Sales.Free;
  end;
end;

Show a summary in a grid

The form owns the summary table and frees it with the grid.

uses Vcl.Forms, Vcl.Controls, Atlas.Data, Atlas.Grid, Atlas.Pivot;

procedure ShowSalesByRegion(Form: TForm; Sales: TAtlasTable);
var
  Pivot: TAtlasPivot;
  Summary: TAtlasTable;
  Grid: TAtlasGrid;
begin
  Pivot := TAtlasPivot.Create(nil);
  try
    // Rows: column 0 (Region); columns: column 1 (Quarter); values: column 2 (Sales).
    Summary := Pivot.Summarize(Sales, 0, 1, 2, aaSum);
  finally
    Pivot.Free;
  end;
  Form.InsertComponent(Summary); // the form now owns and frees the summary
  Grid := TAtlasGrid.Create(Form);
  Grid.Parent := Form;
  Grid.Align := alClient;
  Grid.Table := Summary;
end;

TAtlasAggregate · TAtlasTable · TAtlasGrid · TAtlasPrintDocument

Also in Atlas VCL

TAtlasTable · TAtlasGrid · TAtlasValueEdit · TAtlasTree · TAtlasWorkbook · TAtlasSheetGrid · TAtlasInspector · TAtlasRichEdit · TAtlasSpellChecker · TAtlasChart · TAtlasGauge · TAtlasThemer · TAtlasWizard · TAtlasWorkspace · TAtlasCommandBar · TAtlasActionTile · TAtlasTileBoard · TAtlasNavigation · TAtlasMap · TAtlasGraph · TAtlasDiagram · TAtlasPrintDocument · TAtlasSchedule · TAtlasScheduler · TAtlasTaskPlan · TAtlasGantt · TAtlasPdfDocument · TAtlasPdfViewer

TAtlasPivot