I am trying to run a report on WebFocus 5.3 but it never completes successfully. The code is here. Not sure I can attach the access, master and other styling forms here but if you like to see it I will try to attach it. The first report L03 runs fine. It's the second rpeort that is causing problems. Thank you!
-* ---------------------------------------------
-* Program : dcs_l03_l039.fex
-* Description : Runs reports L-03 and L-039
-* Developed by: CH
-* Date : May 2006
-* ---------------------------------------------
-SET &ECHO=ALL;
SET NODATA = ' '
SET SUMMARYLINES = NEW
SET HOLDLIST = PRINTONLY
-RUN
-DEFAULT &REPORT_SELECT = 'L03'
-DEFAULT &WFFMT = 'PDF'
-SET &RUN_DATE = &DATEMtrDYY;
-SET &RUN_TIME = EDIT(HHMMSS('A8'),'99$:99$:99');
-SET &REPORT_ID = &REPORT_SELECT;
-SET &HEAD_TEXT1 = 'Licensed Driver Population As At ';
-SET &HEAD_TEXT2 = IF &REPORT_SELECT EQ 'L03' THEN 'Condition by Gender' ELSE
- IF &REPORT_SELECT EQ 'L039' THEN 'Specific Conditions Shown on Licence' ELSE ' ';
-INCLUDE DRSI001
-IF &REPORT_SELECT EQ 'L039' GOTO L039_START;
-* -------------------------------------------
-* report
-* -------------------------------------------
DEFINE FILE DRS_L03_MEDIA
CONTROL_YEAR/I4 = &CONTROL_YEAR;
CONTROL_MD/I4 = 1231;
CONTROL_YMD/I8YYMD = EDIT(EDIT(CONTROL_YEAR) || EDIT(CONTROL_MD));
ASAT_DATE/MtDYY = CONTROL_YMD;
CODE_1/I2 = IF CODE9 EQ 'N' THEN 1 ELSE 2;
CODE/A26 = IF CODE9 EQ 'Y' THEN 'Conditions With Code 9' ELSE 'Conditions Without Code 9';
CONDITION/A6 = IF CODE9 EQ 'N' AND GLASSES EQ 'N' AND HAND_CONTROL EQ 'N' AND ZENDORSEMENT EQ 'N' THEN 'None' ELSE
IF CODE9 EQ 'N' AND HAND_CONTROL EQ 'Y' AND ZENDORSEMENT EQ 'N' AND GLASSES EQ 'N' THEN '2' ELSE
IF CODE9 EQ 'N' AND GLASSES EQ 'Y' AND ZENDORSEMENT EQ 'N' AND HAND_CONTROL EQ 'N' THEN 'X' ELSE
IF CODE9 EQ 'N' AND GLASSES EQ 'Y' AND HAND_CONTROL EQ 'Y' AND ZENDORSEMENT EQ 'N' THEN '2,X' ELSE
IF CODE9 EQ 'N' AND ZENDORSEMENT EQ 'Y' AND HAND_CONTROL EQ 'N' AND GLASSES EQ 'N' THEN 'Z' ELSE
IF CODE9 EQ 'N' AND HAND_CONTROL EQ 'Y' AND ZENDORSEMENT EQ 'Y' AND GLASSES EQ 'N' THEN '2,Z' ELSE
IF CODE9 EQ 'N' AND GLASSES EQ 'Y' AND ZENDORSEMENT EQ 'Y' AND HAND_CONTROL EQ 'N' THEN 'X,Z' ELSE
IF CODE9 EQ 'N' AND GLASSES EQ 'Y' AND ZENDORSEMENT EQ 'Y' AND HAND_CONTROL EQ 'Y' THEN '2,X,Z' ELSE
-* WITH CODE 9
IF CODE9 EQ 'Y' AND GLASSES EQ 'Y' AND ZENDORSEMENT EQ 'Y' AND HAND_CONTROL EQ 'N' THEN '9,X,Z' ELSE
IF CODE9 EQ 'Y' AND GLASSES EQ 'Y' AND ZENDORSEMENT EQ 'N' AND HAND_CONTROL EQ 'N' THEN '9,X' ELSE
IF CODE9 EQ 'Y' AND GLASSES EQ 'N' AND ZENDORSEMENT EQ 'Y' AND HAND_CONTROL EQ 'N' THEN '9,Z' ELSE
IF CODE9 EQ 'Y' AND GLASSES EQ 'N' AND ZENDORSEMENT EQ 'N' AND HAND_CONTROL EQ 'N' THEN '9' ELSE 'Others';
SEX/A6 = IF GENDER EQ 'M' THEN 'Male' ELSE 'Female';
END
TABLE FILE DRS_L03_MEDIA
SUM
COMPUTE GRAND_GENDER/I9C = COUNT_NUM; NOPRINT
SUM
COUNT_NUM/I9CS AS ''
COMPUTE NUM_PERCENT/D6.2S% = (COUNT_NUM / GRAND_GENDER) * 100; AS '%'
ROW-TOTAL AS 'Total'
BY CODE_1 NOPRINT
BY CODE NOPRINT SUBTOTAL AS ''
BY CONDITION
ACROSS SEX AS '' COLUMNS 'Male' AND 'Female'
-* -------------------------------------------
-* heading
-* -------------------------------------------
HEADING
" - Driver Licensing Statistics"
"&HEAD_TEXT1 "&HEAD_TEXT2"
"Data at: &DATA_DATE"
" "
-* -------------------------------------------
-* footing
-* -------------------------------------------
FOOTING
"Report ID: DCS-&REPORT_ID / Run Date: &RUN_DATE / Time: &RUN_TIME <+34 Page:ON TABLE SUBFOOT
"LEGEND:"
" X - Driver must wear corective lenses 2- Vehicle must be equipped with hand controls "
" Z - Air brakes endorsement 9 - Condition specified on licence as Category A, B, C, etc."
" "
"NOTE: Reports 'active driver population' counts and percentage of total, listing each combination by gender. <0X
There are no drivers under 16 years of age."
WHERE AGE GT 15;
ON TABLE SET ONLINE-FMT '&WFFMT'
ON TABLE SET CSSURL '/approot/mtomis/report.css'
ON TABLE SET STYLE *
-GOTO STY_&WFFMT
-GOTO STY_PDF
-*
-STY_HTML
-STY_EXL2K
-MRNOEDIT -INCLUDE STYLE01
-GOTO STY_END
-*
-STY_PDF
-MRNOEDIT -INCLUDE DCSSTYLE02
-*
-STY_EXCEL
-STY_END
ENDSTYLE
-END_CODE
END
-GOTO END_DCS_L03
-* ---------------------------------------------
-* REPORT L-039 - USES DIFFERENT TABLES TO REPORT
-* ---------------------------------------------
-L039_START
JOIN DIN_NO IN BASEINFO TO DIN_NO IN CODE9CONDITION AS J0
JOIN CODE9_SUBCODE IN BASEINFO TO CODE9_SUBCODE IN XREFCODE9 AS J1
DEFINE FILE BASEINFO
CONTROL_YEAR/I4 = &CONTROL_YEAR;
CONTROL_MD/I4 = 1231;
CONTROL_YMD/I8YYMD = EDIT(EDIT(CONTROL_YEAR) || EDIT(CONTROL_MD));
ASAT_DATE/MtDYY = CONTROL_YMD;
SEX/A6 = IF GENDER EQ 'M' THEN 'Male' ELSE 'Female';
END
TABLE FILE BASEINFO
-* -------------------------------------------
-* footing
-* -------------------------------------------
SUM
CNT.GENDER/I7CS
BY
CODE9_SUBCODE AS 'Subcode'
BY
CODE9_SUBDESCENG AS 'Description'
ACROSS SEX AS ''
HEADING
"- Driver Control Statistics"
"&HEAD_TEXT1 "&HEAD_TEXT2"
"Data at: &DATA_DATE"
" "
FOOTING
"Report ID: DCS-&REPORT_ID / Run Date: &RUN_DATE / Time: &RUN_TIME <+34 Page:ON TABLE SUBFOOT
" "
"NOTE: Reports counts of licensed drivers for the various Code 9 conditions."
-*WHERE READLIMIT EQ 100
-*WHERE RECORDLIMIT EQ 100
WHERE ACTIVE_DRIVER EQ 'A';
WHERE LIC_CLASS_CODE NE MISSING;
WHERE AGE GT 15;
WHERE (HAND_CONTROL EQ 'Y' OR GLASSES EQ 'Y' OR ZENDORSEMENT EQ 'Y' );
ON TABLE ROW-TOTAL AS 'Total'
ON TABLE COLUMN-TOTAL
ON TABLE SET ONLINE-FMT PDF
ON TABLE SET CSSURL '/approot/mtomis/report.css'
ON TABLE SET STYLE *
-GOTO STY_&WFFMT
-GOTO STY_PDF
-*
-STY_HTML
-STY_EXL2K
-MRNOEDIT -INCLUDE STYLE01
-GOTO STY_END
-*
-STY_PDF
-MRNOEDIT -INCLUDE DCSSTYLE02
-*
-STY_EXCEL
-STY_END
TYPE=REPORT,
COLUMN=N2,
WRAP=6.105556,
$
ENDSTYLE
END
-LO39_END
-END_DCS_L03

WF v5.3.2
Platform: AIX, Win XP, IE 6.0 SP2
Output Formats: Html, Pdf, Excel

Adrian,

If I remember correctly, 5.3 had issues with
-SET &ECHO=ALL;

Try commenting it out...

Hello Ministry of Transport!

The first thing I would do is SET XRETRIEVAL=OFF to turn data retrieval off. Then I would add SQL tracing to determine if there are any issues with the JOINs and to see what SQL is generated.

Stick the following code in a -INCLUDE so you can use it for every report you create, then include it at the beginning of your program:

-*-- Set up SQL tracing --------------------------------------------------------

-*-- Deactivate SQL tracing --------------------------------
SET TRACEOFF = ALL

-*-- Show Commands and data exchange between the -----------
-*-- physical and the logical layers of the data adapter
-*SET TRACEON = SQLCALL

-*-- Enable Trace for the SQL Translator -------------------
SET TRACEON = SQLTRANS

-*-- Show SQL statements -----------------------------------
SET TRACEON = STMTRACE//CLIENT

-*-- Show Optimization information -------------------------
SET TRACEON = SQLAGGR//CLIENT

-*-- Show SQL generated statement trace --------------------
-*SET TRACEON = STMTRACE/1/CLIENT

-*-- Show SQL generated sub-statement trace ----------------
-*SET TRACEON = STMTRACE/2/CLIENT

-*-- Disable the trace stamp (Date/Time etc) ---------------
SET TRACESTAMP = OFF

-*-- Set trace line wrapping - # of characters -------------
SET TRACEWRAP = 78

-*-- Activate SQL tracing ----------------------------------
SET TRACEUSER = ON
-RUN

It appears you are selecting all drivers over a certain age and doing most of your summing on defined fields. How many records in your data source. The sorting on defines of large data sources can get long running and never ending, especially if your join is not set up well or your selection doesn't limit well. I'm assuming if you put a recordlimit in the selection it creates a report??

Without going too much further, I noticed one thing that I've found is a no-no - especially when you're doing a lot of branching. You have the label of -STY_HTML, -STY_PDF, -STY_EXL2k, -STY_EXCEL, and -STY_END duplicated in the same fex. This caused me to tear out a few strands of hair on more than one occasion, because it wasn't consistent on how the code ran - sometimes it branched to one and sometimes to another, and frequently ended up doing a continuous loop (which sounds like it may be the problem). I always put a suffix of _1 , _2 or some other identifier so as to NEVER duplicate labels.
Also noticed that you have two -GOTOs in a row (second one will never be executed)?
There are a couple of _INCLUDEs that we can't see so hard to tell on that point.
I've also found Leah's comment to be completely valid when doing sorts and especially selections on DEFINEd fields for large data sets.
I would still leave the -SET &ECHO=ALL; in there (don't know about Tom's comment - I used this forever in 5.3 without issues) and then try running the report, waiting for a moment and then killing the job from the web console. It should still return some code to you in the browser window and you would be able to tell if it's in a loop or if it's some other inefficiency.
the XRETRIEVAL=OFF would also give you a big clue - if it finishes, it's the quantity of data and the DEFINEs/WHERE/BY. If it still doesn't finish, it's in a loop and I would check your labels.

Simply put, turning on SQL traces which provide helpful messages on the validity of the join structure and also show you the generated SQL is the best way to solve a "report never seems to stop running" issue.

I have reduced the elapsed time of reports that took 15 minutes to 15 seconds by tracing the SQL and understanding the joins. Key fields, indexes, aggregation must be considered when working with relational databases.

I do agree with Francis that you should action SQL tracing if your data sources are SQL based. I would also suggest trying to use SQL passtrhu instead - if your structure is too complex to enable WF to parse it. As Leah says, you are summing quite a few define fields, and these will not necessarily be passed in the SQL, so the previous statement becomes very important.

However, having said that, I think that you will find the Darin has highlighted your problem, and that is the usage of duplicate labels within a single fex. You second -GOTO STY_&WFFMT will effectively set up a loop by going to the first label in the fex and not the one you think it will go to. Change your labels and I think you will have success.

Then go back and look at determining the efficiencies of your code.

Good luck

T

No, the report still keeps running forever even if I use 50 records. I managed to fix it by creating the report again in the GUI with minor edits and this time I could get both Column and Row totals. However, it is still running forever when I incorporate our common stylesheets in there but I cannot see a problem with the stylesheet. I will try the other suggestions by the members on this site. Thanks.

quote:

Originally posted by Leah:
It appears you are selecting all drivers over a certain age and doing most of your summing on defined fields. How many records in your data source. The sorting on defines of large data sources can get long running and never ending, especially if your join is not set up well or your selection doesn't limit well. I'm assuming if you put a recordlimit in the selection it creates a report??

quote:

However, it is still running forever when I incorporate our common stylesheets in there but I cannot see a problem with the stylesheet. I will try the other suggestions by the members on this site. Thanks.

Are you embedding the style sheet? That's the only way I've ever used them, so others may have comments as to that. Though why a style sheet should cause issues is a puzzle.

Adrian, the absolute first thing to do is to check the SQL generated by the JOIN, DEFINE and TABLE statements.

Adrian,

Again, I say that you need to eradicate the duplicate labels in your code (note that they are in your stylesheet!!!). If these labels are in your included stylesheet then you need to revisit that to remove labels that will potentially cause this when ever you have multiple table requests with stylesheets in a single fex.

T

Ditto on Tony's comment. Also noticed that there is no closing " on the end of your footing line. This one gets me all the time.

Double ditto!!

The double quote is there in the original code. It was just accidentally left out when I copied and pasted the code into this message. I have since commented out some of the labels that might be the offenders and got the report to work for 10 records. I immediately tried a larger sample of 50 and I think there was a problem with our Web servers or some environment problem so I haven't seen it work again. Will try once I figure out the environment problems. Trouble is I develop it on the remote server and don't have my own local server. Thanks.

I actually got in the habit of using &variables in my label names so if I -INCLUDE a piece of code that has labels in it more than once, it doesn't cause this problem. Before the -INCLUDE I just increment the &CT variable which is appended onto the label names.

Adrian, did you see my private message to you?