One of the other things that you can do with the IRepositroy collection in SalesLogix is to perform a summation of a entity field after filtering for a group of records. Lets look at how to do this:
Sage.Platform.RepositoryHelper<Sage.Entity.Interfaces.IOpportunity> repository =
Sage.Platform.EntityFactory.GetRepositoryHelper<Sage.Entity.Interfaces.IOpportunity>();
Sage.Platform.Repository.ICriteria criteria = repository.CreateCriteria();
criteria.Add(repository.EF.Eq(“Type”, “Some Type”));
criteria.Add(repository.EF.Eq(“Status”, “Open”));
criteria.SetProjection(repository.PF.Sum(“SalesPotential”));
System.Collections.IList potential= criteria.List();
Double? pot = (Double?)null;
var x = criteria.UniqueResult();
if (x!=null) pot = Convert.ToDouble(x);
//pot now contains a nullable Double sum of sales potential
So in this example I have created a criteria condition looking for all Opportunities with a type=”Some Type” and a Status = “Open” from this I can then use the SetProjection method to sum the SalesPotential property. With that I return an IList. The IList is an arrayed list so my value will be the only returned value. On the final line I just check to ensure the list is not null and then return the first position array which is the sum of the potential for all of the opportunities meeting my conditions.
Something like this should get you the sum of the Opportunity Products on an opportunity where the Family=ABC. note I am attempting a Query of the Product.Family field which I don’t think will work due to the lazy loading of the entity model. If it does not work you would need to do something different like this post:
http://customerfx.com/pages/integrationblog/2010/05/26/unwrapping-a-lazy-loaded-entity-object-in-the-saleslogix-web-entity-model.aspx
Sage.Platform.RepositoryHelper repository =();
Sage.Platform.EntityFactory.GetRepositoryHelper
Sage.Platform.Repository.ICriteria criteria = repository.CreateCriteria();
criteria.Add(repository.EF.Eq(“Product.Family”, “ABC”));
criteria.Add(repository.EF.Eq(“Opportunity.Id”, “SOMEOPPORTUNITYID”));
criteria.SetProjection(repository.PF.Sum(“ExtendedPrice”));
System.Collections.IList potential= criteria.List();
if (potential!= null) used = Convert.ToDouble(potential[0]);
As to the best way of handling this, certainly a data reader or back-end SP would work, it is really up to you as to which way you want to implement it.