Sales Report by Territory

[Dgbeej]

Has anyone done a report for monthly sales by 'Territory'

Territory is in the OCRD table but not in the OPCH table - has anyone any idea of how to filter the OPCH transactions by this field?

Thanks

[Dgbeej]

I did it manually using the Query Generator and somehow got it working - if anyone wants this please shout

[Former Member]

Can you please post what you did to generate this report? Thank you!

Russell Clayton

Sweet Leaf Tea Co

[Dgbeej]

Hi Russell,

Here you go see if this works - they are on 2007 A just in case it effects the query.

Kind regards

Dom

SELECT T0.[territryID], T0.[descript], T1.[CardCode], T2.[DocNum], T2.[DocDate], T2.[DocTotal]-T2.[VatSum] AS 'Net Total' FROM OTER T0 INNER JOIN OCRD T1 ON T0.territryID = T1.Territory INNER JOIN OINV T2 ON T1.CardCode = T2.CardCode WHERE T0.[territryID] >= '[%0]' AND T0.[territryID] <= '[%1]' AND T2.[DocDate] >= '[%2]' AND T2.[DocDate] <= '[%3]' ORDER BY T0.[territryID]

[Former Member]

Hi Dominic,

You can check this thread:

If that report format fit your need, territory report from that query will be fairly easy.

Thanks,

Gordon

[Former Member]

Dominic,

I'm not sure what you are asking here. Why would you use the A/P Invoice table in a Sales Report?

Do you mean you are doing a P&L by territory, so you need A/R and A/P documents?

Either way, you'd probably want to do something like this in XLR or in Crystal, whichever you are more comfortable with. A query would be tough if you wanted to see multiple months with totals in the report. XLR and Crystal both do that really well, though.

Clarify your needs a bit and I'll see if I can help out.

Brad Windecker

[Omega Business Solutions|http://www.omegagroup.com]