Sometimes I spend hours in WebFOCUS on something that should be incredibly simple. Anyone care for an example? I'm glad you said "yes"...

Can anyone tell my why I get a single Excel spreadsheet with two tabs -- each tab containing the output of a WebFOCUS report -- with this code:

ENGINE INT CACHE SET OFF

APP PATH METRICS_MART_PROD

-DEFAULT &START_DATE='01/01/2017';
-DEFAULT &END_DATE='01/14/2017';
-DEFAULT &CUSTOM_PATIENT_TYPE='_FOC_NULL';
-DEFAULT &PATIENT_TYPE='_FOC_NULL';
-DEFAULT &NURSING_UNIT_CODE='_FOC_NULL';

TABLE FILE ibisamp/car
HEADING
"By Car"
BY CAR
ON TABLE PCHOLD FORMAT XLSX OPEN
END
-RUN


JOIN HISTORICAL_OVERVIEW_SUMMARY.HISTORICAL_OVERVIEW_SUMMARY.PATIENT_CATEGORY_KEY IN METRICS_MART_PROD/HISTORICAL_OVERVIEW_SUMMARY
TO UNIQUE DIM_PATIENT_CUSTOM_TYPES.DIM_PATIENT_CUSTOM_TYPES.PATIENT_CUSTOM_TYPE_KEY IN METRICS_MART_PROD/DIM_PATIENT_CUSTOM_TYPES TAG J001 AS J001
END

JOIN HISTORICAL_OVERVIEW_SUMMARY.HISTORICAL_OVERVIEW_SUMMARY.NURSING_UNIT_KEY IN METRICS_MART_PROD/HISTORICAL_OVERVIEW_SUMMARY
TO UNIQUE DIM_NURSING_UNIT.DIM_NURSING_UNIT.NURSING_UNIT_KEY IN METRICS_MART_PROD/DIM_NURSING_UNIT TAG J002 AS J002
END

DEFINE FILE metrics_mart_prod/historical_overview_summary
 DAY/MDY=HDATE ( AS_OF_DATETIME , 'MDY' ) ;
 HOUR/I2=HPART ( AS_OF_DATETIME , 'HOUR' , 'I2' ) ;
 NUMERATOR_I5/I5=HISTORICAL_OVERVIEW_SUMMARY.HISTORICAL_OVERVIEW_SUMMARY.NUMERATOR ;
END
-DEFAULTH &WF_STYLE_UNITS='PIXELS';
-DEFAULTH &WF_STYLE_HEIGHT='405.0';
-DEFAULTH &WF_STYLE_WIDTH='770.0';

-DEFAULTH &WF_TITLE='WebFOCUS Report';
TABLE FILE metrics_mart_prod/historical_overview_summary
SUM NUMERATOR_I5 AS 'Patients'
BY HOUR AS 'Hour'
WHERE DAY EQ &START_DATE. (|FORMAT=MDY).START_DATE.QUOTEDSTRING;
WHERE J001.DIM_PATIENT_CUSTOM_TYPES.CUSTOM_PATIENT_TYPE EQ &CUSTOM_PATIENT_TYPE.(OR(FIND METRICS_MART_PROD/DIM_PATIENT_CUSTOM_TYPES.DIM_PATIENT_CUSTOM_TYPES.CUSTOM_PATIENT_TYPE IN METRICS_MART_PROD/DIM_PATIENT_CUSTOM_TYPES |FORMAT=A30V)).Patient Type.;
WHERE J002.DIM_NURSING_UNIT.NURSING_UNIT_CODE EQ &NURSING_UNIT_CODE.(OR(FIND METRICS_MART_PROD/DIM_NURSING_UNIT.DIM_NURSING_UNIT.NURSING_UNIT_CODE IN METRICS_MART_PROD/DIM_NURSING_UNIT |FORMAT=A255V)).Nursing Unit.;
WHERE HISTORICAL_OVERVIEW_SUMMARY.HISTORICAL_OVERVIEW_SUMMARY.METRIC_KEY EQ 1;
WHERE J001.DIM_PATIENT_CUSTOM_TYPES.PATIENT_TYPE EQ &PATIENT_TYPE.(OR(FIND METRICS_MART_PROD/DIM_PATIENT_CUSTOM_TYPES.DIM_PATIENT_CUSTOM_TYPES.PATIENT_TYPE IN METRICS_MART_PROD/DIM_PATIENT_CUSTOM_TYPES |FORMAT=A30V)).Patient Category:.;
WHERE NOT J002.DIM_NURSING_UNIT.NURSING_UNIT_CODE IN ( '3P' , '3W' , '4N' );
HEADING
"Daily Census by Hour: &START_DATE"
FOOTING
-IF &CUSTOM_PATIENT_TYPE NE '_FOC_NULL' GOTO PASS1;
"Patient Types: ALL "
-GOTO PASS11;
-PASS1
"Patient Types: &CUSTOM_PATIENT_TYPE "
-PASS11

