Hello There,

I am trying to add in three columns, based solely on getting the previous month's data to display. I am not sure the correct steps to take at this point. So, if you could direct me into what steps I have to take and codes I must include that would be super!! below is the code:

-**************************

-SET &ECHO=ALL;

JOIN
EC_CONTRACT_INDEX.CONTRACT_ID IN EC_CONTRACT_INDEX
TO ALL EC_LINE_ITEM.CONTRACT_ID IN EC_LINE_ITEM AS J1
END

JOIN
EC_LINE_ITEM.CONTRACT_ID AND EC_LINE_ITEM.MOD_ID IN EC_CONTRACT_INDEX
TO ALL EC_CONTRACT.CONTRACT_ID AND EC_CONTRACT.MOD_ID IN EC_CONTRACT AS J99
END

TABLE FILE EC_CONTRACT_INDEX
SUM
DIVISION_NUMBER
PROGRAM_NUMBER
CONTRACT_VALUE
BY CONTRACT_ID
BY HIGHEST 1 EC_LINE_ITEM.MOD_ID
BY COST_ACCUMULATOR_ID
WHERE DIVISION_NUMBER EQ '08'
WHERE UNIT_PRICE_BASIS NE 'UE'
WHERE STATUS NE 'X'

WHERE RECORDLIMIT EQ 1000
WHERE READLIMIT EQ 10000
-*ON TABLE PCHOLD FORMAT HTML

ON TABLE HOLD AS HOLD
END

-*
JOIN
HOLD.CONTRACT_ID AND HOLD.MOD_ID AND HOLD.COST_ACCUMULATOR_ID IN HOLD
TO ALL EC_COST_ACCUMULATOR.CONTRACT_ID AND EC_COST_ACCUMULATOR.MOD_ID
AND EC_COST_ACCUMULATOR.COST_ACCUMULATOR_ID IN EC_COST_ACCUMULATOR AS J3
END
-*
DEFINE FILE HOLD
FP=IF CONTRACT_TYPE EQ 'FACL'
OR 'FFP' OR 'FPDQ' OR 'FPEF' OR 'FPIQ'
OR 'FPLE' OR 'FPPR' OR 'FPRQ' OR 'FPRR'
OR 'LBHR' OR 'SFP' THEN CONTRACT_VALUE ELSE 0;
FPI=IF CONTRACT_TYPE EQ 'FPIA' OR 'FPIF' OR 'FPIS' THEN CONTRACT_VALUE ELSE 0;
CP=IF CONTRACT_TYPE CONTAINS 'CP' OR 'CR' THEN CONTRACT_VALUE ELSE 0;
TM=IF CONTRACT_TYPE CONTAINS 'TAM' THEN CONTRACT_VALUE ELSE 0;
NA=IF CONTRACT_TYPE EQ 'AOD'
OR 'BAA' OR 'BOA' OR 'IDIQ' OR 'NC'
OR 'OTA' OR 'UCA' THEN CONTRACT_VALUE ELSE 0;

OPT_COM/A12 = ' ';
LEVEL_1/A12 = ' ';
LEVEL_2/A12 = ' ';
MAPPING/A12 = ' ';

ADJUST_AMNT/A100 = ' ';
MARK_ADJUST/A20 = ' ';
ADJUST_EXP/A100 = ' ';
ADJUST_COMMT/A100 = ' ';
END
-*
TABLE FILE HOLD
SUM
DIVISION_NUMBER
PROGRAM_NUMBER
CONTRACT_VALUE
FP
FPI
CP
TM
NA
COST_ACCUMULATOR_ID
BOOKING_ACCUMULATOR

OPT_COM
LEVEL_1
LEVEL_2
MAPPING

ADJUST_AMNT
MARK_ADJUST
ADJUST_EXP
ADJUST_COMMT
BY CONTRACT_ID
BY MOD_ID
-*BY BOOKING_ACCUMULATOR NOPRINT

-*ON TABLE PCHOLD FORMAT HTML

ON TABLE HOLD AS HOLD1
END
-*
JOIN
HOLD1.CONTRACT_ID AND HOLD1.MOD_ID IN HOLD1
TO ALL EC_CONTRACT.CONTRACT_ID AND EC_CONTRACT.MOD_ID
IN EC_CONTRACT AS J5
END
-*
TABLE FILE HOLD1
SUM BOOKING_ACCUMULATOR
DIVISION_NUMBER
FUNDING_SOURCE
PROGRAM_NUMBER
PROGRAM_PROJECT
CONTRACT_NUMBER
CONTRACT_TYPE
RELEASE_NUMBER
TITLE
START_DATE
COMPLETION_DATE
MANAGER_ID
FP
FPI
CP
TM
NA
CONTRACT_VALUE
STATUS

