×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Adding ONE field to a report with several HOLD files

Adding ONE field to a report with several HOLD files

Adding ONE field to a report with several HOLD files

(OP)
I am trying to add one column to a report with several hold files.

The report should looks like this:
JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC YTD AVG

I just need to add GROSS YTD after avg, but it appears as zero values.

Here is the code:
********************

TABLE FILE SQLOUT
PRINT
    CLIENT_ID
    CLIENT_NAME/A18 AS 'NAME'
    MONTH/I2 AS 'ACT_MONTH'
    NETAMOUNT/P8MB AS 'AMOUNT'
    GROSSAMOUNT AS 'GROSSAMOUNT'


COMPUTE YTD/P8MB = IF CLIENT_ID NE LAST CLIENT_ID THEN NETAMOUNT
                    ELSE YTD + NETAMOUNT; AS 'YTD'

COMPUTE GROSS_YTD/P8MB = IF CLIENT_ID NE LAST CLIENT_ID THEN GROSSAMOUNT
                    ELSE GROSS_YTD + GROSSAMOUNT; AS 'GROSSYTD'


-CONT
BY
    YEAR
BY
    REGION
&SORT_NAME

ON TABLE HOLD AS SMA50EXTR FORMAT ALPHA
END
-RUN

-*------  Handling No records found situation   ---------
-IF &LINES EQ 0 THEN GOTO NORECORDS ;
-*
-*-------------------------------------------------------------------
-*  Getting the maximum Month for the selected Region and Selected
-*  Year..for controlling the space between columns in the report
-*-------------------------------------------------------------------
TABLE FILE SMA50EXTR
SUM MAX.ACT_MONTH
ON TABLE SAVE AS MAXMNTH
END
-RUN
-READ MAXMNTH, &MNTHCNT
-SET &COLSPACE = IF &MNTHCNT LE 5 THEN 'IN +2' ELSE 'IN +1';
-*
DEFINE FILE SMA50EXTR
CMONTH/A3  = DECODE ACT_MONTH (1 'JAN'
                               2 'FEB'
                               3 'MAR'
                               4 'APR'
                               5 'MAY'
                               6 'JUN'
                               7 'JUL'
                               8 'AUG'
                               9 'SEP'
                              10 'OCT'
                              11 'NOV'
                              12 'DEC' );
END
-RUN
TABLE FILE SMA50EXTR
SUM
    AMOUNT AS ''
    GROSSAMOUNT AS ''
ACROSS
    CMONTH AS ''
BY
    REGION

&SORT_NAME

BY
    CLIENT_ID
BY
    NAME

ON TABLE HOLD AS SMATEMP
END
-RUN
-*---------------------------------------------------------------------
-*  The below script creates a HOLD file that contains all the months.
-*  In the Subsequent Sections of the report we will be using this file
-*  in order to Display the missing or future months.
-*---------------------------------------------------------------------
DEFINE FILE SMA50EXTR
JAN/P8MB = 0;
FEB/P8MB = 0;
MAR/P8MB = 0;
APR/P8MB = 0;
MAY/P8MB = 0;
JUN/P8MB = 0;
JUL/P8MB = 0;
AUG/P8MB = 0;
SEP/P8MB = 0;
OCT/P8MB = 0;
NOV/P8MB = 0;
DEC/P8MB = 0;
GRS/P8MB = 0;
END
-RUN
TABLE FILE SMA50EXTR
SUM
    JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC GRS
BY
    REGION

&SORT_NAME

BY
    CLIENT_ID
BY
    NAME

ON TABLE HOLD AS MATCHSMA
END
-RUN
MATCH FILE SMATEMP
SUM *
BY
    REGION

&SORT_NAME

BY
    CLIENT_ID
BY
    NAME
RUN
FILE MATCHSMA
SUM JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC GRS
BY
    REGION

&SORT_NAME

BY
    CLIENT_ID
BY
    NAME
AFTER MATCH HOLD AS SMAHOLD OLD
END
-RUN
DEFINE FILE SMAHOLD
TYTD/P8MB  = JAN+FEB+MAR+APR+MAY+JUN+JUL+AUG+SEP+OCT+NOV+DEC;
AVG/P8MB   = TYTD / &MNTHCNT;
GRSB/P8MB  = GRS;
END
-RUN

TABLE FILE SMAHOLD
PRINT
     CLIENT_ID
     NAME
     JAN
     FEB
     MAR
     APR
     MAY
     JUN
     JUL
     AUG
     SEP
     OCT
     NOV
     DEC
     TYTD
     AVG
     GRSB

BY
     REGION

&SORT_NAME

BY HIGHEST 50 TYTD NOPRINT

ON TABLE HOLD AS SMA50HLD
END
-RUN
-*

DEFINE FILE SMA50HLD
HRANK/I2  = IF &BYSORT EQ LAST &BYSORT AND
    CLIENT_ID EQ LAST CLIENT_ID THEN 1
    ELSE IF &BYSORT EQ LAST &BYSORT AND
    CLIENT_ID NE LAST CLIENT_ID THEN HRANK + 1
    ELSE 1;
CITY1/A19   = 'CITY : ' | EDIT(CITY,'999999999999$$$$$$$$$');
REGION1/A30 = 'Region : ' | REGION;
BLK1/A1     = ' ';
-IF &BYNAME EQ 'CITY' THEN GOTO SKIPTHIS;
ORIGIN/A25  = 'Origin : ' | ORIGIN_CITY_CODE;
SUBFT1/A32  = 'Origin ' | ORIGIN_CITY_CODE | ' Total:';
-GOTO ENDDEFINE;
-SKIPTHIS
ORIGIN/A25  = ' ';
SUBFT1/A20  = EDIT(CITY,'9999999999999$$$$$$$$') || ' Total:';
-ENDDEFINE
CUR_DT_TM/HMDYYSA = HGETC(10,CUR_DT_TM);
END
-RUN
-*
TABLE FILE SMA50HLD
PRINT
     CLIENT_ID/A8 IN +1  AS 'Client Id'
     NAME/A20     IN +1  AS 'Name'
     JAN          IN +1  AS 'Jan'
     FEB          IN +1  AS 'Feb'
     MAR          IN +1  AS 'Mar'
     APR          IN +1  AS 'Apr'
     MAY  &COLSPACE      AS 'May'
     JUN  &COLSPACE      AS 'Jun'
     JUL  &COLSPACE      AS 'Jul'
     AUG  &COLSPACE      AS 'Aug'
     SEP  &COLSPACE      AS 'Sep'
     OCT  &COLSPACE      AS 'Oct'
     NOV  &COLSPACE      AS 'Nov'
     DEC  &COLSPACE      AS 'Dec'
     TYTD &COLSPACE      AS 'YTD'
     AVG  &COLSPACE      AS 'Avg'
     GRSB &COLSPACE      AS 'Gross'
BY
     REGION  NOPRINT
BY
     &BYSORT NOPRINT
BY
     HRANK   IN +0 AS ''
ACROSS
     BLK1    NOPRINT

WHERE HRANK LE 50

ON REGION PAGE-BREAK

Thanks,

Leo   

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close