Re: Sales By Item Report with Dates and Item Filter

Method_David — Tue, 27 Nov 2012 15:01:14 GMT

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

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

Sales By Item Report with Dates and Item Filter

nick_warren — Sun, 25 Nov 2012 13:03:41 GMT

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?

Method:API Q&A

Re: Sales By Item Report with Dates and Item Filter

Sales By Item Report with Dates and Item Filter