Report To View Item Sales By Geography

[Former Member]

Hello All -

I would like to see a report that shows how well our products sell by U.S. State. Also, rather than see a list of individual items, I would like to see the cumulative totals of those items that have the same first four digits in the item number. Our item numbers are 9 digits in length - so for all items that share the same first 4 digits, I would like to know what the cumulative sales are by state.

Help?!

Thanks,

Mike

[Former Member · Accepted solution]

Hi Mike ,

Lets get your requirement correctly otherwise creating ur report is difficult

1. When you say by US , where is this information ..are you talking about Business Partner whose ship or bill to address is US .

2.Do you count invoice as your sales

3.Does ur report need to include credit memo

4. What feld u are expected to see in query

Hope this things will clear up and then we will start writing query ...

What do u say

Bishal

[Former Member · Accepted solution]

1. I'm talking about the Bill To Address of closed invoices.

2. Yes

3. Yes

4. Example of what Query would pull up below:

Items AK AL CA etc.

0838 $50000 $24000

0933

0944

etc.

Vertical axis -list items

Horizontal axis - list 50 states

Note - items are 9 characters -- however, report would consolidate data for those items that share the same first 3 digits.

Hopefully this makes sense.

Thanks,

Mike

[Former Member · Accepted solution]

Hi Mike ,

Start with Something like this

Select SubString(Itemcode,0,5) as ItemCode,

Sum (case when CRD1.State = 'CA' then Linetotal else 0 End )as CA ,

Sum (case when CRD1.State = 'IL' then Linetotal else 0 End )as IL

from INV1 inner Join OINV on OINV.DocEntry= INV1.DocEntry

inner Join OCRD on OCRD.CardCode= OINV.CardCode

inner Join CRD1 on OCRD.CardCode = OCRD.CardCode

Where CRD1.AdresType='B' and OINV.DocType='I'

Group by SubString(Itemcode,0,5)

Start with one or two state to start and then extend upto 50 ...Basically you have to test whether result are coming as expected or Not

Hope this is the start ..

Thank you

Bishal