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?

Cerelia

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:)

Phanerozoic

Thank you, but it’s helpless :( It doesn’t mention how to “range” and aggregate that range – Cerelia

So if I understand you correctly, you want to first filter rows by some condition and the group the filteretd rows? – Phanerozoic

I want to aggregate the filteretd rows – Cerelia

or any idea how get (Sum) of dataset field in memory. of course not using for loop. – Cerelia

somthing like what linq do in .Net, but only with dataset fields. – Cerelia

Aha! I didn’t try it with FILTER it can do the same job and it works fine, thanks for your help, appreciate it :) – Cerelia