-IF &PATIENT_TYPE NE '_FOC_NULL' GOTO PASS3;
"Patient Categories: ALL "
-GOTO PASS33;
-PASS3
"Patient Categories: &PATIENT_TYPE "
-PASS33

-IF &NURSING_UNIT_CODE NE '_FOC_NULL' GOTO PASS4;
"Nursing Units: ALL "
-GOTO PASS44;
-PASS4
"Nursing Units: &NURSING_UNIT_CODE "
-PASS44

ON TABLE PCHOLD FORMAT XLSX CLOSE
END
-RUN

-EXIT

But replacing the first CAR report with a copy of the second report yields an "Error: Request processing failed" message after hanging for a minute?

ENGINE INT CACHE SET OFF

APP PATH METRICS_MART_PROD

-DEFAULT &START_DATE='01/01/2017';
-DEFAULT &END_DATE='01/14/2017';
-DEFAULT &CUSTOM_PATIENT_TYPE='_FOC_NULL';
-DEFAULT &PATIENT_TYPE='_FOC_NULL';
-DEFAULT &NURSING_UNIT_CODE='_FOC_NULL';

JOIN HISTORICAL_OVERVIEW_SUMMARY.HISTORICAL_OVERVIEW_SUMMARY.PATIENT_CATEGORY_KEY IN METRICS_MART_PROD/HISTORICAL_OVERVIEW_SUMMARY
TO UNIQUE DIM_PATIENT_CUSTOM_TYPES.DIM_PATIENT_CUSTOM_TYPES.PATIENT_CUSTOM_TYPE_KEY IN METRICS_MART_PROD/DIM_PATIENT_CUSTOM_TYPES TAG J001 AS J001
END

JOIN HISTORICAL_OVERVIEW_SUMMARY.HISTORICAL_OVERVIEW_SUMMARY.NURSING_UNIT_KEY IN METRICS_MART_PROD/HISTORICAL_OVERVIEW_SUMMARY
TO UNIQUE DIM_NURSING_UNIT.DIM_NURSING_UNIT.NURSING_UNIT_KEY IN METRICS_MART_PROD/DIM_NURSING_UNIT TAG J002 AS J002
END

DEFINE FILE metrics_mart_prod/historical_overview_summary
 DAY/MDY=HDATE ( AS_OF_DATETIME , 'MDY' ) ;
 HOUR/I2=HPART ( AS_OF_DATETIME , 'HOUR' , 'I2' ) ;
 NUMERATOR_I5/I5=HISTORICAL_OVERVIEW_SUMMARY.HISTORICAL_OVERVIEW_SUMMARY.NUMERATOR ;
END
-DEFAULTH &WF_STYLE_UNITS='PIXELS';
-DEFAULTH &WF_STYLE_HEIGHT='405.0';
-DEFAULTH &WF_STYLE_WIDTH='770.0';

-DEFAULTH &WF_TITLE='WebFOCUS Report';
TABLE FILE metrics_mart_prod/historical_overview_summary
SUM NUMERATOR_I5 AS 'Patients'
BY HOUR AS 'Hour'
WHERE DAY EQ &START_DATE. (|FORMAT=MDY).START_DATE.QUOTEDSTRING;
WHERE J001.DIM_PATIENT_CUSTOM_TYPES.CUSTOM_PATIENT_TYPE EQ &CUSTOM_PATIENT_TYPE.(OR(FIND METRICS_MART_PROD/DIM_PATIENT_CUSTOM_TYPES.DIM_PATIENT_CUSTOM_TYPES.CUSTOM_PATIENT_TYPE IN METRICS_MART_PROD/DIM_PATIENT_CUSTOM_TYPES |FORMAT=A30V)).Patient Type.;
WHERE J002.DIM_NURSING_UNIT.NURSING_UNIT_CODE EQ &NURSING_UNIT_CODE.(OR(FIND METRICS_MART_PROD/DIM_NURSING_UNIT.DIM_NURSING_UNIT.NURSING_UNIT_CODE IN METRICS_MART_PROD/DIM_NURSING_UNIT |FORMAT=A255V)).Nursing Unit.;
WHERE HISTORICAL_OVERVIEW_SUMMARY.HISTORICAL_OVERVIEW_SUMMARY.METRIC_KEY EQ 1;
WHERE J001.DIM_PATIENT_CUSTOM_TYPES.PATIENT_TYPE EQ &PATIENT_TYPE.(OR(FIND METRICS_MART_PROD/DIM_PATIENT_CUSTOM_TYPES.DIM_PATIENT_CUSTOM_TYPES.PATIENT_TYPE IN METRICS_MART_PROD/DIM_PATIENT_CUSTOM_TYPES |FORMAT=A30V)).Patient Category:.;
WHERE NOT J002.DIM_NURSING_UNIT.NURSING_UNIT_CODE IN ( '3P' , '3W' , '4N' );
HEADING
"Daily Census by Hour: &START_DATE"
FOOTING
-IF &CUSTOM_PATIENT_TYPE NE '_FOC_NULL' GOTO PASS1;
"Patient Types: ALL "
-GOTO PASS11;
-PASS1
"Patient Types: &CUSTOM_PATIENT_TYPE "
-PASS11

