Records to Multiple totals

[nctechbear]

I have a situation where we have an invoiced data record. This data record has a single sales rep assigned to the account. However, the territory might have two sales reps assigned to it. The account can have an inside and outside sales rep.

We are trying to get a report which shows all customers for a territory. The territory may be made of up to three different sales reps.

We would like to have these totals then ranked (highest to lowest).

Any suggestions would be greatly appreciated.

 

[IdoMillet]

This is a bit too open ended. What is the desired output and what is the problem in achieving it?

If the problem is joins, double counting customers, etc., please describe the table structures and how you are trying to join them.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[nctechbear]

The table structure is OEFCHEADER. The table contains among other things, order number, date, invoice number, date, sales rep, order value net, order value total, order cost total.

This is a single table report.

Example: A record contains:

Order Number: 101
Order date: 3/1/04
Invoice Number: 120000
Invoice Date: 3/12/04
Sales Rep #: 1071
Order Value Net: 10000.00
Order Value Total: 10050.00
Order Cost Total: 8000.00

I have sales reps that have territories based upon the sales rep value. In this example, this record would be assigned to sales territory for Jeff, and Donald.

Report Requirements:

Show Sales Territory by name (i.e, Jeff, and Donald), report net sales, profit (Order Value Net - Order Cost Total), and Profit % (Order Value Net - Order Cost Total / Order Value Net) as totals (1 line per territory).

After that has been accomplished, sort the report in descending order by Gross Profit $.

Hope this answers the question.

 

[GJParker]

I have sales reps that have territories based upon the sales rep value. In this example, this record would be assigned to sales territory for Jeff, and Donald

is Jeff, and Donald the name of 1 sales territory or 2 territories, 1 called Jeff and 1 called Donald ?

Are the territory names included in your table ? If not they will need to hard-coded into your report as a formula.



Gary Parker
Systems Support Analyst
Manchester, England

 

[nctechbear]

Jeff is a territory that uses sales rep numbers 107 and 1071. Donald is a territory that uses sales rep number 1071.

Territory names are not part of the table definition.

 

[GJParker]

From what you are saying, 1 order number will be assigned to multiple sales territories i.e. in you r example Order No. 101 should appear in territory Jeff and territory Donald.

You will need to create a 2nd table, containing Sales Rep and Territory Name i.e.

107     Jeff
1071    Jeff
1071    Donald

Once this is created link to OEFCHEADER on Sales Rep, then you can group by Territory Name insert your details and summarise as required.

Then do a TopN sort to sort by gross profit.

HTH

Gary Parker
Systems Support Analyst
Manchester, England

 

[nctechbear]

I can't create a second table with these items in it. The accounting system that I am using ODBC won't allow me to create a new table.

Now what?

 

[GJParker]

The table doesn't have to bve in the accounting sytem database, you could use Access or even Excel and link this into your report.

Gary Parker
Systems Support Analyst
Manchester, England