OPT_COM
LEVEL_1
LEVEL_2
MAPPING

ADJUST_AMNT
MARK_ADJUST
ADJUST_EXP
ADJUST_COMMT
BY CONTRACT_ID
-*BY BOOKING_ACCUMULATOR
WHERE CONTRACT_TYPE NE 'IDIQ' OR 'OTA' OR 'UCA' OR 'BOA' OR 'BAA'
ON TABLE HOLD AS HOLD2
END
-*
JOIN
HOLD2.DIVISION_NUMBER AND HOLD2.FUNDING_SOURCE IN HOLD2 TO ALL
EC_FUNDING_SOURCE.DIVISION_NUMBER AND EC_FUNDING_SOURCE.FUNDING_SOURCE
IN EC_FUNDING_SOURCE AS J7
END
-*
JOIN
HOLD2.MANAGER_ID IN HOLD2
TO ALL EC_USER.USER_ID IN EC_USER AS J9
END
-*
DEFINE FILE HOLD2
CONTRACT_TYPE_1/A10=
IF FP GT CP AND FP GT FPI AND FP GT TM AND FP GT NA THEN 'FP' ELSE
IF FPI GT CP AND FPI GT FP AND FPI GT TM AND FPI GT NA THEN 'FPI' ELSE
IF CP GT FP AND CP GT FPI AND CP GT TM AND CP GT NA THEN 'CP' ELSE
IF TM GT FP AND TM GT FPI AND TM GT CP AND TM GT NA THEN 'TAM'
-* IF NA GT FP AND NA GT FPI AND NA GT CP AND NA GT TM THEN 'NA'
ELSE ' ';
CONTRACT_TYPE_2/A10=
IF CONTRACT_TYPE_1 NE ' ' THEN CONTRACT_TYPE_1 ELSE CONTRACT_TYPE;
APPROVE/A10=IF STATUS EQ 'A' THEN 'Approved'
ELSE IF STATUS EQ 'I' OR 'W' THEN 'WIP' ELSE ' ';
NAME/A35 = LAST_NAME || (', '|FIRST_NAME);
START_DATE/MDYY = HDATE(START_DATE, 'MDYY');
COMPLETION_DATE/MDYY = HDATE(COMPLETION_DATE, 'MDYY');
-* today's date
TODAY/MDYY=&MDYY;
TOT_VALUE/D16.2 = FP + FPI+ CP+ TM

-* trying to create a display right now for the previous month, when I uncomment
-*the code below...it will get you the debuggin code you see on the bottom
-*
-*-SET &CALCDTE = EDIT (&START_DATE);
-*-SET PREV_MON = SUBSTR(&CALCDTE.LENGTH, &CALCDTE, 1, 2, 2, 'A2');
-*-SET PREV_DATE = IF PREV_MON EQ '01' THEN '12'
-*-ELSE IF PREV_MON EQ '02' THEN '01'
-*-ELSE IF PREV_MON EQ '03' THEN '02'
-*-ELSE IF PREV_MON EQ '04' THEN '03'
-*-ELSE IF PREV_MON EQ '05' THEN '04'
-*-ELSE IF PREV_MON EQ '06' THEN '05'
-*-ELSE IF PREV_MON EQ '07' THEN '06'
-*-ELSE IF PREV_MON EQ '08' THEN '07'
-*-ELSE IF PREV_MON EQ '09' THEN '08'
-*-ELSE IF PREV_MON EQ '10' THEN '09'
-*-ELSE IF PREV_MON EQ '11' THEN '10'
-*-ELSE EQ '12';
-*
END
-*
TABLE FILE HOLD2
HEADING
"Sorted and Summed by ECE Unique ID"
"Run Date/Time: &DATE &TOD"
PRINT
DIVISION_NUMBER NOPRINT

OPT_COM AS 'Operating Company'

PROGRAM_PROJECT AS 'Dept'
PROGRAM_NUMBER AS 'Product Line'
BOOKING_ACCUMULATOR AS 'Master Req'

LEVEL_1 AS 'Level 1'
LEVEL_2 AS 'Level 2'
MAPPING AS 'Mapping'

CONTRACT_NUMBER AS 'Contract Number'
RELEASE_NUMBER AS 'DO Number'
TITLE AS 'Title'
START_DATE AS 'POP Start'
COMPLETION_DATE AS 'POP End'
NAME AS 'Name'
-*CONTRACT_TYPE_1 AS 'Contract Type'
CONTRACT_TYPE_2 AS 'Contract Type'
DESCRIPTION AS 'Funded By'
FP/D16.2CM AS 'Fixed Price Order Value'
FPI/D16.2CM AS 'Fixed Price Incentive Order Value'
CP/D16.2CM AS 'Cost Type Order Value'
TM/D16.2CM AS 'Time and Materials Order Value'
NA/D16.2CM AS 'NA Order Value'
CONTRACT_VALUE/D16.2CM AS 'Order Value'
TOT_VALUE/D16.2CM AS 'Order Value Calc'