-IF &PATIENT_TYPE NE '_FOC_NULL' GOTO PASS3;
"Patient Categories: ALL "
-GOTO PASS33;
-PASS3
"Patient Categories: &PATIENT_TYPE "
-PASS33

-IF &NURSING_UNIT_CODE NE '_FOC_NULL' GOTO PASS4;
"Nursing Units: ALL "
-GOTO PASS44;
-PASS4
"Nursing Units: &NURSING_UNIT_CODE "
-PASS44

ON TABLE PCHOLD FORMAT XLSX OPEN
END
-RUN

JOIN CLEAR *

JOIN HISTORICAL_OVERVIEW_SUMMARY.HISTORICAL_OVERVIEW_SUMMARY.PATIENT_CATEGORY_KEY IN METRICS_MART_PROD/HISTORICAL_OVERVIEW_SUMMARY
TO UNIQUE DIM_PATIENT_CUSTOM_TYPES.DIM_PATIENT_CUSTOM_TYPES.PATIENT_CUSTOM_TYPE_KEY IN METRICS_MART_PROD/DIM_PATIENT_CUSTOM_TYPES TAG J001 AS J001
END

JOIN HISTORICAL_OVERVIEW_SUMMARY.HISTORICAL_OVERVIEW_SUMMARY.NURSING_UNIT_KEY IN METRICS_MART_PROD/HISTORICAL_OVERVIEW_SUMMARY
TO UNIQUE DIM_NURSING_UNIT.DIM_NURSING_UNIT.NURSING_UNIT_KEY IN METRICS_MART_PROD/DIM_NURSING_UNIT TAG J002 AS J002
END

DEFINE FILE metrics_mart_prod/historical_overview_summary
 DAY/MDY=HDATE ( AS_OF_DATETIME , 'MDY' ) ;
 HOUR/I2=HPART ( AS_OF_DATETIME , 'HOUR' , 'I2' ) ;
 NUMERATOR_I5/I5=HISTORICAL_OVERVIEW_SUMMARY.HISTORICAL_OVERVIEW_SUMMARY.NUMERATOR ;
END
-DEFAULTH &WF_STYLE_UNITS='PIXELS';
-DEFAULTH &WF_STYLE_HEIGHT='405.0';
-DEFAULTH &WF_STYLE_WIDTH='770.0';

