i have a requirement to show the counts(i.e.unit of Measure) for different weeks in a month and also the total count for that month and year selected. this would look like below:
count1 count2
wk1|wk2|wk3|wk4|Mnth|Yr wk1|wk2|wk3|wk4|Mnth|Yr

i am able to do get the first part that is for count1 but both could not be achieved. Could anyone help me out to resolve the issue.

Sandhya,

Try the following as an example. The key to getting what you are looking for is to use the ACROSS-TOTAL command.

DEFINE FILE PERSINFO
BIRTHQTR/Q=BIRTHDATE
END

TABLE FILE PERSINFO
COUNT PIN
ACROSS COUNTRY AS '' ACROSS-TOTAL
ACROSS BIRTHQTR AS '' ACROSS-TOTAL
END

Thanks Grackin for the reply. The example that you cited did help me to solve a part of my problem.

1)Now considering a variation to your example i want two counts instead of just a single count in the same report. i.e.

TABLE FILE PERSINFO
COUNT
PIN
AGE
ACROSS YEAR AS '' ACROSS-TOTAL
ACROSS WEEK AS '' ACROSS-TOTAL
WHERE YEAR EQ '2006';
WHERE MONTH EQ 'JAN';
END

The output of the above example would be like:

2006 || total
----------------------------------------
WK1 || WK2 || total
----------------------------------------
PIN|AGE || PIN|AGE|| PIN|AGE|| PIN|AGE


but i want the output as:

2006
---------------------------------------------------------
WK1|WK2|Month tot|Year tot || WK1|WK2|Month tot|Year tot
---------------------------------------------------------
PIN|PIN|PIN |PIN || AGE|AGE|AGE |AGE
is there anyway by which i can achive the above output.


2)Also when we get the year total we have to get the total for the entire year and not just for the selected month. As there is a filter on both year and month, the data that is coming in the report is just for the selected month. Is there any way by which we can just put the filter only on year and then show only selected month's weeks in the report i.e. by supressing other months.Thus the report would show the selected months weekwise data and the total Month's and also total Year's data.

Try the following as an example:

In order the rearrange the order of the fields (PIN and AGE) in the ACROSS, you need to twist the data so that the totals for these two are on physically separate records. Hence the need for the SAVE file and the MFD with the usage of OCCURS and ORDER. This probably could also be accomplished with the MacGyver Technique but I chose this way instead.

Give it a test and pick it apart to learn how it works. the only thing it doesn't have is TITLEs for the Year Totals.

DEFINE FILE PERSINFO
BIRTHDEC/YY='2006';
BIRTHQTR/Q=BIRTHDATE;
BIRTHMTH/M=BIRTHDATE;
CNTSPOUSE/I9=IF (RELATIONSHIP EQ 'SPOUSE') THEN 1 ELSE 0;
CNTSIBLING/I9=IF (RELATIONSHIP EQ 'BROTHER' OR 'SISTER') THEN 1 ELSE 0;
CNTPARENT/I9=IF (RELATIONSHIP EQ 'FATHER' OR 'MOTHER') THEN 1 ELSE 0;
END

TABLE FILE PERSINFO
SUM CNTSPOUSE CNTPARENT
BY BIRTHDEC
SUM CNTSPOUSE CNTPARENT
BY BIRTHDEC
BY BIRTHQTR
BY BIRTHMTH
WHERE BIRTHDEC EQ '2006'
ON TABLE SAVE AS 'THEDATA'
END
-RUN

APP FI THEMFD DISK THEDATA.MAS
-RUN

