Stuck! - Summing on Weird Linkages 1

[CrystalUserGuy]

Here's the scenario:

I have the following tables:

SALES
PROJECTS
QUOTAS

SALES is connected to PROJECTS with a projectID field common to both tables.
PROJECTS is connected to QUOTAS with a salesID field common to both tables.

SALES has some projectID fields which are null.
PROJECTS has some salesID which are null.
In order to have complete sales data show up for the report, I have linked via left outer joins: SALES to PROJECTS

to QUOTAS.

Now, my goal is to compare sales data (SALES) to sales quotas (QUOTAS) while going through PROJECTS to link SALES

and QUOTAS.

My problem is that when I display subtotals of sales (SALES) by salesrep (in QUOTAS), the totals come out ok. When

I pull quotas (QUOTAS) by sales rep, the info comes out ok. But, QUOTAS is a table with a hierarchical quota

system, in that there exist the fields:

VP Manager Sales Rep Quota

If I try to summarize the data further, by manager for instance, I get the correct actual sales total from SALES but

I cannot summarize the QUOTAS without getting a massive quota which doesn't add up to the actual instances within

the Quota table (ie. if Manager has 3 sales reps with quotas totalling $100k, instead I get $100,000,000).

Do you have any suggestions? I've tried to play around with subreports and variables but I'm not familiar enough

with these concepts in Crystal yet.

 

[Ngolem]

"SALES is connected to PROJECTS with a projectID field common to both tables.
PROJECTS is connected to QUOTAS with a salesID field common to both tables.

SALES has some projectID fields which are null.
PROJECTS has some salesID which are null."

Are there 2 links between the SALES and PROJECTS tables??

You show only one link ProjectID should there not be a link by SalesID?? as well

Having said that it sounds to me that you should be using a subreport for this report.

the main report would be of the Quota and Projects tables with Sales as the subreport...it is not clear to me as to the linkage though...what is SalesID in the Project table used for?

Jim Broadbent

 

[rogar]

There's a piece of data missing here. Presumably, a Manager has one or more SalesReps under him and his quota should be the sum of their quotas? You need to include this relationship in your data model so as not to add in quotas which are sums of other quotas.

 

[kenhamady]

Sounds like you have a one to many relationship, and when you do, you can't total from the 'one' end of that relationship without getting duplicates. It is these duplicates that inflate your totals. You might try using a running total that is set to evaluate on "change or group" or "change or field". this can eliminate duplicates.

You might also find linked subreports helpful instead of the awkward joins. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[CrystalUserGuy]

Thanks for everyone's help with this, particularly Ken's suggestion of going towards a subreports model -- that, and using variables did the trick!

Thanks again folks.