-DEFAULTH &WF_TITLE='WebFOCUS Report';
TABLE FILE metrics_mart_prod/historical_overview_summary
SUM NUMERATOR_I5 AS 'Patients'
BY HOUR AS 'Hour'
WHERE DAY EQ &START_DATE. (|FORMAT=MDY).START_DATE.QUOTEDSTRING;
WHERE J001.DIM_PATIENT_CUSTOM_TYPES.CUSTOM_PATIENT_TYPE EQ &CUSTOM_PATIENT_TYPE.(OR(FIND METRICS_MART_PROD/DIM_PATIENT_CUSTOM_TYPES.DIM_PATIENT_CUSTOM_TYPES.CUSTOM_PATIENT_TYPE IN METRICS_MART_PROD/DIM_PATIENT_CUSTOM_TYPES |FORMAT=A30V)).Patient Type.;
WHERE J002.DIM_NURSING_UNIT.NURSING_UNIT_CODE EQ &NURSING_UNIT_CODE.(OR(FIND METRICS_MART_PROD/DIM_NURSING_UNIT.DIM_NURSING_UNIT.NURSING_UNIT_CODE IN METRICS_MART_PROD/DIM_NURSING_UNIT |FORMAT=A255V)).Nursing Unit.;
WHERE HISTORICAL_OVERVIEW_SUMMARY.HISTORICAL_OVERVIEW_SUMMARY.METRIC_KEY EQ 1;
WHERE J001.DIM_PATIENT_CUSTOM_TYPES.PATIENT_TYPE EQ &PATIENT_TYPE.(OR(FIND METRICS_MART_PROD/DIM_PATIENT_CUSTOM_TYPES.DIM_PATIENT_CUSTOM_TYPES.PATIENT_TYPE IN METRICS_MART_PROD/DIM_PATIENT_CUSTOM_TYPES |FORMAT=A30V)).Patient Category:.;
WHERE NOT J002.DIM_NURSING_UNIT.NURSING_UNIT_CODE IN ( '3P' , '3W' , '4N' );
HEADING
"Daily Census by Hour: &START_DATE"
FOOTING
-IF &CUSTOM_PATIENT_TYPE NE '_FOC_NULL' GOTO PASS1;
"Patient Types: ALL "
-GOTO PASS11;
-PASS1
"Patient Types: &CUSTOM_PATIENT_TYPE "
-PASS11

-IF &PATIENT_TYPE NE '_FOC_NULL' GOTO PASS3;
"Patient Categories: ALL "
-GOTO PASS33;
-PASS3
"Patient Categories: &PATIENT_TYPE "
-PASS33

-IF &NURSING_UNIT_CODE NE '_FOC_NULL' GOTO PASS4;
"Nursing Units: ALL "
-GOTO PASS44;
-PASS4
"Nursing Units: &NURSING_UNIT_CODE "
-PASS44

ON TABLE PCHOLD FORMAT XLSX CLOSE
END
-RUN

And yet this works just fine (One Excel spreadsheet, two tabs with same report):

TABLE FILE ibisamp/car
HEADING
"By Car"
BY CAR
ON TABLE PCHOLD FORMAT XLSX OPEN
END
-RUN

TABLE FILE ibisamp/car
HEADING
"By Car"
BY CAR
ON TABLE PCHOLD FORMAT XLSX CLOSE
END
-RUN

-EXIT

Take your time answering, because I've already accepted the fact that I'm going to blow the entire day (at least) on this nonsense instead of getting anything productive done.

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

Probable cause: Duplicate dialog manager labels. The first GOTO executed in the second TABLE request will loop back to the matching label already encountered in the first one.

TABLE FILE metrics_mart_prod/historical_overview_summary

-IF &CUSTOM_PATIENT_TYPE NE '_FOC_NULL' GOTO PASS1;

-GOTO PASS11;

-PASS1

-PASS11

-IF &PATIENT_TYPE NE '_FOC_NULL' GOTO PASS3;

-GOTO PASS33;

-PASS3

-PASS33

-IF &NURSING_UNIT_CODE NE '_FOC_NULL' GOTO PASS4;

-GOTO PASS44;

-PASS4

-PASS44

END


TABLE FILE metrics_mart_prod/historical_overview_summary

-IF &CUSTOM_PATIENT_TYPE NE '_FOC_NULL' GOTO PASS1;

-GOTO PASS11;

-PASS1

-PASS11

-IF &PATIENT_TYPE NE '_FOC_NULL' GOTO PASS3;

-GOTO PASS33;

-PASS3

-PASS33

-IF &NURSING_UNIT_CODE NE '_FOC_NULL' GOTO PASS4;

-GOTO PASS44;

-PASS4

-PASS44

END

Hey Squatch,
Because you have the same exact set of amperes in both steps when you only need them in the first step and/or duplicate DM labels???

Sorry, you've already figured it out.

Thanks Tom and Jack for your quick response.

Jack, you were correct. It was duplicate dialog manager labels. It is working now after the proper adjustments.

I guess my gripe now is that I have a hard time believing that dialog manager can't flag identical labels in different places. It's not like a GOTO label should ever be at more than one location. A simple check of the code and warning would have saved me a lot of time.

And if I may add my two cents: do not put a semi-colon at the end of a -GOTO statement. One day it might fly in your face...

quote:

Originally posted by Danny-SRL:
And if I may add my two cents: do not put a semi-colon at the end of a -GOTO statement. One day it might fly in your face...

Yes, I'm finding out that excessive or incorrect punctuation in a 1970s extant mainframe programming language is the equivalent of a grenade or landmine in the real world. And duplicate GOTO labels are nuclear weapons.