Master-Detail report - Flexcel3 to Flexcel 7

FLX TMS FlexCel for VCL & FMX
1

I have a problem converting Flexcel 3 to FlexCel 7.
Problem is when Detail dataset does not have records for first Master row. In that case none detail records are created even if Detail have records that match Master row.
In FlexCel 3 it works.

CDSMaster (fields: rbr, name)
1 first
2 second
3 third
CDSDetail (fields: rbr, rbr_det, name)
2 1 second 1
2 2 second 2
3 1 third 1
3 2 third 2
3 3 third 3

If I export all rows to excel then there are no detail rows in Excel file.
If I export records 2 and 3 from Master then everything is OK.
If I add row to Detial dataset
1 1
then all rows are exported in Excel file.

How to correct this situation (without adding dummy rows to Detail dataset)?
Thanks.

2

Hi,
I am not sure on what could be happening here, but this should work correctly. I made a small demo to try is, which you can get here: https://download.tmssoftware.com/flexcel/samples/master-detail.zip

It creates the report as expected:

2025-07-31_11.14.11
2025-07-31_11.14.111050×730 23.9 KB

Do you see anything different in your app that could cause it to behave differently? What I can think of is:

In the example I used in-memory ClientDataSets, so there is no real issue as no data is fetched from a server. But sometimes DataSets can return "0" as the record count if no records are fetched yet, and FlexCel will assume there are 0 records and so give empty results. I assume that when there is data in the first row, it fetches the full thing, so you don't see the error.

In FlexCel 3, we had a property "CalcRecordCount" in the FlexCelReport, which you could set to "SlowCount":

  /// <summary>
  /// Defines how TFlexCelReport will count the records in a TDataSet.<para></para>
  /// See <see cref="TFlexCelReport.CalcRecordCount" text="CalcRecordCount Property" /> for more
  /// information.
  /// </summary>                                                                                
  TCalcRecordCount=(

  /// <summary>
  /// FlexCel will assume RecordCount is returning a correct value. See <see cref="TFlexCelReport.CalcRecordCount" text="CalcRecordCount Property" />
  /// for more information.
  /// </summary>
  cr_None,

  /// <summary>
  /// FlexCel will do a db.Last command and assume the number returned by RecordCount is ok. See <see cref="TFlexCelReport.CalcRecordCount" text="CalcRecordCount Property" />
  /// for more information.
  /// </summary>
  cr_Count,

  /// <summary>
  /// FlexCel will count all records in the database, assuming RecordCount is returning an incorrect value.
  /// See <see cref="TFlexCelReport.CalcRecordCount" text="CalcRecordCount Property" /> for more
  /// information. 
  /// </summary>                                                                                           
   cr_SlowCount);

Can you check if your old reports have this property set? If so, you might need to set the same in FlexCel 7. Now it is not anymore a property of the full report, but for each individual dataset (so you can have some with slowcount and some with not).

In this example, to set slowcount you would change the AddTables to be:

    Report.AddTable('master', CDSMaster, TRecordCountMode.SlowCount);
   Report.AddTable('detail', CDSDetail, TRecordCountMode.SlowCount);

Note: if you are using FireDac, try setting RecordCountMode in the component itself, it will be faster than setting SlowCount in FlexCel.

There is more information here:

Let me know it this was the issue, or if the problem still persists.

3

Hi,
I've tried your example ad it works as expected.
We are using FDMemTables.

I have altered your example and added example with FDMemTables besides TClientDatasets.
Here is my example.
Master_Detail_FDMemTable.zip (98.9 KB)

In FDMemTable default RecordcountMode is "cmVisible".
I'm getting same results with "cmVisible", "cmTotal" or "cmFetched" set on Detail table.

That was working normally in Flexcel 3 (with the same results like your example with TClientDataSet).

I've added another FDMemTable and populate first detail so it have data and that example working with all rows shown.

Could you tell me what property I must set in FDMemTable(s) to get correct results?

Thanks.

4

Thanks for the extra information and reproducible case.
The problem seems to be that FDMemTable cleans its data when you close it, and when you open it again it is empty.

I wasn't aware that FDMemTable behaved this way, and it is the only DataSet I know that clears its data on close: as you've seen that's not the case with ClientDataSets, or any "non-memory" dataset as it will fetch its data from the server when you close it.

On our side, the problem is in this method, that will close and reopen a dataset when there are no records:

procedure TDatasetVirtualTableState.EnsureMDWorking;
begin
  if Data.Eof and (Data.DataSource <> nil) then
  begin
    //Datasets that have been opened by their masters might be in wrong state
    Data.Close;
    Data.Open;
  end;
end;

This is because some other datasets (normally those fetching on demand from a db, not memory datasets) go to an invalid state when we move the master, so if they report 0 records, we force a "hard" refresh by closing and reopening the ds and forcing it to fetch the new records. It is a workaround for a bug in some components, that triggers another bug (IMO) in another component.

It is hard to find a "best" solution:

  • If we remove that code, it will be fixed for FDMemTable, but it will break for those other datasets.
  • If we add a property: DoNotReopenDatasets or similar, nobody (not even myself) will remember about it 6 months from now, and nobody will know that they have to use the property if they are using TFDMemTables.
  • We can try some heuristics (if component.classname = 'TFDMemTable'...) but that will break in too many cases (imagine you have a descendant of TFDMemTable for example, the classname would be different). Also other dataset components (probably in-memory tables) might clear on close.

I will study a little more what can be done to solve this in the best way (or in the "less worse" way), but in the meantime, as a workaround, I see 3 options:

  1. If you are using a TFDMemTable in your code too, you could switch to a TList. TLists are much more reliable than TDataSets (where, as we've just seen, every implementation comes with its share of bugs of different behaviors). So, instead of loading this in a memtable, load it into a TList of objects, and run the report from there.
  2. TClientDataSet works, so it can be another choice
  3. You can make TFDMemTable persist its data, but it is not trivial as far as I know. You could set FireDAC.Stan.Option.TFDResourceOptions.Persistent - RAD Studio API Documentation to true and provide a filename, but then the data would be saved with your app, and be there when you restart it. Maybe you could use SaveToStream before running the report and save it to a memory stream, and on the OnAfterOpen event of the dataset, LoadFromStream the data.

As said, I don't like any of the possible solutions or the workarounds (except maybe converting all to TLists/TArrays if that is possible for you). But we will study what can be the best long term solution here.