[CLOSED] how to build a FOCUS database by appending report results
We have just upgraded to 8.2.04. I have earlier reports that have been created by building FOCUS tables where report results are appended together. They still work. I am trying to create a new report and am unable to append as I have before. Here is my code in entirety:
-* File: IBFS:/DEV-8204/WFC/Repository/training/std_reports/renaecrjfomg/Procedure1.fex Created by WebFOCUS AppStudio -*WHENCE JO_HOLD1 MASTER -SET &ECHO=ALL; FILEDEF JO_HOLD1 DISK jo_hold1.ftm(APPEND DEFINE FILE ENROLLMENT_RECORD_E Course_Title2/A50V=TRIM('T', COURSE_TITLE, 50, ' ', 50, Course_Title2); Department_Title2/A50V=TRIM('T', ACADEMIC_DEPARTMENT, 50, ' ', 50, Department_Title2); UNIQUE_COURSE/A120V=CIP_CODE || COURSE_NUMBER || SECTION_NUMBER || Department_Title2 || Course_Title2; STAFF_COUNT/I9C=0; ROOM_COUNT/I6C=0; END TABLE FILE ENROLLMENT_RECORD_E SUM CNT.DST.UNIQUE_COURSE AS 'COURSE_COUNT' STAFF_COUNT ROOM_COUNT CREDIT_HOURS/P3C AS 'COURSE_CREDITS' CREDIT/P3C AS 'STUDENT_CREDITS' BY LOWEST INSTITUTION_CODE AS 'INST_CODE' WHERE INSTITUTION_CODE EQ '110' OR '111'; WHERE ACADEMIC_YEAR EQ '20172018'; ON TABLE SET PAGE-NUM NOLEAD ON TABLE SET ASNAMES ON ON TABLE NOTOTAL ON TABLE HOLD AS JO_HOLD1 FORMAT FOCUS INDEX 'INSTITUTION_CODE' ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * INCLUDE = IBFS:/WFC/Repository/training/std_reports/jackieo1s6bm/ok.sty, $ SUMMARY='REPORT ONE', TITLETEXT='REPORT ONE', $ ENDSTYLE END DEFINE FILE ENROLLMENT_RECORD_E ROOM_COUNT/I6C=0; UNIQUE_COURSE/I8C=0; STAFF_COUNT/I9C=0; COURSE_CREDIT/P3C=0; STUDENT_CREDIT/P3C=0; END
TABLE FILE ENROLLMENT_RECORD_E SUM CNT.DST.STAFF_SOCIAL_SECURITY_NUMBER AS 'STAFF_COUNT' ROOM_COUNT COURSE_CREDIT AS 'COURSE_CREDITS' STUDENT_CREDIT AS 'STUDENT_CREDITS' BY INSTITUTION_CODE AS 'INST_CODE' BY LOWEST UNIQUE_COURSE AS 'COURSE_COUNT' WHERE STAFF_SOCIAL_SECURITY_NUMBER NE 'MMMMMMMMM'; WHERE INSTITUTION_CODE EQ '110' OR '111'; WHERE ACADEMIC_YEAR EQ '20172018'; ON TABLE SET PAGE-NUM NOLEAD ON TABLE SET ASNAMES ON ON TABLE NOTOTAL ON TABLE HOLD AS JO_HOLD2 FORMAT FOCUS INDEX 'INSTITUTION_CODE' ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * INCLUDE = IBFS:/WFC/Repository/training/std_reports/jackieo1s6bm/ok.sty, $ SUMMARY='REPORT TWO', TITLETEXT='REPORT TWO', $ ENDSTYLE END DEFINE FILE ENROLLMENT_RECORD_E STUDENT_CREDIT/P3C=0; UNIQUE_COURSE/I5C=0; STAFF_COUNT/I6C=0; COURSE_CREDIT/P3C=0; BUILDING_ROOM/A15V=BUILDING_CODE || ROOM_NUMBER; END TABLE FILE ENROLLMENT_RECORD_E SUM UNIQUE_COURSE AS 'COURSE_COUNT' STAFF_COUNT CNT.DST.BUILDING_ROOM AS 'ROOM_COUNT' COURSE_CREDIT AS 'COURSE_CREDITS' STUDENT_CREDIT AS 'STUDENT_CREDITS' BY INSTITUTION_CODE AS 'INST_CODE' WHERE INSTITUTION_CODE EQ '110' OR '111'; WHERE ACADEMIC_YEAR EQ '20172018'; ON TABLE SET PAGE-NUM NOLEAD ON TABLE SET ASNAMES ON ON TABLE NOTOTAL ON TABLE HOLD AS JO_HOLD3 FORMAT FOCUS INDEX 'INSTITUTION_CODE' ON TABLE SET HTMLEMBEDIMG ON ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * INCLUDE = IBFS:/WFC/Repository/training/std_reports/jackieo1s6bm/ok.sty, $ SUMMARY='REPORT THREE', TITLETEXT='REPORT THREE', $ ENDSTYLE END USE JO_HOLD1 AS JO_HOLD1 JO_HOLD2 AS JO_HOLD1 JO_HOLD3 AS JO_HOLD1 END TABLE FILE JO_HOLD1 PRINT COURSE_COUNT STAFF_COUNT ROOM_COUNT COURSE_CREDITS STUDENT_CREDITS BY INST_CODE ON TABLE SET PAGE-NUM NOLEAD ON TABLE SET ASNAMES ON ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT HTML ON TABLE SET HTMLEMBEDIMG ON ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty, $ ENDSTYLE END
Something has changed in this new version. I have tried every way I know how, but need help. Thank you, JackieThis message has been edited. Last edited by: FP Mod Chuck,
WebFOCUS 7.6 Windows, All Outputs
October 24, 2018, 02:00 PM
BabakNYC
I'm not quite sure what the filedef ....(APPEND is doing. It doesn't seem to apply to anything that it follows. What you're doing is creating 3 focus files and then concatenating them with the USE command which should work. When you say it doesn't work, what do you get? Is there an error or do you get any output? Do you see the 3 focus files? If they're there, the USE command you have should concatenate them in the report. However, the 3 table requests have to have identical output for you to be able to concatenate them. A quick review of these 3 reports indicate the output for them might be different consequently causing the 3 hold files not to be structured the same.
When I create the final report using JO_HOLD1, I only get the first focus table results back. I have used USE before to accomplish this. Have also received information along the way to use FI or FILEDEF since USE has a limit on how many holds to append together. Around 15 I believe.
Thank you for helping me with this. Jackie O.
WebFOCUS 7.6 Windows, All Outputs
October 24, 2018, 02:46 PM
BabakNYC
That makes sense because only JO_HOLD1.MAS describes JO_HOLD1.FOC. The other two FOC files don't match the exact same data structure and column names. So, they aren't used. You'll have to make sure the outcome of the two subsequent table request is identical to the first one. Otherwise, they can't be concatenated. Take a look at the link I gave you. There are plenty of examples there for you to follow.
WebFOCUS 8206, Unix, Windows
October 24, 2018, 02:55 PM
JackieO
Thank you. I will check out the link and recheck my data structures.
Jackie O.
WebFOCUS 7.6 Windows, All Outputs
October 24, 2018, 03:38 PM
JackieO
BabakNYC,
When I try to "roll up" the last report data into just two lines, I get data like "-1,159,864.867" where I should be getting "2,513". Is this an overflow or something similar? The format was increased by one since it is a sum of all lines.
Jackie O
WebFOCUS 7.6 Windows, All Outputs
October 24, 2018, 03:54 PM
BabakNYC
I've no idea. That doesn't sound like an overflow. I'd disassemble the code and take away the ON TABLE HOLD to see what the code generates.
WebFOCUS 8206, Unix, Windows
October 24, 2018, 04:22 PM
JackieO
BabakNYC,
If my three hold files each contain six columns and all are named the same, where am I getting additional columns when I create my final report? My SEG01 contains 9 columns. Sorry to have all these questions, but I don't get this.
Jackie
WebFOCUS 7.6 Windows, All Outputs
October 24, 2018, 04:39 PM
BabakNYC
In order to concatenate 3 focus files their master files have to be identical. Your 3 requests do not generate identical files. When you try something like that it's hard to explain what outcome to expect. You should correct the 3 requests to generate identical files in order to use the same master.This message has been edited. Last edited by: BabakNYC,
WebFOCUS 8206, Unix, Windows
October 24, 2018, 04:58 PM
jfr99
Try putting this ...
ON TABLE SET HOLDLIST PRINTONLY
before each of your ON TABLE HOLD statements.
WebFocus might be putting extra fields in your HOLD files.