Show data for all combinations even if zero

[sagobogger]

Not quite sure how to title this one but here's a simple example.

Suppose I have a car dealership and my total month sales are:

4dr Sedans   100
2dr Sedans    80
Convertibles  20
PickUps       30
Vans          25

I want a report by each salesperson that shows their individual sales and the % of the dealership total sales by vehicle type.

Not all salepersons will have sold all types of vehicle, and I can't figure out how to get the dealership numbers on the report for the vehicle types they didn't sell.

So Joe's page of the report should look like this:

Type         Dealer     Joe Sales     % of Dealer Total
4dr Sedans   100         10             10%
2dr Sedans    80          8             10%
Convertibles  20          5             25%
PickUps       30          0              0%
Vans          25          0              0%

 

[focwizard]

Have you tried a multi-verb request, where the first verb set is used to get the dealership numbers, and the second gets the salesmans figures? It'll look something like this:

SUM CNT.SALES AS 'TOTCNT' BY TYPE
SUM CNT.SALES AS  SMCNT' COMPUTE SALESPCT=100 * C2/C1;
BY TYPE BY SALESMAN
ON TABLE SET ASNAMES ON
ON TABLE HOLD
...
TABLE FILE HOLD
PRINT TOTCNT SNCNT SALESPCT
BY SALESMAN PAGE-BREAK
BY TYPE
END

 

[sagobogger]

Thanks, I learned something I can use but sadly it doesn't solve the original problem of getting the total sales for all vehicle types listed for all salespersons, even if they didn't have personal sales,

TABLE FILE MYCARS
SUM SOLD AS 'TOTSOLD' BY VEHTYPE
SUM SOLD AS 'EMPSOLD' COMPUTE SALESPCT=100 * C2/C1;
BY VEHTYPE BY EMPLOYEE
ON TABLE SET ASNAMES ON
ON TABLE HOLD
END

TABLE FILE HOLD
PRINT TOTSOLD EMPSOLD SALESPCT BY EMPLOYEE BY VEHTYPE
END

JOE   CONV     3.00  3.00      100.00
JOE   SEDAN   13.00  5.00       38.46
JOE   TRUCK   14.00 10.00       71.43

SAM   LIMO     2.00  2.00      100.00
SAM   SEDAN   13.00  8.00       61.54
SAM   TRUCK   14.00  4.00       28.57

 

[PeterG5]

Have you tried
SET ALL = ON

 

[sagobogger]

I have now, but it doesn't change the result.

 

[nselph]

Of course if you did an across on the salesperson, you would get it....

 

[sagobogger]

Well, yeah, but a) the assignment is an individual report for each salesperson and b) this is a very simplifed example, in the real world I have to do this for 600 "salespersons" and several columns of data.

The only (very clunky) solution I have partially working so far is to first populate a table with bogus data (like 0.0001) for each combination, then update that table with the real values that exist, and then finally figure out how to print it so that the 0.0001's show as zeros.

 

[craigiep]

MATCH should do it for you, something like this

MATCH FILE SALES
SUM SOLD AS ALL_SOLD
BY DEALER
BY VEHTYPE
RUN
FILE SALES
SUM SOLD AS EMP_SALES
BY DEALER
BY VEHTYPE
BY EMPLOYEE
AFTER MATCH HOLD AS ALLSALES OLD
END

This file can be used to calculate percentages etc. You may have to amend this or add more layers depending on the final requirements, but it will hopefully be the basis for the job.

 

[sagobogger]

Sorry, same result :-(

MATCH FILE MYCARS
SUM SOLD AS ALL_SOLD
BY DEALER
BY VEHTYPE
RUN
FILE MYCARS
SUM SOLD AS EMP_SALES
BY DEALER
BY VEHTYPE
BY EMPLOYEE
AFTER MATCH HOLD AS ALLSALES OLD
END

TABLE FILE ALLSALES
PRINT ALL_SOLD EMP_SALES
BY DEALER
BY EMPLOYEE
BY VEHTYPE
END

DEALER SP     VEHTYPE ALL_SOLD EMP_SALES
WHEELS JOE    CONV        3.00      3.00
WHEELS JOE    SEDAN      13.00      5.00
WHEELS JOE    TRUCK      14.00     10.00
WHEELS SAM    LIMO        2.00      2.00
WHEELS SAM    SEDAN      13.00      8.00
WHEELS SAM    TRUCK      14.00      4.00