As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.
Join the TIBCO Community TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.
From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
Request access to the private WebFOCUS User Group (login required) to network with fellow members.
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.
I've got a rather cumbersome program with a lot of code and what I am trying to do is in the report section, print information for employes, followed by detail information for the employee. The header pice seems to be working, but the detail portion, although data is shown, contains a lot of duplicated information. I'm looking to have the detail portion print only once for duplicated data, like when you use the BY command.
I'm enclosing my code, if anyone wishes to take a look at it; like I said, the code is cumbersome and lengthy. Thank you in advance!
-*=======================================================================
-* ORIG: 05/12/05 BM
-* REV: 03/11/92 KS
-* ID: HFA031
-* TITLE: COMPENSATION ANALYSIS REPORT DATA
-* DESC: PROVIDES FILE OF ALL FTE ADMINISTRATORS, FACULTY, STAFF
-* WITH OBJECT CODE, SSN, NAME, TITLE, POSITION #, CLASS CODE,
-* SALARY FTE, SERVICE DATE, GENDER, RACE
-* FILES: EAFILE;EBFILE;EPFILE;LOC TBL;EDCHEK
-* OUTPUT: EXCEL FILE
-* PRINTER: MVSSYS.LOCAL (DEFAULT) OR A DISK
-* REQ BY: ALICIA REYES, DISTRICT HUMAN RESOURCES
-* REQ#: 13029715
-*=======================================================================
SET MSG = ON
-DEFAULT &PRG = BANNER01
JOIN CLEAR *
SET ASNAMES = ON
-*
-INCLUDE FOCDEF
-*
DEFINE FILE EDCHEK
CHECKKEY /A12 = ' ' | EMP_ID;
TOTNETPAY /P09.2C = IF TOT_NET EQ 0 THEN TOT_DIR_DEP
ELSE TOT_NET;
END
-*
TABLE FILE EDCHEK
PRINT
CHECKKEY
EMP_ID
CHECK_DATE
CHECK_VOID
TOT_GROSS
TOTNETPAY
BY CHECKKEY
WHERE CHECK_VOID EQ 'N' OR ' '
-*WHERE EMP_ID EQ '449887197'
WHERE CHECK_DATE IS-FROM &QTRDT1 TO &QTRDT2
ON TABLE HOLD AS PAYCHECK FORMAT FOCUS INDEX CHECKKEY
END
-RUN
-*
JOIN POSKEY WITH POS_NUM_210 IN EBFILE TO ROOT_KEY_EP IN EPFILE
-*
DEFINE FILE EBFILE
-INCLUDE FIXDEPT
NEWKEY /A12 = ' '|EMP_ID_210;
POSKEY /A11 = '001'|| '&FISCALYR' | POS_NUM_210;
SELREC /A01 = IF (EPACN EQ 'Y') THEN 'Y' ELSE 'N';
ISGRANT /A01 = EDIT(POS_NUM_210,'$9$$$$');
END
-*
TABLE FILE EBFILE
-*
PRINT
-*
EMP_ID_210
NEWKEY
POSKEY
EPACN AS GRANT
POS_NUM_210
ASGN_UNIT
ASGN_BGN_DT
ASGN_END_DT
ASGN_DPT
JOB_CLS_EB
ANN_SLRY
ANN_SLRY_FLG
FTE_EB
ASGN_UNIT
JOB_CD_210
PAYRATE_EB
PAY_PER_EB
ISGRANT
SELREC
-*
BY EMP_ID_210
-*BY LOWEST JOB_CD_210
WHERE EMP_ID_210 EQ '486449244'
WHERE ASGN_BGN_DT IS-FROM &QTRDT1 TO &QTRDT2
IF ISGRANT EQ '9'
-*
ON TABLE HOLD AS KAY1A1 FORMAT FOCUS INDEX EMP_ID_210
-*
END
-*
TABLE FILE KAY1A1
-*
PRINT
-*
EMP_ID_210
POS_NUM_210
ANN_SLRY
JOB_CD_210
PAYRATE_EB
PAY_PER_EB
-*
END
-RUN
-*
TABLE FILE EBFILE
PRINT
EMP_ID_300
EBEOM AS EBEOM
EBEPU AS EBEPU
JOB_CD_300
EARN_300_CD
EARN_TP_300
KEY_300B
BY EMP_ID_300
-*BY LOWEST JOB_CD_300
WHERE EMP_ID_300 EQ '486449244'
-*WHERE EMP_ID_300 EQ '449887197'
ON TABLE HOLD AS KAY1A2 FORMAT FOCUS INDEX EMP_ID_300
END
-RUN
-*
TABLE FILE KAY1A2
PRINT *
END
-RUN
-*
JOIN EMP_ID_300 IN KAY1A2 TO ALL EMP_ID_210 IN KAY1A1 AS JOINKAY2
TABLE FILE KAY1A2
PRINT
-*
NEWKEY
POSKEY
GRANT
EMP_ID_210
POS_NUM_210
ASGN_UNIT
ASGN_BGN_DT
ASGN_END_DT
ASGN_DPT
JOB_CLS_EB
ANN_SLRY
ANN_SLRY_FLG
FTE_EB
ASGN_UNIT
JOB_CD_210
PAYRATE_EB
PAY_PER_EB
EBEOM
EBEPU
JOB_CD_300
EARN_300_CD
EARN_TP_300
KEY_300B
ISGRANT
-*
BY NEWKEY
BY JOB_CD_210
WHERE EMP_ID_210 EQ '486449244'
ON TABLE HOLD AS KAY1A
END
-RUN
-*
JOIN NEWKEY IN KAY1A TO KEY_100 IN EAFILE AS FILEA
JOIN NEWKEY IN KAY1A TO ALL CHECKKEY IN PAYCHECK AS FILEB
-*
DEFINE FILE KAY1A
EMP_NAME_FS2 /A33 WITH EMP_NAME_FST =
', ' | EMP_NAME_FST || (' ' | EMP_NAME_MID) ;
EMP_NAME_LS2 /A31 WITH EMP_NAME_LST =
EMP_NAME_LST || (' ' | EMP_NAME_SUF) ;
EMP_NAME /A64 WITH EMP_NAME_LST = EMP_NAME_LS2 || EMP_NAME_FS2 ;
EMP_NAME_EA /A30 WITH EMP_NAME_LST =
SUBSTR(64,EMP_NAME,1,30,30,EMP_NAME_EA);
WLBLDPT /A35 = DECODE ASGN_DPT(DEPT ELSE ' ');
LBLDPTX /A35 = IF WLBLDPT EQ ' ' THEN ASGN_DPT ELSE WLBLDPT;
LBL_DEPT /A26 = EDIT (LBLDPTX, '9999999999999999999999999$');
XDEPT /A20 = EDIT (LBLDPTX, '99999999999999999999$');
END
-*
TABLE FILE KAY1A
-*
PRINT
-*
POSKEY
NEWKEY
GRANT
EMP_ID_210
EMP_NAME_EA
POS_NUM_210
ASGN_BGN_DT
ASGN_END_DT
JOB_CLS_EB
ANN_SLRY
FTE_EB
ASGN_UNIT
JOB_CD_210
JOB_CLS_EB
PAYRATE_EB
ANN_SLRY_FLG
PAY_PER_EB
LBLDPTX
EBEOM
EBEPU
CHECK_DATE
TOT_GROSS
TOTNETPAY
ISGRANT
-*
BY POSKEY
-*BY LOWEST JOB_CD_210
BY CHECK_DATE
-*
ON TABLE HOLD AS KAY1B FORMAT FOCUS INDEX NEWKEY
-*
END
-*
JOIN CLEAR *
JOIN POSKEY IN KAY1B TO ROOT_KEY_EP IN EPFILE
-*
DEFINE FILE KAY1B
POSNUM /A3 = EDIT (POS_900_NUM,'999$$$');
CLASS /A1 = EDIT (POS_900_NUM,'$$$9$$');
POS_NUM /A6 = EDIT (ROOT_KEY_EP,'$$$$$999999');
END
-*
TABLE FILE KAY1B
-*
PRINT
-*
POS_900_NUM
POSNUM
EMP_NAME_EA
EMP_ID_210
POS_NUM
POS_TITLE
FND_ACCT_NUM
ORIG_FTE
AMT_PCT_EP
ORIG_FND_AMT
POS_UNIT
POS_NUM_210
ASGN_UNIT
JOB_CLS_EB
ANN_SLRY
FTE_EB
GRANT_CNTRCT
ASGN_BGN_DT
ASGN_END_DT
PAYRATE_EB
ANN_SLRY_FLG
PAY_PER_EB
LBLDPTX
EBEOM
EBEPU
CHECK_DATE
TOT_GROSS
TOTNETPAY
ISGRANT
BY POS_900_NUM
BY CHECK_DATE
-*
IF POS_END_DT GT &YYMD
-*
ON TABLE HOLD AS KAY1C FORMAT FOCUS INDEX POS_900_NUM
-*
END
-*
DEFINE FILE KAY1C
FY /A2 = '&FISCALYR';
FTE /P8.2= IF (AMT_PCT_EP LT 1 AND ORIG_FTE LT 1)
THEN AMT_PCT_EP ELSE ORIG_FTE;
END
-RUN
-********************************************************
TABLE FILE KAY1C
-*
PRINT
-*
FND_ACCT_NUM
POSNUM
EMP_NAME_EA
EMP_ID_210
POS_NUM
POS_TITLE
ORIG_FTE
AMT_PCT_EP
ORIG_FND_AMT
POS_UNIT
POS_NUM_210
ASGN_UNIT
JOB_CLS_EB
ANN_SLRY
FTE_EB
GRANT_CNTRCT
ASGN_BGN_DT
ASGN_END_DT
PAYRATE_EB
ANN_SLRY_FLG
PAY_PER_EB
POS_900_NUM
FY
LBLDPTX
EBEOM
EBEPU
CHECK_DATE
TOT_GROSS
TOTNETPAY
ISGRANT
BY FND_ACCT_NUM
ON TABLE HOLD AS KAY1D FORMAT FOCUS INDEX FND_ACCT_NUM
END
-RUN
-********************************************************
JOIN CLEAR *
JOIN FND_ACCT_NUM IN KAY1D TO PLUS_ACCT IN ADV2BAN2 AS LASTJOIN
TABLE FILE KAY1D
PRINT
POSNUM
EMP_NAME_EA
EMP_ID_210
POS_NUM
POS_TITLE
FND_ACCT_NUM
ORIG_FTE
AMT_PCT_EP
ORIG_FND_AMT
POS_UNIT
POS_NUM_210
ASGN_UNIT
JOB_CLS_EB
ANN_SLRY
FTE_EB
GRANT_CNTRCT
ASGN_BGN_DT
ASGN_END_DT
PAYRATE_EB
ANN_SLRY_FLG
PAY_PER_EB
BANNER_FUND
BANNER_ORG
BANNER_ACCT
BANNER_PGM
FY
LBLDPTX
EBEOM
EBEPU
CHECK_DATE
TOT_GROSS
TOTNETPAY
ISGRANT
BY EMP_ID_210
BY CHECK_DATE
ON TABLE HOLD AS KAY1E FORMAT FOCUS INDEX EMP_ID_210
END
-RUN
-*
TABLE FILE KAY1E
PRINT
POSNUM
EMP_NAME_EA
EMP_ID_210
FND_ACCT_NUM
GRANT_CNTRCT
BANNER_FUND
ASGN_BGN_DT
ASGN_END_DT
BY EMP_ID_210 NOPRINT
END
-RUN
-*
TABLE FILE KAY1E
PRINT
POSNUM
EMP_NAME_EA
EMP_ID_210
POS_NUM
POS_TITLE
FND_ACCT_NUM
ORIG_FTE
AMT_PCT_EP
ORIG_FND_AMT
POS_UNIT
POS_NUM_210
ASGN_UNIT
JOB_CLS_EB
ANN_SLRY
FTE_EB
GRANT_CNTRCT
ASGN_BGN_DT
ASGN_END_DT
PAYRATE_EB
ANN_SLRY_FLG
PAY_PER_EB
BANNER_FUND
BANNER_ORG
BANNER_ACCT
BANNER_PGM
FY
LBLDPTX
EBEOM
EBEPU
CHECK_DATE
TOT_GROSS
TOTNETPAY
ISGRANT
EMP_ID_210
CHECK_DATE
BY BANNER_FUND
ON TABLE HOLD AS KAY1F FORMAT FOCUS INDEX BANNER_FUND
END
-RUN
-*
TABLE FILE GRANTFIX
PRINT
GRNT_CODE
GRNT_PI_ID
GRNT_PI_NAME
GRNT_FND
END
-RUN
-*
TABLE FILE GRANTFIX
PRINT
GRNT_CODE
GRNT_PI_ID
GRNT_PI_NAME
GRNT_FND
BY GRNT_FND
ON TABLE HOLD AS KAY1G FORMAT FOCUS INDEX GRNT_FND
END
-RUN
-*
JOIN CLEAR *
JOIN BANNER_FUND IN KAY1F TO ALL GRNT_FND IN KAY1G
-*
TABLE FILE KAY1F
PRINT
POSNUM
EMP_NAME_EA
EMP_ID_210
POS_NUM
POS_TITLE
FND_ACCT_NUM
ORIG_FTE
AMT_PCT_EP
ORIG_FND_AMT
POS_UNIT
POS_NUM_210
ASGN_UNIT
JOB_CLS_EB
ANN_SLRY
FTE_EB
GRANT_CNTRCT
ASGN_BGN_DT
ASGN_END_DT
PAYRATE_EB
ANN_SLRY_FLG
PAY_PER_EB
BANNER_FUND
BANNER_ORG
BANNER_ACCT
BANNER_PGM
FY
LBLDPTX
EBEOM
EBEPU
CHECK_DATE
TOT_GROSS
TOTNETPAY
ISGRANT
GRNT_CODE
GRNT_PI_ID
GRNT_PI_NAME
GRNT_FND
BY EMP_ID_210
BY CHECK_DATE
WHERE EMP_ID_210 EQ '486449244'
ON TABLE HOLD AS KAY1H FORMAT FOCUS INDEX EMP_ID_210
END
-RUN
-*
TABLE FILE KAY1H
PRINT
EMP_ID_210
FND_ACCT_NUM
BANNER_FUND
GRNT_PI_NAME
GRNT_FND
END
-RUN
-*
SET PAGE = NOPAGE
SET PAGE = OFF
SET LINES = 999999
-*
DEFINE FILE KAY1H
PAYPERIODS /P2 = ATODBL(PAY_PER_EB,'2','D2');
GRANTPAY /P09.2C = (PAYRATE_EB * PAYPERIODS * FTE_EB);
FULLPAY /P09.2C = (PAYRATE_EB * PAYPERIODS);
FOAP /A28 = BANNER_FUND || '-' || BANNER_ORG || '-' ||
BANNER_ACCT || '-' || BANNER_PGM;
PERCNT /P6.2 = FTE_EB * 100;
ACTIVITY /A40 = 'ACTIVITIES PERFORMED RELATED TO GRANTS';
GRANT /A07 = 'GRANT #';
LINEINFO /A70 =
'____________________________________________________________________';
CERTIFY /A56 =
'I CERTIFY THE ABOVE INFORMATION IS CORRECT AS REPORTED';
BLANKLN /A03 = ' ';
EMPSIG /A56 =
'_______________________________________ (EMPLOYEE NAME)';
GRNT_NM /A25 = SUBSTR(76,GRNT_PI_NAME,1,25,26,GRNT_NM);
END
TABLE FILE KAY1H
-*
HEADING CENTER
-*
" "
"PROGRAM ID:BANNER01 <113 PAGE:<TABPAGENO"
"A L A M O C O M M U N I T Y C O L L E G E D I S T R I C T "
"H U M A N R E S O U R C E S Y S T E M "
"COMPENSATION ANALYSIS REPORT DATA - AS OF &DATE"
"FOR THE PERIOD OF &QTRDT1 THROUGH &QTRDT2"
" "
-*
-*PRINT
WRITE
-*
-*** FST.CHECK_DATE AS ' ' IN 0
-*** FST.POS_NUM_210 AS ' ' IN 13
-*** FST.FOAP AS ' ' IN 24
-*** FST.FND_ACCT_NUM AS ' ' IN 46
-*** FST.GRNT_CODE AS ' ' IN 58
-*** FST.GRNT_NM AS ' ' IN 65
-*** FST.PERCNT AS ' ' IN 107
-*** FST.TOT_GROSS AS ' ' IN 115
-*** FST.TOTNETPAY AS ' ' IN 111
FST.FOAP AS ' ' IN 0
FST.FND_ACCT_NUM AS ' ' IN 22
FST.GRNT_CODE AS ' ' IN 34
FST.GRNT_NM AS ' ' IN 40
FST.PERCNT AS ' ' IN 65
FST.TOT_GROSS AS ' ' IN 73
FST.POS_NUM AS ' ' IN 80
FST.CHECK_DATE AS ' ' IN 92
-*
-*BY FY NOPRINT REPAGE
BY EMP_ID_210 NOPRINT
BY POS_NUM_210 NOPRINT
-***BY CHECK_DATE NOPRINT
-***BY FOAP NOPRINT
-***BY FND_ACCT_NUM NOPRINT
-***BY LBLDPTX NOPRINT
BY FOAP NOPRINT
BY FND_ACCT_NUM NOPRINT
BY GRNT_CODE NOPRINT
BY GRNT_NM NOPRINT
BY POS_NUM_210 NOPRINT
BY CHECK_DATE NOPRINT
BY LBLDPTX NOPRINT
-*
ON EMP_ID_210 SUBHEAD
" "
"EMP ID: <EMP_ID_210 NAME:<EMP_NAME_EA DEPT:<LBLDPTX"
" TOT FROM GRANT:<TOT.GRANTPAY TOT OVERALL:<TOT.FULLPAY"
"-------------------------------------------------------------------------------
-----------------------------------------"
"<0 BANNER <23 HRS <35 PI <41 PI ID <56 %PAID
<62 GROSS "
"<0 ACCOUNT <23 ACCOUNT <35 NUMBER <41 NAME <56 BY GRNT
<62 PAY"
-*"<0 DATE <12 POS <24 BANNER <49 HRS <60 % PAID <76 GROSS <85 NET"
-*0 PAID <12 NUM <24 ACCOUNT <49 ACCOUNT <60 BY GRNT <76 PAY <85 PA
"-------------------------------------------------------------------------------
-----------------------------------------"
-*
ON EMP_ID_210 SUBFOOT
"<ACTIVITY"
"<GRANT"
"<LINEINFO"
"<LINEINFO"
"<LINEINFO"
"<LINEINFO"
"<LINEINFO"
"<LINEINFO"
"<LINEINFO"
"<LINEINFO"
"<LINEINFO"
"<LINEINFO"
"<LINEINFO"
"<LINEINFO"
"<LINEINFO"
"<LINEINFO"
"<LINEINFO"
"<CERTIFY"
"<BLANKLN"
"<EMPSIG"
IF ISGRANT EQ '9'
END
-RUN
I agree with the SUM... There's a lot of code here and we don't have access to "EDCHEK" file of know your inputs for &QTRDT(1 and 2). So, it's a bit difficult to work with this code.
Can you duplicate the issue with either the CAR or EMP(something) files?
Also, what is your output format? Note that the multiple spaces between "A L A M O" and "C O M M U N I T Y" etc, will not be spaced as it looks like you desire if it's in HTML.
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005