Hi can any one give suggestion for my problem.

Problem:

I have a hold file with across field and by field.I have a drill down for both title and data.When i drill down in title iam getting all my Across field values.When i drilldown in data field iam not getting all my across field vales.by field which doesnt have data for that particular Across column is not displaying.I want to display all Across field values.


here is my sample code.
TABLE FILE
COUNT
RDC
BY DEPT
ON TABLE HOLD AS HOLD1
END
TABLE FILE HOLD1
SUM
TOTALCNT
BY RDC
ACROSS DATE
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE = TITLE,
FOCEXEC =APP/FILE2
$
TYPE = DATA ,
FOCEXEC = APP/FILE2(parameter1)
$
END

Need help.
Thanks in advance.

So you want all by field values to be displayed in all cases.
In other words, you want focus to 'invent' records.
A way to accomplish this is to use the BY columnname ROWS value OVER value OVER value ... syntax, thus creating rows for each named value.
Another way of doing this is to first create a hold file with all your by-field values, followed by a match (hold old) with all across field values.
The result will also show all by field values, and is more dynamic than the BY ROWS method.

Souji,

  
TABLE FILE 
COUNT
RDC
BY DEPT
ON TABLE HOLD AS HOLD1
END
TABLE FILE HOLD1
SUM
TOTALCNT
BY RDC
ACROSS DATE 
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE = TITLE,
FOCEXEC =APP/FILE2
$
TYPE = DATA ,
FOCEXEC = APP/FILE2(parameter1)
$
END

Notice that the fields in your HOLD1 file are RDC and DEPT, whereas in the following TABLE FILE HOLD1 you are referencing TOTALCNT and DATE also which cannot exist in HOLD1. COuld you please be more specific?

-************file1.fex***************
DEFINE FILE TABLE1
CURDATE/YYMD = '&DATEYYMD';
MINDATE/YYMD = CURDATE + 1;
MAXDATE/YYMD = CURDATE + 14;
END
TABLE FILE TABLE1
PRINT
MINDATE
MAXDATE
WHERE ACCT_D EQ '&DATEYYMD'
ON TABLE SAVE AS SAVEDATS
END

DEFINE FILE TABLE2
CU_SIZE/P9.2 = (TOT_DSTB_Q * UNIT_CU_SIZE_Q);
CARTONS/P9 = (TOT_DSTB_Q / SSP_Q);
END
TABLE FILE TABLE2
SUM
CU_SIZE
CARTONS
BY SEND_LOC_I AS RDC
BY DEPT_I
BY PUSH_D
WHERE PUSH_D GE '&PUSHMIN'
WHERE PUSH_D LE '&PUSHMAX'
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS HOLD2
END
TABLE FILE HOLD2
COUNT DEPT_I
BY RDC
BY PUSH_D
ON TABLE HOLD AS HOLD2X
END
-RUN
TABLE FILE HOLD2X
COUNT RDC AS 'DCCNT'
BY PUSH_D
ON TABLE HOLD AS HOLDCCNT FORMAT FOCUS INDEX PUSH_D
END
-RUN
JOIN CLEAR *
JOIN PUSH_D IN HOLD2 TO PUSH_D IN HOLDCCNT AS JD
END

TABLE FILE HOLD2
SUM
CARTONS AS 'TOTAL,SHIP'
BY RDC
ACROSS PUSH_D
HEADING
"REPORT1"
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE = DATA ,
FOCEXEC = APP/FILE2(parameter1)
$
END


-*********file2.fex************
TABLE FILE HOLD2
SUM
CARTONS AS 'TOTAL,SHIP'
BY RDC
BY DEPT
ACROSS PUSH_D
HEADING
"REPORT2"
END


When i drill down from file1 to file 2 its not displaying all my across values.In RDC there are differen departments.when i drill down to particular drill down across push_d the data under that particular push_d was coming not all the values.

Need Help
Thanks,
Souji.