I have to create a report and in the subtotal I must put also the number of rows that were counted in the sous-total.
The report must look like this:
COL1 COL2 COL3
A 1 20
A 2 10 ----------------------------
SubTotal A(2) 3 30

B 2 15
B 3 25
B 1 10 ---------------------------
SubTotal B(3) 6 50

I tried to use a count , something like that:

TABLE FILE TBL
COL2
COL3
COMPUTE VAR= CNT.COL1
BY COL1
ON TABLE SUBTOTAL COL1 AS 'SubTotal
But it didn't work. Is it possible to do this? Thanks for your responses,
Andreea

try a compute on the by field

BY COL1 COMPUTE ...

Or do a compute in teh report with noprint
COMPUTE NUMB=1; NOPRINT
And
ON COL1 SUBFOOT
"subtotal

	Frank
	prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7

Thanks for your response, but it didn't help me because I can't get the report as I want it:
If I use:
BY COL1 COMPUTE VAR=CNT.COL1
I get an error message, because I can't use CNT in BY.
And, If I try the second solution :
ON COL1 SUBFOOT
"subtotal (I get the first part of the result that I expected( Subtotal A(2) ) but I can't get the rest of the subtotal for the columns COL2 and COL3.
I forgot to say that my report must also contain a final total :
COL1 COL2 COL3
A 1 20
A 2 10
-------------------
SubTotal A(2) 3 30

B 2 15
B 3 25
B 1 10
-------------------
SubTotal B(3) 6 50
TOTAL (5) 9 80

THanks again and have a nice day,
Andreea

Andreea,

Try this

TABLE FILE CAR
SUM COMPUTE CTRY_CNT/I9 WITH COUNTRY = 1; NOPRINT
BY COUNTRY
SUM RCOST DCOST
BY COUNTRY
BY CAR
ON COUNTRY SUBFOOT
"Subtotal <COUNTRY (<ST.CTRY_CNT<+0>) <ST.RCOST <ST.DCOST"
ON TABLE SUBFOOT
"Total (<ST.CTRY_CNT<+0>) <ST.RCOST <ST.DCOST"
END

Edited because I forgot the Total row for you.

T

This message has been edited. Last edited by: Tony A,

Hello Tony,
Thanks for your support; It works fine right now, as I want it.
I have only one thing that's not OK: the result of the subtotal isn't right; I get always the CTRY_CNT = 1;
If I have 3 record for country=X, I need to have CTRY_CNT = 3.
The Total works fine; it makes the sum of the values which was found in the subtotal fields.

Thanks again,
Andreea

Andreea,

Just add another BY CAR to the first summation -

TABLE FILE CAR
SUM COMPUTE CTRY_CNT/I9 WITH CAR = 1; NOPRINT
BY COUNTRY
BY CAR
SUM RCOST DCOST
BY COUNTRY
BY CAR
ON COUNTRY SUBFOOT
"Subtotal <COUNTRY (<ST.CTRY_CNT<+0>) <ST.RCOST <ST.DCOST"
ON TABLE SUBFOOT
"Total (<ST.CTRY_CNT<+0>) <ST.RCOST <ST.DCOST"
END

T

Hello,
If I use the syntax:
TABLE FILE CAR
SUM COMPUTE CTRY_CNT/I9 WITH CAR = 1; NOPRINT
BY COUNTRY
BY CAR
SUM RCOST DCOST
BY COUNTRY
BY CAR
ON COUNTRY SUBFOOT
"Subtotal ... ) ON TABLE SUBFOOT
"Total .....() END
I get an report with subtotal after each rows, something like that:

COUNTRY CAR RCOST DCOST
----------------------------
Country1 Car1 2 3
Subtotal Country1(1) 2 3
Country1 Car2 1 2
Subtotal Country1(1) 1 2
Country2 Car2 5 3
Subtotal Country2(1) 5 3
Country2 Car3 2 4
Subtotal Country2(1) 2 4
Country2 Car4 1 3
Subtotal Country2(1) 1 3
Total(5) 11 15

