Method Community

Any idea on which Query to use for a Sales By Item Report?

I thought about putting all the Sales Order using a date range on a datatable and then checking S.O by S.O for the specific item requsted - seems tedious to me. 

Any Ideas?

Hi nick_warren,

This is straightforward enough. You need to use a group by clause. My examples are in C#and let's use MethodAPISelect_XML

Step 1. Get the salesorder recordids for the time frame of interest. Let's use Nov 21-27 2012.

Using MethodAPISelect_XML to write the results to a label....really how you parse the xml is up to you

lblMethodAPISelect_XML.Text = _MethodAPI.MethodAPISelect_XML(CompanyAccount, Login, Password, ref XMLToReturn, "SalesOrder", "RecordID", "TxnDate >'2012/Nov/21' and TxnDate < '2012/Nov/28'", "", "", "");

In my case there is only one record in that time frame

<MethodIntegration Table='SalesOrder'> <Record> <RecordID>7</RecordID> </Record> </MethodIntegration>

Step 2.

After you've gotten your SalesOrder recordids, use the group by clause to query SalesOrderLine...I've chosen to group by the item description

Once again writing the results to a label

 lblMethodAPISelect_XML.Text = _MethodAPI.MethodAPISelect_XML(CompanyAccount, Login, Password, ref XMLToReturn, "SalesOrderLine", "Sum(amount), Desc", "SalesOrderRecordID in(7)", "Desc", "", "");

In my case this returns the folllowing xml

<MethodIntegration Table='SalesOrderLine'> <Record> <Column1>200.0000</Column1> <Desc>Product1</Desc> </Record> <Record> <Column1>100.0000</Column1> <Desc>Product</Desc> </Record> </MethodIntegration>

And there you have it.

Dave