-WRITE THEMFD FILENAME=THEDATA, SUFFIX=FIX,$
-WRITE THEMFD SEGNAME=ONE, SEGTYPE=S0,$
-WRITE THEMFD FIELDNAME=BIRTHDEC , , FORMAT=YY, ACTUAL=A4,$
-WRITE THEMFD FIELDNAME=CNTSPOUSE, , FORMAT=I9, ACTUAL=A9,$
-WRITE THEMFD FIELDNAME=CNTPARENT, , FORMAT=I9, ACTUAL=A9,$
-WRITE THEMFD FIELDNAME=BIRTHQTR , , FORMAT=Q , ACTUAL=A1,$
-WRITE THEMFD FIELDNAME=BIRTHMTH , , FORMAT=A2, ACTUAL=A2,$
-WRITE THEMFD SEGNAME=MTHTOTS, SEGTYPE=S0, PARENT=ONE, OCCURS=2,$
-WRITE THEMFD FIELDNAME=CNTMONTH , , FORMAT=I9, ACTUAL=A9,$
-WRITE THEMFD FIELDNAME=CNTORDER , ALIAS=ORDER, FORMAT=I2, ACTUAL=I4,$
-WRITE THEMFD DEFINE CNTTYPE/A8=DECODE CNTORDER(1 'Spouses' 2 'Parents');$

TABLE FILE THEDATA
SUM MAX.CNTSPOUSE AS '' MAX.CNTPARENT AS ''
SUM CNTMONTH AS ''
ACROSS BIRTHDEC AS ''
ACROSS CNTTYPE AS ''
ACROSS BIRTHQTR NOPRINT COLUMNS 'Q1'
ACROSS BIRTHMTH AS '' ACROSS-TOTAL AS 'Total'
ON TABLE SET STYLE *
TYPE=REPORT, COLUMN=MAX.CNTSPOUSE, SEQUENCE=5,$
TYPE=REPORT, COLUMN=MAX.CNTPARENT, SEQUENCE=10,$
ENDSTYLE
END

Here is the result:

PAGE 1
2006
Parents					Spouses
01	02	03	Total		01	02	03	Total
0	2	0	2	15	0	1	0	1	8

Using the example I posted allows you to get the YEAR totals and month totals in the same SAVE file. Then, use the COLUMNS keyword to limit your columns to the month you want (in your case Jan). The example I created uses COLUMNS to limit the request to Q1 only but you can adapt the code to your needs.

hi Grackin,
Sorry for such a late reply. I could resolve this issue by just doing an accross and then holding it into a file and then rearranging the columns as per my requirement.Let me put this in the form of an example to be more clear.

SET ASNAMES=ON
TABLE FILE EMPLOYEE
SUM
AGE AS 'AG'
WORKEXP 'WE'
BY EMPLID
ACROSS TIME COLUMNS WK1 AND WK2 AND WK3 AND WK4 AND MTD AND YTD
WHERE TIME EQ 'WK1' OR 'WK2' OR 'WK3' OR 'WK4' OR 'MTD' OR 'YTD'
ON TABLE HOLD AS TEMP_XYZ FORMAT ALPHA
END

-SET &V_A_WK1='AG' || WK1;
-SET &V_A_WK2='AG' || WK2;
-SET &V_A_WK3='AG' || WK3;
-SET &V_A_WK4='AG' || WK4;
-SET &V_A_MTD='AG' || MTD;
-SET &V_A_YTD='AG' || YTD;


-SET &V_W_WK1='WE' || WK1;
-SET &V_W_WK2='WE' || WK2;
-SET &V_W_WK3='WE' || WK3;
-SET &V_W_WK4='WE' || WK4;
-SET &V_W_MTD='WE' || MTD;
-SET &V_W_YTD='WE' || YTD;

TABLE FILE TEMP_XYZ
PRINT
&V_A_WK1
&V_A_WK2
&V_A_WK3
&V_A_WK4
&V_A_MTD
&V_A_YTD
&V_W_WK1
&V_W_WK2
&V_W_WK3
&V_W_WK4
&V_W_MTD
&V_W_YTD
BY
EMPLD
END

then i have used the subhead to give the appropriate titles as per the requirement.

I have a similar situation I need help with. I want to have header going across the first row and a header/titles going down the first column. Then I need counts for each category in the header (first row). I have played with the ACROSS with not a lot of success. Can anyone help?

Thanks...

See this recent post.