sql and delphi devexpress tcxtreelist

Question

I am trying to create a query that will be able to load into a tcxtreelist in delphi

I have a structure like this

-Season

• Month

  • week

All I have is the structure. I still need to know how to constructed my query, so I can load this onto my treelist

if anyone knows how to do this, that really appreciate your help

Answer 1

I'm not sure the cxDBTreeList, which was my first thought, is really suited to your purpose because it only works for a self-referencing dataset (See the Devex online help for what this means). On the other hand, it is quite straightforward, if a little long-winded to set up a cxTreeList to display your data.

In the following example, for simplicity I've left out the "week" level of your structure and replaced the "Season" level by a "Quarter" (three-month period) one.

To try the example below:

1. Create a new project, and on its form, drop a TClientDataSet named CDS1 and a TcxTreelist.

   Also, drop a TDataSource and TDBGrid onto the form and connect them up to the CDS in the usual way so that you can see the data you're working with.

2. Edit the code of the main form as shown below. It's probably easiest if you create a new OnCalcFields event for CDS1 ond then cut'n paste the calcfields code into it.

3. As you'll see from the code, the calculated fields are actually of type fkInternalCalc. The reason for this is so that the CDS can be indexed on them (unlike fxCalculated fields which don't permit this).

4. The project is intended to be as self-contained as possible: that's why the CDS's fields and the cxTreeList columns are all created in code, and why the project uses a CDS as the dataset, so that all the data can be created in code and doesn't require an external database or server.

5. You'll see that once the Quarter and Month nodes are set up, it's pretty trivial to "hang" the individual data rows off them (in the while not CDS1.eof loop).

6. The Description calculated column is there so as to be able to display some information specific to an individual data row in the cxTreeList. Obviously, you could have columns which get their values from individual dataset fields instead if you wanted.

Code:

type
  TForm1 = class(TForm)
    cxTreeList1: TcxTreeList;
    CDS1: TClientDataSet;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    procedure FormCreate(Sender: TObject);
    procedure CDS1CalcFields(DataSet: TDataSet);
  private
    CDS1ID: TIntegerField;
    CDS1ADate: TDateTimeField;
    CDS1Name: TStringField;
    CDS1Month: TIntegerField;
    CDS1Description: TStringField;
    CDS1Quarter: TIntegerField;
    colQuarter : TcxTreeListColumn;
    colMonth: TcxTreeListColumn;
    colDataRow: TcxTreeListColumn;
  protected
  public
    QuarterNodes : array[1..4] of TcxTreeListNode;
    MonthNodes : array[1..12] of TcxTreeListNode;
  end;

[...]

procedure TForm1.FormCreate(Sender: TObject);
var
  i : Integer;
  Quarter,
  Month : Integer;
  NewNode : TcxTreeListNode;
begin
  // First, create the dataset's fields
  CDS1ID := TIntegerField.Create(Self);
  CDS1ID.FieldName := 'ID';
  CDS1ID.DataSet := CDS1;

  CDS1Name := TStringField.Create(Self);
  CDS1Name.Size := 20;
  CDS1Name.FieldName := 'Name';
  CDS1Name.DataSet := CDS1;

  CDS1ADate := TDateTimeField.Create(Self);
  CDS1ADate.FieldName := 'Date';
  CDS1ADate.DataSet := CDS1;

  CDS1Quarter := TIntegerField.Create(Self);
  CDS1Quarter.FieldName := 'Quarter';
  CDS1Quarter.FieldKind := fkInternalCalc;
  CDS1Quarter.DataSet := CDS1;

  CDS1Month := TIntegerField.Create(Self);
  CDS1Month.FieldName := 'Month';
  CDS1Month.FieldKind := fkInternalCalc;
  CDS1Month.DataSet := CDS1;

  CDS1Description := TStringField.Create(Self);
  CDS1Description.Size := 80;
  CDS1Description.FieldName := 'Description';
  CDS1Description.FieldKind := fkInternalCalc;
  CDS1Description.DataSet := CDS1;

  //  Next create the dataset's index and data rows
  CDS1.CreateDataSet;
  CDS1.IndexFieldNames := 'Quarter;Month;ID';

  for i := 1 to 20 do begin
    CDS1.Insert;
    CDS1ID.AsInteger := i;
    CDS1Name.AsString := 'Row' + IntToStr(i);
    CDS1ADate.AsDateTime := Now - 365 + random(366); //  This sets the ADate field
    //  to a date in the past year
    CDS1.Post;
  end;

  try
    //  Next set up the cxTreeList's columns
    cxTreeList1.BeginUpdate;

    colQuarter := cxTreeList1.CreateColumn(Nil);
    colQuarter.Caption.Text := 'Quarter';

    colMonth := cxTreeList1.CreateColumn(Nil);
    colMonth.Caption.Text := 'Month';

    colDataRow := cxTreeList1.CreateColumn(Nil);
    colDataRow.Caption.Text := 'DataRow';
    colDataRow.Width := 300;

    //  Set up the top level (Quarter) and next level (Month) nodes
    for Quarter := 1 to 4 do begin
      QuarterNodes[Quarter] := cxTreeList1.Root.AddChild;
      QuarterNodes[Quarter].Values[0] := Quarter;
      for Month := 1 to 3 do begin
        MonthNodes[(Quarter - 1) * 3 + Month] := QuarterNodes[Quarter].AddChild;
        MonthNodes[(Quarter - 1) * 3 + Month].Values[0] := QuarterNodes[Quarter].Values[0];
        MonthNodes[(Quarter - 1) * 3 + Month].Values[1] := (Quarter - 1) * 3 + Month;
      end;
    end;

    //  Next, create individual nodes for the Data rows and add them as children
    //  of the relevant month
    CDS1.DisableControls;
    try

     CDS1.First;
     while not CDS1.Eof do begin
       Month := CDS1Month.AsInteger;
       NewNode := MonthNodes[Month].AddChild;
       NewNode.Values[0] := MonthNodes[Month].Values[0];
       NewNode.Values[1] := MonthNodes[Month].Values[1];
       NewNode.Values[2] := CDS1Description.AsString;
       CDS1.Next;
     end;
    finally
      CDS1.First;
      CDS1.EnableControls;
    end;
  finally
    cxTreeList1.FullExpand;
    cxTreeList1.EndUpdate;
  end;
end;

procedure TForm1.CDS1CalcFields(DataSet: TDataSet);
var
  AYear, AMonth, ADay, AHour, AMinute, ASecond, AMilliSecond: Word;
  ADayNumber,
  AWeekNumber : Word;
  ADate : TDateTime;
  S : String;
begin
  ADate := CDS1ADate.AsDateTime;
  DecodeDateTime(ADate, AYear, AMonth, ADay, AHour, AMinute, ASecond, AMilliSecond);

  CDS1Quarter.AsInteger := 1 + AMonth div 4;
  CDS1Month.AsInteger := AMonth;

  CDS1Description.AsString := Format('ID: %d, Name: %s, Date: %s', [CDS1ID.AsInteger, CDS1Name.AsString, CDS1ADate.AsString]);

end;