Relatively newbie question:

Can aggregate functions & grouping be applied to Collections and TypedViews or does one need to construct a TypedList either progromatically or via the GUI in order to achieve this?

Cheers, Steve

Smartini wrote:

Relatively newbie question:

Can aggregate functions & grouping be applied to Collections and TypedViews or does one need to construct a TypedList either progromatically or via the GUI in order to achieve this?

Aggregates, expressions and group by clauses can be used when fetching a collection, typed view, typed list or a dynamic list, except the group by is not useful when fetching entities so there it is not possible to specify a group by clause.

What do you use, selfservicing or adapter? And what particularly do you want to achieve? (So I can point you to the proper example/give you a proper example )

I'm using SelfServicing. I have a view (SQL2K) which in its simplest presents me with a list of purchases and organisations (PurchaseID, OrganisationID). All I need to do is both count all purchases per organisation and count all organisations per purchase (seperately of course). I have managed to achieve the results I want through a TypedList that I configured in the designer and following the code sample in the help docs but I'd rather not have to rely on this additional complexity when a group by & count() on a SQL view are perfectly sufficient.

Regards, Steve

Should do the trick:

// lets assume the typed view is called PurchasesTypedView
// We can't use Fill() as that routine grabs their own set of fields and we have to adjust the fields
// so we'll create two dynamic lists, using the typed view's fields
ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(PurchagesFieldIndex.OrganisationID, 0, "OrganisationID");
fields.DefineField(PurchagesFieldIndex.PurchaseID, 1, "NumberOfPurchases");

// first run, count purchases per organisation
DataTable purchagesPerOrganisation = new DataTable();
fields[1].AggregateFunctionToApply = AggregateFunction.Count;
GroupByCollection groupBy = new GroupByCollection();
groupBy.Add(fields[0]);
TypedListDAO dao = DAOFactory.CreateTypedListDAO();
dao.GetMultiAsDataTable(fields, purchagesPerOrganisation, 0, null, null, null, true, groupBy, null, 0, 0);

// second run, count organisations per purchase. I create a new set of fields as the order of fields differs
// but you don't have to do this.
fields = new ResultsetFields(2);
fields.DefineField(PurchagesFieldIndex.PurchaseID, 0, "PurchaseID");
fields.DefineField(PurchagesFieldIndex.OrganisationID, 1, "NumberOfOrganisations");
DataTable organisationsPerPurchase = new DataTable();
fields[1].AggregateFunctionToApply = AggregateFunction.Count;
groupBy = new GroupByCollection();
groupBy.Add(fields[0]);
dao.GetMultiAsDataTable(fields, organisationsPerPurchase, 0, null, null, null, true, groupBy, null, 0, 0);

Thanks for that. Couple of minor adjustments and all's well. Thanks for that. Steve