Multiple Aggregations in X++ Join

At work we’ve recently discussed how to calculate a customers invoice amount for multiple years. One idea was to loop over the customer accounts (while select)  and individually calculate the sum for each year with individual statements (select sum). However, this can be done by a single statement.

CustInvoiceJour     custInvoiceJour;
CustInvoiceTrans    custInvoiceTrans1;
CustInvoiceTrans    custInvoiceTrans2;

while select InvoiceAccount,InvoiceId from custInvoiceJour
group by InvoiceAccount
outer join InvoiceId, sum(LineAmount) from custInvoiceTrans1
where custInvoiceJour.InvoiceId == custInvoiceTrans1.InvoiceId &&
custInvoiceTrans1.InvoiceDate >= str2DateDMY(‘1.1.2011’) &&
custInvoiceTrans1.InvoiceDate <= str2DateDMY(‘31.12.2011’)
outer join InvoiceId, sum(LineAmount) from custInvoiceTrans2
where custInvoiceJour.InvoiceId == custInvoiceTrans2.InvoiceId &&
custInvoiceTrans2.InvoiceDate >= str2DateDMY(‘1.1.2012’) &&
custInvoiceTrans2.InvoiceDate <= str2DateDMY(‘31.12.2012’)
{
info( strFmt(“Customer:%1 2011:%2 2012:%3”,
custInvoiceJour.InvoiceAccount,
custInvoiceTrans1.LineAmount,
custInvoiceTrans2.LineAmount));
}
//Customer:DE-001 2011:3.814.145,50 2012:3.842.223,00

This will result in the following output

Mutliple sum() in X++ Join

Check values for customer DE-001 with excel

Check Mutliple sum() in X++ Join

About erpcoder
Azure Cloud Architect and Dynamics 365 enthusiast working in Research & Development for InsideAx

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: