Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] how to build a FOCUS database by appending report results
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] how to build a FOCUS database by appending report results
 Login/Join
 
Member
posted
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,
Jackie

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 26 | Location: Oklahoma City | Registered: January 27, 2012Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.

https://webfocusinfocenter.inf...ng/source/use108.htm

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


WebFOCUS 8206, Unix, Windows
 
Posts: 1673 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Member
posted Hide Post
Hello,

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
 
Posts: 26 | Location: Oklahoma City | Registered: January 27, 2012Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1673 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Member
posted Hide Post
Thank you. I will check out the link and recheck my data structures.

Jackie O.


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 26 | Location: Oklahoma City | Registered: January 27, 2012Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 26 | Location: Oklahoma City | Registered: January 27, 2012Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1673 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 26 | Location: Oklahoma City | Registered: January 27, 2012Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1673 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.


WebFocus 8.201M, Windows, App Studio
 
Posts: 221 | Location: Lincoln Nebraska | Registered: August 12, 2008Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] how to build a FOCUS database by appending report results

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.