July 18, 2008, 04:27 PM
LeahAs indicated RECAP and placing information in a subfoot.
July 23, 2008, 11:18 AM
karenvI've looked at all of the places that each of you have suggested and tried all of the examples and I still don't see a good solution. If I missed the example would someone please point me to it ?
I rewrote the original program to try to make it more maintainable. Before it had defines for each across value hard coded creating each column. It did not do an across. Therefore whenever a new code was added the program had to be modified. I wanted to have the program not have to be touched whenever a new code was added.
The original sample code that I posted gives me all of the correct data I just don't want to show the PCT in the body of the report, I only want it to show in the summary lines. I have that working from the stand point that I made the PCT field color white ( on white background) in the body of the report, but that is really hokey and ultimately shows a blank line. Plus if the user selects to output this report to EXCEL then the data is really still there. What I really want is to NOPRINT a field in the detail and ON sort RECOMPUTE and SUMMARIZE field1 and field2 (field2 being the NOPRINTed field in the detail). It seems that other people need this functionality too. How about a new feature ?
July 23, 2008, 02:15 PM
mgrackinOkay. It took some time for me to figure this out but I think I have a viable solution for you. It is not pretty but it works. There should be an easier way to do this but for now this is what I came up with.
In the following code I basically created the TOTAL and PERCENT rows of data in HOLD files and then added them back into the raw data as rows of data. This makes it possible to treat them as normal rows of data instead of summary lines which seems to be the problem.
Run the code below and see how it works.
SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
DEFINE FILE CAR
NEWSALES/D12=SALES;
END
-* Extract the raw data
TABLE FILE CAR
SUM NEWSALES
BY COUNTRY
BY CAR
BY BODYTYPE
WHERE BODYTYPE NE 'CONVERTIBLE' OR 'HARDTOP'
ON TABLE HOLD AS 'RAWDATA'
END
-* Create summary records for the total sales
TABLE FILE RAWDATA
SUM NEWSALES
COMPUTE CAR/A16='Total';
COMPUTE SORTCNTL/I1=2;
BY COUNTRY
BY BODYTYPE
ON TABLE HOLD AS 'COUNTRYTOT'
END
-* Create summary records for the percent sales
TABLE FILE RAWDATA
SUM PCT.NEWSALES AS 'NEWSALES' WITHIN COUNTRY
COMPUTE CAR/A16='Percent';
COMPUTE SORTCNTL/I1=3;
BY COUNTRY
BY BODYTYPE
ON TABLE HOLD AS 'COUNTRYPCT'
END
DEFINE FILE RAWDATA
SORTCNTL/I1=1;
END
-* Combine all the data into one file
TABLE FILE RAWDATA
SUM NEWSALES
BY COUNTRY
BY SORTCNTL
BY CAR
BY BODYTYPE
ON TABLE HOLD AS 'FINALDATA'
MORE
FILE COUNTRYTOT
MORE
FILE COUNTRYPCT
END
-* Reformat the NEWSALES field dynamicly
DEFINE FILE FINALDATA
THEFRMT/A8=DECODE SORTCNTL(1 'D12' 2 'D12' 3 'F6.2%');
END
-* Generate the final output
TABLE FILE FINALDATA
SUM NEWSALES/THEFRMT
BY COUNTRY
BY SORTCNTL NOPRINT
BY CAR
ACROSS BODYTYPE ACROSS-TOTAL
END