Focal Point
Subtotal with detail
July 14, 2004, 05:13 PM
reFOCUSingSubtotal with detail
I will start off by showing you what I want to do:
SORT__COST__HOURS___AMOUNT
1_____3_____2_______6
1_____4_____5_______20
1_____5_____6_______30
Sub total 1_________56
2___________3_________
2___________7_________
Sub total 2_________67
Here is what raw data looks like:
SORT__COST__HOURS___AMOUNT
1_____3_____2_______6
1_____4_____5_______20
1_____5_____6_______30
2_____6_____3_______18
2_____7_____7_______49
So my problem is how can I produce a subtotal without showing the information I'm summing?
I put underscores in to show the spacing they are not actually in the data.
July 14, 2004, 05:36 PM
LeahHave you considered a subfoot on the sort key,
SET STYLEMODE = FIXED
TABLE FILE CAR
PRINT COUNTRY SEATS DEALER_COST RETAIL_COST
BY COUNTRY NOPRINT
ON COUNTRY SUBFOOT
"<+25>SUBTOTAL
END
Gives:
PAGE 1
COUNTRY SEATS DEALER_COST RETAIL_COST
------- ----- ----------- -----------
ENGLAND 2 7,427 8,878
ENGLAND 5 11,194 13,491
ENGLAND 4 14,940 17,850
ENGLAND 2 4,292 5,100
SUBTOTAL 45,319
FRANCE 5 4,631 5,610
SUBTOTAL 5,610
ITALY 4 4,915 5,925
ITALY 2 5,660 6,820
ITALY 2 5,660 6,820
ITALY 2 25,000 31,500
SUBTOTAL 51,065
JAPAN 4 2,626 3,139
JAPAN 4 2,886 3,339
SUBTOTAL 6,478
W GERMANY 5 5,063 5,970
W GERMANY 5 5,800 5,940
W GERMANY 4 6,000 6,355
W GERMANY 5 10,000 13,752
W GERMANY 5 11,000 14,123
W GERMANY 5 8,300 9,097
W GERMANY 5 8,400 9,495
SUBTOTAL 64,732
July 14, 2004, 05:58 PM
reFOCUSingThis does not work for me since I don't want to show the individual numbers that I am creating a subtotal for.
For SORT 1 I want to show all the values in the data but for SORT 2 I only want show the column HOURS with a subtotal on AMOUNT.
July 14, 2004, 07:20 PM
LeahSo you're looking for something like this if I interpret your last response correctly:
PAGE 1
SUBTOTAL FOR ENGLAND 45,319
SUBTOTAL FOR FRANCE 5,610
SUBTOTAL FOR ITALY 51,065
SUBTOTAL FOR JAPAN 6,478
SUBTOTAL FOR W GERMANY 64,732
SET STYLEMODE = FIXED
TABLE FILE CAR
SUM
COMPUTE LITA/A15 = 'SUBTOTAL FOR '; AS ''
COUNTRY AS ''
RETAIL_COST AS ''
BY COUNTRY NOPRINT
END
Of course if sort field is not alpha, you my have to do something to keep the value of it.
July 14, 2004, 07:40 PM
reFOCUSingkind of. I will make the example relate to the car table.
This is want I'm trying to do.
I want to see a subtotal for retail_cost under each grouping of country. I want to see detail information from the columns seats, dealer_cost and retail_cost for each country except italy. For italy I want to see only the column seats and I still want to have a subtotal on retail_cost.
Based on the data you posted the first time this is the output I'm trying to get.
COUNTRY SEATS DEALER_COST RETAIL_COST
------- ----- ----------- -----------
ENGLAND 2 7,427 8,878
ENGLAND 5 11,194 13,491
ENGLAND 4 14,940 17,850
ENGLAND 2 4,292 5,100
SUBTOTAL 45,319
FRANCE 5 4,631 5,610
SUBTOTAL 5,610
ITALY 4
ITALY 2
ITALY 2
ITALY 2
SUBTOTAL 51,065
JAPAN 4 2,626 3,139
JAPAN 4 2,886 3,339
SUBTOTAL 6,478
W GERMANY 5 5,063 5,970
W GERMANY 5 5,800 5,940
W GERMANY 4 6,000 6,355
W GERMANY 5 10,000 13,752
W GERMANY 5 11,000 14,123
W GERMANY 5 8,300 9,097
W GERMANY 5 8,400 9,495
SUBTOTAL 64,732
July 14, 2004, 08:16 PM
<WFUser>The only problem seems to be how to print field for all sorts except for one. You will probably end up with 2 reports. You can put them under each other with HTML or a compound PDF doc. For the sorts where you are printing everything, that is no problem. For the other (Italy) why can't you just do this.
TABLE FILE CAR
SUM SEATS
BY COUNTRY
ON COUNTRY SUBFOOT
"TOTAL RCOST < RCOST>"
BY MODEL NOPRINT
END
July 14, 2004, 08:20 PM
LeahOkay, how's this:
SET STYLEMODE = FIXED
TABLE FILE CAR
PRINT
COUNTRY AS ''
SEATS NOPRINT
DEALER_COST NOPRINT
RETAIL_COST NOPRINT
COMPUTE SEAT/D7S = C2; AS ''
COMPUTE DEALERC/D7S = IF COUNTRY EQ 'ITALY' THEN 0 ELSE C3; AS ''
COMPUTE RETAILC/D7S = IF COUNTRY EQ 'ITALY' THEN 0 ELSE C4; AS ''
BY COUNTRY NOPRINT
ON COUNTRY SUBFOOT
" SUBTOTAL FOR
END
PAGE 1
ENGLAND 2 7,427 8,878
ENGLAND 5 11,194 13,491
ENGLAND 4 14,940 17,850
ENGLAND 2 4,292 5,100
SUBTOTAL FOR ENGLAND 45,319
FRANCE 5 4,631 5,610
SUBTOTAL FOR FRANCE 5,610
ITALY 4
ITALY 2
ITALY 2
ITALY 2
SUBTOTAL FOR ITALY 51,065
JAPAN 4 2,626 3,139
JAPAN 4 2,886 3,339
SUBTOTAL FOR JAPAN 6,478
W GERMANY 5 5,063 5,970
W GERMANY 5 5,800 5,940
W GERMANY 4 6,000 6,355
W GERMANY 5 10,000 13,752
W GERMANY 5 11,000 14,123
W GERMANY 5 8,300 9,097
W GERMANY 5 8,400 9,495
SUBTOTAL FOR W GERMANY 64,732
July 14, 2004, 08:51 PM
reFOCUSingIf anyone else reads this Leah's code should read as follows:
TABLE FILE CAR
PRINT
COUNTRY AS ''
SEATS NOPRINT
DEALER_COST NOPRINT
RETAIL_COST NOPRINT
COMPUTE SEAT/D7S = C2; AS ''
COMPUTE DEALERC/D7S = IF COUNTRY EQ 'ITALY' THEN 0 ELSE C3; AS ''
COMPUTE RETAILC/D7S = IF COUNTRY EQ 'ITALY' THEN 0 ELSE C4; AS ''
BY COUNTRY NOPRINT
ON COUNTRY SUBFOOT
"SUBTOTAL FOR <ST.RETAIL_COST"
END
The < messed up the last bit of it.