-* AS 'Previous Month Orders Value Calc'
-* AS 'Prelim Month Orders'
ADJUST_AMNT AS 'Adjustment Amount'
MARK_ADJUST AS 'Mark(s) for Adjustments'
-* AS 'Total Previous Month 2008 Orders'
ADJUST_EXP AS 'Adjustment Explanation'
ADJUST_COMMT AS 'Adjustment Comment'

-*APPROVE AS 'Approved/WIP'
-*TODAY AS 'Run Date'
BY CONTRACT_ID AS 'ECE Unique ID'
BY CONTRACT_NUMBER NOPRINT
BY RELEASE_NUMBER NOPRINT
-*BY BOOKING_ACCUMULATOR NOPRINT
WHERE BOOKING_ACCUMULATOR NE '???' OR 'NOBILL'
WHERE APPROVE NE 'WIP'
WHERE RECORDLIMIT EQ 100
WHERE READLIMIT EQ 100
ON TABLE PCHOLD FORMAT HTML
-*ON TABLE PCHOLD FORMAT EXCEL
END
-EXIT


-***********************************************



0 NUMBER OF RECORDS IN TABLE= 330645 LINES= 1
0 ERROR AT OR NEAR LINE 19 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC282) RESULT OF EXPRESSION IS NOT COMPATIBLE WITH THE FORMAT OF FIELD:
CALCDTE
0 ERROR AT OR NEAR LINE 20 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: CALCDTE.LENGTH
0 ERROR AT OR NEAR LINE 21 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: PREV_MON
0 ERROR AT OR NEAR LINE 45 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: PREV_DATE
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT


Thank you in advance

This message has been edited. Last edited by: Kerry,

TOT_VALUE/D16.2 = FP + FPI+ CP+ TM

This may be a nit but the line above doesn't end in a semicolon.

And you don't have ampersands in front of any of your variables in the commented code.

And you've got an extra EQ on the last line. This works:

-SET &CALCDTE = EDIT (&START_DATE);
-SET &PREV_MON = SUBSTR(&CALCDTE.LENGTH, &CALCDTE, 1, 2, 2, 'A2');
-SET &PREV_DATE = IF &PREV_MON EQ '01' THEN '12'
-ELSE IF &PREV_MON EQ '02' THEN '01'
-ELSE IF &PREV_MON EQ '03' THEN '02'
-ELSE IF &PREV_MON EQ '04' THEN '03'
-ELSE IF &PREV_MON EQ '05' THEN '04'
-ELSE IF &PREV_MON EQ '06' THEN '05'
-ELSE IF &PREV_MON EQ '07' THEN '06'
-ELSE IF &PREV_MON EQ '08' THEN '07'
-ELSE IF &PREV_MON EQ '09' THEN '08'
-ELSE IF &PREV_MON EQ '10' THEN '09'
-ELSE IF &PREV_MON EQ '11' THEN '10'
-ELSE '12';

BTW, you could use a DECODE for &PREV_DATE.

And, on top of Ginny's valid remark, use:

quote:

