Bidirectional Report

Hi,


I am trying to create a bidirectional report that expands both horizontally and vertically. On both axes, there are an unknown number of rows/columns. I have been trying to use TLists and a hierarchical class system. Is it possible to use the addtable method to add the data set for each date along the horizontal? See code below. I am on Delphi Tokyo 10.2. Thanks.

TEMPLATE:

Time <#MondayDates.Date>
<#MondayDates.MondayTime> <#MondayDates.MondayDates>

And the result being:

Time 02/01/92 03/01/92 04/01/92 05/01/92
12:00:00 AM 1.00 2.00 1.00 3.00
1:00:00 AM 2.00 5.00 6.00 9.00
2:00:00 AM 3.00 6.00 8.00 6.00
3:00:00 AM 4.00 2.00 4.00 4.00
4:00:00 AM 5.00 3.00 5.00 3.00

CODE:

    TMondayTime = class
  public
    Time: String;
  end;

    TMondayData = class
  public
    Data: String;
    MondayTimeSet: TList<TMondayTime>;
  end;

    TMondayDate = class
  public
    Date: String;
    MondayDataSet: TList<TMondayData>;
  end;



procedure dataexport;
var
Report: TFlexCelReport;
saveDialog: TSaveDialog;
MondayDates: TList<TMondayDate>;
MondayDate: TMondayDate;
MondayData: TMondayData;
MondayTime: TMondayTime;
MondayData1: TMondayData;
MondayTime1: TMondayTime;
r, k: integer;
begin

saveDialog := TSaveDialog.Create(frmUI);
if not SaveDialog.Execute then exit;

Report := TFlexCelReport.Create(true);

    r := 1;
    MondayDates := TList<TMondayDate>.Create;
    While frmMonday.StringGrid1.Columns[r].Header <> '' do
    begin
    MondayDate := TMondayDate.Create;
    MondayDate.Date := frmMonday.StringGrid1.Columns[r].Header;
    MondayData := TMondayData.Create;
    MondayDate.MondayDataSet := TList<TMondayData>.Create;
    MondayTime := TMondayTime.Create;
    MondayData.MondayTimeSet := TList<TMondayTime>.Create;
    For k := 0 to frmUI.SharedData.Data_Interval do
    begin
      MondayData1 := TMondayData.Create;
      MondayData1.Data := frmMonday.StringGrid1.cells[r,k];
      MondayDate.MondayDataSet.Add(MondayData1);
      MondayTime1 := TMondayTime.Create;
      MondayTime1.Time := frmMonday.StringGrid1.cells[0,k];
      MondayData.MondayTimeSet.Add(MondayTime1);
    end;
    inc(r);
    MondayDates.Add(MondayDate);
    end;

    Report.AddTable<TMondayDate>('MondayDates', MondayDates);

    Report.Run('\Mac\Home\Documents\Embarcadero\Studio\Projects\Working1\Flow Anlaysis Simulation.xltx',SaveDialog.FileName);
  finally
    Report.Free;
    saveDialog.Free;
  end;
end;

Hi,
Doing a report with the nesting in this way is not possible as
far as I can see it, because you have 2 independent ranges (the times
and the dates), but in the data model times are inside the dates.

There
should be possible to do something by using the nested names: that is
instead of <#MondayDates.MondayTime> you should use
<#MondayDates.MondayDataset> since that is the field that
MondayDates exposes. But I don't think it makes much sense to try to
make this model hierarchically at all: For this case a relational model
(which FlexCel also handles) comes much more naturally.

So what I would do is to have 3 datasets:
MondayDates: TObjectList<TMondayDate>;
MondayTimes: TObjectList<TMondayTime>;
MondayData: TObjectList<TMondayData>;

(side note: I used TObjectList instead of TList so the classes inside the list are freed when you free the list).

Where:
  TMondayDate = class
  public
    Date: String;
    constructor Create(const aDate: string);
  end;

  TMondayTime = class
  public
    Time: String;
    constructor Create(const aTime: string);
  end;

  TMondayData = class
  public
    Data: String;
    Date: string;
    Time: string;
    constructor Create(const aDate, aTime, aData: string);
  end;

And then add the relationships:
    Report.AddRelationship('MondayTimes', 'MondayData', 'Time', 'Time');
    Report.AddRelationship('MondayDates', 'MondayData', 'Date', 'Date');

You might even use a single dataset and use Distinct to get the Dates and Times datasets.
Once you have the 3 datasets related, the template is just writing the 3 ranges. I attach an example here:
http://www.tmssoftware.biz/flexcel/samples/bidilist.zip

Note that this example is more on the lines of:
http://www.tmssoftware.biz/flexcel/doc/vcl/samples/delphi/reports/user-defined-functions/index.html#files
In that I use 3 ranges (one for the data, one for the dates, one for the time), but it could also be done like
http://www.tmssoftware.biz/flexcel/doc/vcl/samples/delphi/reports/bidirectional-reports/index.html

But
to do it like in this second case you would define only the "Dates" and
"Times" datasets, then use an user defined function that takes a date
and a time and returns a data.
The template would be like this:

Time <#MondayDates.Date>
<#MondayTimes.Time> <#GetData(<#MondayDates.Date>;<#MondayTimes.Time>)>
And you would add an user defined function GetData that returns the data for a time and a date.
In this case you wouldn't need to add relationships, but I think using an user defined function for this might be a little overkill.

That's fantastic, thank you. It worked perfectly. Really enjoying using Flexcel and I commend you for the fast and detailed support.