Hi,

I need a aggregate value for a field on a table. I'm adding the relationship with the code below.

filter.Relations.Add(CustomerEntity.Relations.InvoiceEntityUsingCustomerId, "Customer", groupRange.Name, JoinHint.Left)
                    .CustomFilter = joinFilter;

This relationship could be added several times. To put this in perspective, i am join invoices on to Customer 4 times. Once for invoices less than 30 days overdue, once for 30-60 days overdue etc.

I've defined this aggregate field for the Invoice table as shown here. This field is added for each of the joins, it's ObjectAlias and Alias are always different.

fields.DefineField(invoiceTotal, index++, AggregateFunction.Sum);

The problem i'm having is that the aggregate is then calculated on the final result set, but really what i want is the aggregate column to calculated the matchings rows of the join and then for that value to be outputted in the final result set.

If i were writing sql i wouldn't join the table and give it an alias i would join to the result of a subquery against the invoice table. I can't see a way to do this, or an alternative that will give me what i want.

The final result set i'm trying to get looks a bit like this: CustomerId, CurrentInvoiceTotal, 30DaysInvoiceTotal, 60DaysInvoiceTotal, 90DaysInvoiceTotal

I need this nice flat data for a report.

And heres the whole code:

public static DataTable OverdueInvoiceReport(int customerId, params ReportGroupRange[] groups)
        {
            IRelationPredicateBucket filter = new RelationPredicateBucket();
            IGroupByCollection groupBy = new GroupByCollection();
            ResultsetFields fields = new ResultsetFields(3 + (groups.Length/* * 2*/));
            int index = 0;

            fields.DefineField(CustomerFieldIndex.CustomerId, index++, "CustomerId", "Customer");
            fields.DefineField(CustomerFieldIndex.CustomerCode, index++, "CustomerCode", "Customer");
            fields.DefineField(CustomerFieldIndex.Email, index++, "Email", "Customer");

            foreach (ReportGroupRange groupRange in groups){
                EntityField2 dateDifference = InvoiceFields.InvoiceDate,
                    invoiceDate = InvoiceFields.InvoiceDate,
                    //invoiceCount = InvoiceFields.InvoiceId,
                    invoiceTotal = InvoiceFields.TotalAmount,
                    isPaid = InvoiceFields.IsPaid;
                IPredicateExpression joinFilter = new PredicateExpression();
                isPaid.ObjectAlias = invoiceTotal.ObjectAlias = /*invoiceCount.ObjectAlias = */invoiceDate.ObjectAlias = dateDifference.ObjectAlias = groupRange.Name;
                //invoiceCount.Alias = "InvoiceCount_" + groupRange.Name;
                invoiceTotal.Alias = "InvoiceTotal_" + groupRange.Name;
                dateDifference.Alias = "DateDifference";                
                dateDifference.ExpressionToApply = new DbFunctionCallEx("DATEDIFF", new object[] { SqlFunctionArgs.Day, invoiceDate, DateTime.Today });

                joinFilter.Add(isPaid == false);
                joinFilter.Add(dateDifference >= groupRange.Start);
                if (groupRange.End != null)
                    joinFilter.AddWithAnd(dateDifference <= groupRange.End);

                filter.Relations.Add(CustomerEntity.Relations.InvoiceEntityUsingCustomerId, "Customer", groupRange.Name, JoinHint.Left)
                    .CustomFilter = joinFilter;

                //fields.DefineField(invoiceCount, index++, AggregateFunction.Count);
                fields.DefineField(invoiceTotal, index++, AggregateFunction.Sum);
            }
        
            filter.PredicateExpression.Add((EntityField2)fields[0] == customerId);

            groupBy.Add(fields[0]);
            groupBy.Add(fields[1]);
            groupBy.Add(fields[2]);

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                DataTable dataTable = new DataTable();
                adapter.FetchTypedList(fields, dataTable, filter, 0, null, false, groupBy);

                return dataTable;
            }
        }

The DbFunctionCallEx is an extended DbFunctionCall to support DATEDIFF (and DATEADD). I created under the guidance of another post. If you need it to help me solve this riddle i can post it.

Thanks

I think you might need to use Scalar query expressions

Please check the LLBLGen Pro manual: "Use the generated code -> Field expressions and aggregates", scroll down to the Scalar query expressions