PREV_MON/A2 = EDIT('&START_DATE.EVAL,'99');
PREV_DATE/A2 = IF PREV_MON EQ '01' THEN '12' ELSE
IF PREV_MON EQ '02' THEN '01' ELSE
IF PREV_MON EQ '03' THEN '02' ELSE
IF PREV_MON EQ '04' THEN '03' ELSE
IF PREV_MON EQ '05' THEN '04' ELSE
IF PREV_MON EQ '06' THEN '05' ELSE
IF PREV_MON EQ '07' THEN '06' ELSE
IF PREV_MON EQ '08' THEN '07' ELSE
IF PREV_MON EQ '09' THEN '08' ELSE
IF PREV_MON EQ '10' THEN '09' ELSE
IF PREV_MON EQ '11' THEN '10' ELSE '12';

Since you're in a DEFINE, the DM approach will (in this case) not work. The above code transforms it into fields.

Hope this helps ...

Yup Thank you Both! I got the report to display, but when I tried to place one of the PREV_DATE as a column that I want to display, I got:


0 NUMBER OF RECORDS IN TABLE= 1000 LINES= 70
(BEFORE TOTAL TESTS)
0 NUMBER OF RECORDS IN TABLE= 10 LINES= 5
0 NUMBER OF RECORDS IN TABLE= 4 LINES= 4
0 ERROR AT OR NEAR LINE 180 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC257) MISSING QUOTE MARKS: ('START_DATE.EVAL,'9
BYPASSING TO END OF COMMAND
0 ERROR AT OR NEAR LINE 244 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: PREV_DATE
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT


any thoughts on why that isnt working?

Take the quote off of the beginning of &START_DATE.EVAL.

Mayor

the error message says it all.....

MISSING QUOTE MARKS: ('START_DATE.EVAL,'9


and there is a much easier way to calculate the previous date..

you should read some manuals.

Shouldn't a quote be added to the end of &START_DATE.EVAL?

When in a situation where either DECODE OR IF-THEN-ELSE will work, I use DECODE (see PREV_DATE_2 calculation below) as it is easier to read.

However, since you are manipulating a date, I would use Date Functions (see PREV_DATE_3 calculation below for one method).

-SET &START_DATE = 04082008;
-*
DEFINE FILE CAR
PREV_MON/A2 = EDIT('&START_DATE.EVAL','99');
PREV_DATE_1/A2 = IF PREV_MON EQ '01' THEN '12' ELSE
IF PREV_MON EQ '02' THEN '01' ELSE
IF PREV_MON EQ '03' THEN '02' ELSE
IF PREV_MON EQ '04' THEN '03' ELSE
IF PREV_MON EQ '05' THEN '04' ELSE
IF PREV_MON EQ '06' THEN '05' ELSE
IF PREV_MON EQ '07' THEN '06' ELSE
IF PREV_MON EQ '08' THEN '07' ELSE
IF PREV_MON EQ '09' THEN '08' ELSE
IF PREV_MON EQ '10' THEN '09' ELSE
IF PREV_MON EQ '11' THEN '10' ELSE '12';
-*///////////////////////////////////////////////////////
PREV_DATE_2/A2 = DECODE PREV_MON(01 12
                                 02 01
                                 03 02
                                 04 03
                                 05 04
                                 06 05
                                 07 06
                                 08 07
                                 09 08
                                 10 09
                                 11 10
                                 12 11
                               ELSE NA);
-*///////////////////////////////////////////////////////
STARTDATE_MDYY/MDYY = &START_DATE.EVAL;
STARTDATE_YYMD/I8YYMD = DATECVT(STARTDATE_MDYY, 'MDYY', 'I8YYMD');
START_MONTH/I6YYM = STARTDATE_YYMD/100;
PREV_MONTH/I6YYM = AYM(START_MONTH, -1, 'I6YYM');
PREV_DATE_3/A2 = EDIT(PREV_MONTH, '$$$$99')
END
-*
TABLE FILE CAR
PRINT
      CAR NOPRINT
      PREV_MON
      PREV_DATE_1
      PREV_DATE_2
      PREV_DATE_3
WHERE RECORDLIMIT EQ 1
END

quote:

PREV_DATE

Calculating the previous month from an date.


If your START_DATE is a smartdate field in the database you can say

DEFINE FILE CAR
START_DATE/MDYY='12152008';
PREVPERIOD/YYMD=DATEMOV(START_DATE, 'BOM')-1;
PREVMONTH/M=PREVPERIOD;
END

The second line moves the start_date field to the first day of the month ( BOM ) and by subtracting 1 you get the last day of the previous month.
The next step makes this field to a M month only field.

You can do this in the beginning of your application.

quote:

DEFINE FILE HOLD
FP=IF CONTRACT_TYPE EQ 'FACL'
OR 'FFP' OR 'FPDQ' OR 'FPEF' OR 'FPIQ'
OR 'FPLE' OR 'FPPR' OR 'FPRQ' OR 'FPRR'
OR 'LBHR' OR 'SFP' THEN CONTRACT_VALUE ELSE 0;
FPI=IF CONTRACT_TYPE EQ 'FPIA' OR 'FPIF' OR 'FPIS' THEN CONTRACT_VALUE ELSE 0;
CP=IF CONTRACT_TYPE CONTAINS 'CP' OR 'CR' THEN CONTRACT_VALUE ELSE 0;
TM=IF CONTRACT_TYPE CONTAINS 'TAM' THEN CONTRACT_VALUE ELSE 0;
NA=IF CONTRACT_TYPE EQ 'AOD'
OR 'BAA' OR 'BOA' OR 'IDIQ' OR 'NC'
OR 'OTA' OR 'UCA' THEN CONTRACT_VALUE ELSE 0;

How is this working for you? You don't have formats or field length on these defines.

quote:

You don't have formats or field length

The "Default" format is, I believe, D12.2. So as long as its a number this usually "works".

An old FOCUS hound once told me, "Default and Disaster both begin with the letter "D".

-Commando Dave

quote:

An old FOCUS hound once told me, "Default and Disaster both begin with the letter "D".

-Commando Dave

So does Dave ;-)

As I have said before, you can learn something new everyday. I'm surprised they didn't nail this down in the code tightening.