Delphi: How to aggregate just the ranged records in TClientDataset?

Question

I need to make some aggregates using TClientdataset. In SQL these aggregates can be done with script like this :

Select Sum(column1) from table1 where Date_Column < Date_Value

Because I need more speed during a very long process and a very slow network, I want to use in-memory aggregates instead of using sql . My idea is to add an aggregate to the ClientDataset with Expression like this:

Sum(column1)

And make Date_Column index, then filter the clientdataset like this:

myClientdataset.SetRang([value1],[value2]);

I was expecting to see the aggregate result of this range, but, unfortunately the aggregate ignore the range and keep giving the result of all records!

So, my question is: how I can achieve this in TClientdataset? Or, do you have any other idea how to do ranged-aggregates in memory?

Answer 1

Maybe this link will be helpful: Grouping and Aggregates

Edit:

I think i've got it, it took me quite time and it was a frustrating experience;)

I've made a sample project.

First, an aggregate without filtering:

Second, an aggregate after presing a button:

Is this is the effect which you wanted?

Please note that I was not able to achieve this efect with usage of SetRange(), I've used Filter property instead.

How to achieve:

1. Create an index on some field, GroupingLevel must be 0.
2. Setup that index as a property of TClientDataset.IndexName.
3. Create an aggregate with GroupingLevel = 0 and Expression like SUM(YourFieldName), in my case it was SUM(Population).
4. In IndexName property write the index which you've created.
5. Setup the aggregate to active in designtime, (setting it up in runtime seemed to not work).

My code after pressing a button:

  cdsMain.Filter := 'Population <= 100';
  cdsMain.Filtered := True;
  if not VarIsNull(cdsMain.Aggregates[0].Value) then
    lblAggregatedPopulation.Caption := 'Aggregated population: ' + IntToStr(cdsMain.Aggregates[0].Value);

Please note that in Filter you can easly change condition to achieve the same result as for the SetRange. However, I've read that SetRange is faster in filtering data.

Hope it helps:)