Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED (with grievances)] Excel tabbed output not working

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED (with grievances)] Excel tabbed output not working
 Login/Join
 
Master
posted
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,


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
 
Posts: 594 | Location: Michigan | Registered: September 04, 2015Report This Post
Virtuoso
posted Hide Post
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



- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Expert
posted Hide Post
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???


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Expert
posted Hide Post
Sorry, you've already figured it out.


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Master
posted Hide Post
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.


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
 
Posts: 594 | Location: Michigan | Registered: September 04, 2015Report This Post
Virtuoso
posted Hide Post
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...


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Master
posted Hide Post
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.


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
 
Posts: 594 | Location: Michigan | Registered: September 04, 2015Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED (with grievances)] Excel tabbed output not working

Copyright © 1996-2020 Information Builders