In another case, if I use the syntax:
TABLE FILE CAR
SUM COMPUTE CTRY_CNT/I9 WITH COUNTRY = 1; NOPRINT
BY COUNTRY
SUM RCOST DCOST
BY COUNTRY
BY CAR
ON COUNTRY SUBFOOT
"Subtotal ... ) ON TABLE SUBFOOT
"Total .... () END
I get one result like that(The subtotal isn't right):
COUNTRY CAR RCOST DCOST
Country1 Car1 2 3
Country1 Car2 1 2
----------------------------
Subtotal Country1(1) 3 5

Country2 Car2 5 3
Country2 Car3 2 4
Country2 Car4 1 3
----------------------------
Subtotal Country1(1) 8 10
Total(2) 11 15

As you can see, no one of the solutions help me get the report as I want it;
I want my report to be something like that(in red is what I want to get from my report):
COUNTRY CAR RCOST DCOST
Country1 Car1 2 3
Country1 Car2 1 2
----------------------------
Subtotal Country1(2) 3 5
Country2 Car2 5 3
Country2 Car3 2 4
Country2 Car4 1 3
----------------------------
Subtotal Country2(3) 8 10
Total(5) 11 15

It's any other method that I can use to create that subtotal as I want?
Thanks a lot for your responses,

Andreea

This message has been edited. Last edited by: <Andr33a>,

quote:

SUM COMPUTE CTRY_CNT/I9 WITH COUNTRY = 1; NOPRINT

SUM COMPUTE CTRY_CNT/I9 WITH CAR = 1; NOPRINT

Your are complicating your code unnecessarily. You do not need to do a multi-verb request. Try the following.

TABLE FILE CAR
SUM RCOST DCOST
COMPUTE CTRY_CNT/I9 WITH CAR = 1; NOPRINT
BY COUNTRY
BY CAR
ON COUNTRY SUBFOOT
"Subtotal <COUNTRY [<ST.CTRY_CNT<+0>) <ST.RCOST <ST.DCOST"
ON TABLE SUBFOOT
"Total (<ST.CTRY_CNT<+0>) <ST.RCOST <ST.DCOST"
END

This message has been edited. Last edited by: mgrackin,

Too true Mickey - gives exactly the same output as the code I posted so I don't understand why Andreea was getting a different output?

T

Hello,
Tony, you are right; The code that you post it gives the same output as the code posted by Mickey, but it's not the output I want to have.
Using your codes I get the output exactly like that:

COUNTRY CAR RCOST DCOST
----------------------------
Country1 Car1 2 3
Subtotal Country1(1) 2 3
Country1 Car2 1 2
Subtotal Country1(1) 1 2
Country2 Car2 5 3
Subtotal Country2(1) 5 3
Country2 Car3 2 4
Subtotal Country2(1) 2 4
Country2 Car4 1 3
Subtotal Country2(1) 1 3
Total(5) 11 15

And I don't want to have a subtotal after every type of the car; I want to have a subtotal by the country and in the parantheses I must get the number of the cars that are in the country that I used as the tri field. It means I want something like this:

OUNTRY CAR RCOST DCOST
Country1 Car1 2 3
Country1 Car2 1 2
----------------------------
Subtotal Country1( 2) 3 5
Country2 Car2 5 3
Country2 Car3 2 4
Country2 Car4 1 3
----------------------------
Subtotal Country2(3) 8 10
Total(5) 11 15

Thanks a lot for yours support; I'll try find a way to make that report and if I find a solution I will post it to the forum.
If you have other options/sugestions I will be glad to received them.

Andreea


PS: I forgot to mention that I need to use PRINT for the rest of the columns, because I have more lignes that must be printed into the output; So I used SUM for the subtotal :
SUM
COMPUTE CTRY_CNT/I9 WITH CAR = 1; NOPRINT
BY COUNTRY
BY CAR
and PRINT for the rest of the columns:
PRINT
RCOST DCOST
BY COUNTRY
BY CAR

This message has been edited. Last edited by: <Andr33a>,

Andreea, The output I receive using the code I posted is this -

COUNTRY	CAR	RETAIL_COST	DEALER_COST
ENGLAND	JAGUAR	22,369	18,621
	JENSEN	17,850	14,940
	TRIUMPH	5,100	4,292
Subtotal ENGLAND (3) 45,319 37,853
FRANCE	PEUGEOT	5,610	4,631
Subtotal FRANCE (1) 5,610 4,631
ITALY	ALFA ROMEO	19,565	16,235
	MASERATI	31,500	25,000
Subtotal ITALY (2) 51,065 41,235
JAPAN	DATSUN	3,139	2,626
	TOYOTA	3,339	2,886
Subtotal JAPAN (2) 6,478 5,512
W GERMANY	AUDI	5,970	5,063
	BMW	58,762	49,500
Subtotal W GERMANY (2) 64,732 54,563
Total (10) 173,204 143,794

-

Which gives what you asked for. How are you getting different results? T

This message has been edited. Last edited by: Tony A,

Andrea,

The following code gives you SUBTOTALs just on Country which seems to be what you are looking for. This is basically the code I posted last week. May be we do not quite understand what you are trying to do.

TABLE FILE CAR
SUM RCOST DCOST
COMPUTE CTRY_CNT/I9 WITH CAR = 1; NOPRINT
BY COUNTRY
BY CAR
ON TABLE SET HTMLCSS ON
ON COUNTRY SUBFOOT
"---------------------------------------------------"
"Subtotal <COUNTRY (<ST.CTRY_CNT<+0>) <ST.RCOST <ST.DCOST"
ON TABLE SUBFOOT
"---------------------------------------------------"
"---------------------------------------------------"
"Total (<ST.CTRY_CNT<+0>) <ST.RCOST <ST.DCOST"
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, FONT='COURIER',$
ENDSTYLE
END

Andreea,

Try this code using PPRINT:

TABLE FILE CAR
PRINT RCOST DCOST
COMPUTE CTRY_CNT/I9=IF (COUNTRY EQ LAST COUNTRY) AND (CAR EQ LAST CAR) THEN 0 ELSE 1; NOPRINT
BY COUNTRY
BY CAR
ON TABLE SET HTMLCSS ON
ON COUNTRY SUBFOOT
"--------------------------------------------------"
"Subtotal <COUNTRY (<ST.CTRY_CNT<+0>) <ST.RCOST <ST.DCOST</2"
ON TABLE SUBFOOT
"--------------------------------------------------"
"--------------------------------------------------"
"Total (<ST.CTRY_CNT<+0>) <ST.RCOST <ST.DCOST"
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, FONT='COURIER',$
ENDSTYLE
END

Thanks Tony, Mickey,for your infos;
The mistake was made in my report, because in SUM I put a condition and in PRINT I put the conditions in another order, and It seems that this was the problem.
Thanks again and have a nice day,

Andreea

Andreea,

FYI, Multi-Verb (SUM, PRINT) requests such as yours should only have one set of selection statements and they should all be after the last verb, which in your case is PRINT.

I'm insterested to see a CAR FILE example of your request with seelction statements in different places and in a different order.

I'm also not clear as to why you need to do a Multi-Verb request. But if it's working, GREAT!