Hi,

I have written a procedure, in which my output should be 2 excel sheets. But when i am running the procedure i am getting only the last report. What could be the reason and could any one suggest the solution for getting two excel output reports.

This message has been edited. Last edited by: Vinay Kumar,

try this

*
-* example of how to use the EXL2K OPEN/CLOSE option in combination with BYTOC and the TITLETEXT
-*
-*[CAR:SUMMARY]
-*------------------------------->
TABLE FILE CAR
SUM CNT.CAR
BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K OPEN
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
     TITLETEXT='Summary',
$
END
-RUN
-*------------------------------->
-*
-*
-*[CAR:PER COUNTRY
-*------------------------------->
TABLE FILE CAR

PRINT CAR
BY COUNTRY

ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K CLOSE
ON TABLE SET COMPOUND 'BYTOC 1'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
END
-*--------------------------

Hi Frank,

The solution which you have provided would create 2 reports in one excel(different work books) sheet.

My requirement is to create two different excel sheets.

true

try this

-* File exl2K_OUTPUT_TITLE_BYTOC.fex
-*
-* example of how to use the EXL2K OPEN/CLOSE option in combination with BYTOC and the TITLETEXT
-*
-*[CAR:SUMMARY]
-*------------------------------->
FILEDEF K0404FA1  DISK D:\ibi\apps\inboxftp\summary.xls
-*
TABLE FILE CAR
SUM CNT.CAR
BY COUNTRY
ON TABLE HOLD AS K0404FA1 FORMAT EXL2K 
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
     TITLETEXT='Summary',
$
END
-RUN
-*------------------------------->
-*
-*
FILEDEF K0404FA2  DISK D:\ibi\apps\inboxftp\details.xls
-*[CAR:PER COUNTRY
-*------------------------------->
TABLE FILE CAR

PRINT CAR
BY COUNTRY

ON TABLE NOTOTAL
ON TABLE HOLD AS K0404FA2 FORMAT EXL2K 
ON TABLE SET COMPOUND 'BYTOC 1'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
END
-*------------------------------->

I added two filedefs
they both point to a writable directory on the server where the files now will be available

Frank,

I am getting the below error.

(FOC350) ERROR WRITING OUTPUT FILE: K0404FA1

Try this:

SET PAGE-NUM=OFF
-RUN

SET COMPOUND=OPEN
-RUN

TABLE FILE CAR
SUM SALES
BY COUNTRY
HEADING
"Sales Report"
" "

ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='Sales', $
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K
END
-RUN

SET COMPOUND=CLOSE
-RUN

TABLE FILE CAR
SUM DC
BY COUNTRY
HEADING
"Cost Report"
" "

ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='Cost', $
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K
END

Does your FILEDEF point to an excisting directory?

Francis

Your solution does the same as I suggested in my first answer.
He wants 2 separated spreadsheets.

Thanks Frank! Your code worked! I gave my filedef path wrong!

Thanks Francis for your reply!

Does FILEDEF won't work on mainframe focus?

If i have to write the same logic in mainframe focus. How could it be using FILEDEF?

Frank, sorry, I misunderstood! Yours is the correct answer. Vinay's code may not be pointing to a directory that the reporting server can write to.

I don't think you need the COMPOUND statement, this works, using the FILEDEFs to APP folders:

-* File multi_excel1.fex

FILEDEF REPORT1 DISK demo/report1.xls
-RUN

TABLE FILE CAR
SUM CNT.CAR
BY COUNTRY
ON TABLE HOLD AS REPORT1 FORMAT EXL2K
ON TABLE SET STYLE *
TITLETEXT='Summary', $
END
-RUN

FILEDEF REPORT2 DISK demo/report2.xls
-RUN

TABLE FILE CAR
PRINT CAR
BY COUNTRY
ON TABLE HOLD AS REPORT2 FORMAT EXL2K
ON TABLE SET STYLE *
TITLETEXT='Detail', $
END
-RUN

Ya Francis! I corrected it!

Could you please help me in writting the same logic in mainframe focus?

When i am using FILEDEF in mainframe focus, i am getting below error.

UNKNOWN FOCUS COMMAND FILEDEF

Use DYNAM ALLOC for the mainframe, or use FILEDEF as my previous example - if the output location is an App folder.

Francis, does FILEDEF works in mainframe focus?

I am getting an error when i am using FILEDEF!

Use DYNAM ALLOC on the mainframe. I don't know how you can create an Excel file on the mainframe though, so the technique discussed in this thread may not be possible...

I have worked using DYNAM ALLOC. I was just wondering on how to use FILEDEF in mainframes!

Thanks for the reply Francis!

Vinay, could you please tell me how you managed to create an Excel spreadsheet on the mainframe? The Excel spreadsheet is saved in a file that is DYNAM ALLOCated, but how do you access this spreadsheet? Via FTP?

Francis,

The focus query would be the same as we write in webfocus, but the output (format excel) should be holded in a sequential file.

After writting writting the query, we would need to write a mail step in JCL using a mail PROC. There would be two sysin's, one for TO mail id and other for attachments. The attachments would be output sequential files.

When you submit the job, you would be getting an email in the format excel.

Let me know if you need more info!