I have a report I need to produce that lists last years data, this years data, and a calculation between the two columns. I need those 3 columns to be grouped below the header of the field where the sum values come from. Does anyone any any ideas on how to do that?

Here is a sample I did from the cars data:

TABLE FILE CAR
SUM
DEALER_COST AS 'DEALER'
RETAIL_COST AS 'RETAIL'
BY COUNTRY
BY CAR
ACROSS SEATS
WHERE COUNTRY EQ 'ENGLAND';
END

The ouput from this is:
SEATS
2 4 5
COUNTRY CAR DEALER RETAIL DEALER RETAIL DEALER RETAIL
ENGLAND JAGUAR 7,427 8,878 . . 11,194 13,491
JENSEN . . 14,940 17,850 . .
TRIUMPH 4,292 5,100 . .


I need to output to be like this:
DEALER_COST RETAIL_COST
SEATS SEATS
COUNTRY CAR 2 4 5 2 4 5
ENGLAND JAGUAR 7,427 . 11,194 8,878 . 13,491
JENSEN . 14,940 . . 17,850 .
TRIUMPH 4,292 . . 5,100 . .

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

If you put your output between

[code]

[/code]

tags, you could use spaces to line up the report as you'd like it to be, then we may be able to help you out, right now it looks like a jumble of text and numbers.

COUNTRY  SALES
-------  -----
ENGLAND   2301

I have a report I need to produce that lists last years data, this years data, and a calculation between the two columns. I need those 3 columns to be grouped below the header of the field where the sum values come from. Does anyone any any ideas on how to do that?

Here is a sample I did from the cars data:

TABLE FILE CAR
SUM
DEALER_COST AS 'DEALER'
RETAIL_COST AS 'RETAIL'
BY COUNTRY
BY CAR
ACROSS SEATS
WHERE COUNTRY EQ 'ENGLAND';
END

The ouput from this is:

		                      SEATS					
		         2		4		5	
COUNTRY	CAR	DEALER	RETAIL	DEALER	RETAIL	DEALER	RETAIL
ENGLAND	JAGUAR	7,427	8,878	.	.	11,194	13,491
	JENSEN	.	.	14,940	17,850	.	.
	TRIUMPH	4,292	5,100	.	.	.	.

I need to output to be like this:

		       DEALER		|      RETAIL		
		        SEATS		|       SEATS		
COUNTRY	CAR	  2	  4	  5	|  2	  4	  5
ENGLAND	JAGUAR	7,427	.	11,194	|8,878	.	13,491
	JENSEN	.	14,940	.	|.	17,850	.
	TRIUMPH	4,292	.	.	|5,100	.	.

Thanks for the help!

quote:

I need those 3 columns to be grouped below the header of the field where the sum values come from

I can't figure out what you mean by this. It looks like you want seats 2 4 and 5 repeated twice - another ACROSS before the ACROSS SEATS might do it for you.

One method involves creating intermediary HOLD files and then concatenating them with the MORE FILE command. Here's an example:

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA
SET PAGE=NOLEAD
-RUN

TABLE FILE CAR
SUM
DEALER_COST AS 'COST'
COMPUTE COST_TYPE/A10 = 'DEALER';
BY COUNTRY
BY CAR
BY SEATS
WHERE COUNTRY EQ 'ENGLAND';
ON TABLE HOLD AS HDEALER
END
-RUN

TABLE FILE CAR
SUM
RETAIL_COST AS 'COST'
COMPUTE COST_TYPE/A10 = 'RETAIL';
BY COUNTRY
BY CAR
BY SEATS
WHERE COUNTRY EQ 'ENGLAND';
ON TABLE HOLD AS HRETAIL
END
-RUN

TABLE FILE HDEALER
SUM
COST
BY COUNTRY
BY CAR
ACROSS COST_TYPE AS ''
ACROSS SEATS

ON TABLE SET STYLE *
TYPE=REPORT, SQUEEZE=ON, FONT='ARIAL', SIZE=9, $

ENDSTYLE

MORE
FILE HRETAIL
END

Once you get this to work, you can tackle the headings!

For each column, dealer and retail, I need the seats grouped below them instead of the dealer and retail grouped below the seats.

For my actual program I have 15 fields. Each of those fields needs to have values below them representing last year and this year. When I do a normal across I get the two big columns. The first is last year and then shows all of my sum fields below it, the second is this year with all of my sum fields below it.

instead of this:
|---------------|---------------|
|     2010      |      2011     |
| a | b | c | d | a | b | c | d |
|---------------|---------------|
 
I need this:
|---------------------------------------|
|    a    |    b    |    c    |    d    |
|2010|2011|2010|2011|2010|2011|2010|2011|
|---------------------------------------|

CVB, the example I just posted does that.

quote:

One method involves creating intermediary HOLD files and then concatenating them with the MORE FILE command. Here's an example:

Thank you! This is perfect.

Carol

CVB,

You can also use the McGyver method. It produces the exact same output as Francis but with only one pass. Here's the code:

-* Set up McGyver master and data file.
-*
-* Build McGyver MFD
FILEDEF MCMAS DISK MCGYV.MAS
-RUN
-WRITE MCMAS FILENAME=mcgyv, SUFFIX=FIX     , $
-WRITE MCMAS   SEGMENT=ONE, SEGTYPE=S0, $
-WRITE MCMAS     FIELDNAME=CONTROL, USAGE=A1, ACTUAL=A1, $
-WRITE MCMAS   SEGMENT=TWO, SEGTYPE=S0, PARENT=ONE, OCCURS=VARIABLE, $
-WRITE MCMAS     FIELDNAME=CHAR1, USAGE=A1, ACTUAL=A1, $
-WRITE MCMAS     FIELDNAME=CTR, ALIAS=ORDER, USAGE=I4, ACTUAL=I4, $
-*
-* Build McGyver FTM
FILEDEF MCGYV DISK MCGYV.FTM
-RUN
-WRITE MCGYV XAB

-**************************************************************************************
-RUN
JOIN CONTROL WITH BODYTYPE IN CAR TO UNIQUE CONTROL IN MCGYV AS J2
DEFINE FILE CAR
-* Control=X: used as Join cross reference target field
CONTROL      /A1 WITH BODYTYPE='X';
COSTSORT/A6=DECODE CTR(1 DEALER ELSE RETAIL);
COST/D12.2=IF CTR EQ 1 THEN DEALER_COST ELSE RETAIL_COST;
END
TABLE FILE CAR
SUM COST AS ''
ACROSS COSTSORT AS ''
ACROSS SEATS 
BY COUNTRY
BY CAR
WHERE COUNTRY EQ 'ENGLAND'
ON TABLE SET PAGE NOLEAD
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=REPORT, SQUEEZE=ON, FONT='ARIAL', SIZE=9, $
ENDSTYLE
END

Hi Francis, a tip of the hat to you. By the way, at Summit in Dallas, you were mentioned by Art Greenhaus during the FocWizards session so your fame is spreading!

David

"mentioned by Art Greenhaus during the FocWizards session" - positive mention I hope! Like me, he is one of the old guard - probably not too interested in the new-fangled GUI stuff...