Focal Point
[solved]two excel sheet in one workbook

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/7457072516

January 10, 2012, 01:38 PM
arsagg
[solved]two excel sheet in one workbook
Hi All,

I am generating two reports in two different loops. Now I want them to get produced in one work book. The code I am using is:

TABLE FILE A01
PRINT
ISSUE.ST.CD
CNTRC.ST.CD
BY BASE.POL.NUM
ON TABLE PCHOLD AS HOLD1 FORMAT EXL2K OPEN
END
-*
TABLE FILE B01
PRINT
ISSUE.ST.CD
CNTRC.ST.CD
BY BASE.POL.NUM
ON TABLE PCHOLD AS HOLD2 FORMAT EXL2K CLOSE
END

This isn't working. On downloading HOLD2, it gives me the output of the secong loop only. Any help on this?

Thanks.

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


FOCUS 7.6.11
Windows, Excel, LOTUS, ALPHA
January 10, 2012, 02:09 PM
Francis Mariani
Specify only one HOLD file name:

TABLE FILE CAR
SUM
DEALER_COST
BY COUNTRY

HEADING
"Dealer"

ON TABLE PCHOLD AS HOLD2 FORMAT EXL2K OPEN

ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='Dealer', $
TYPE=HEADING, STYLE=BOLD, COLOR=GREEN, $
TYPE=TITLE, STYLE=BOLD, $
ENDSTYLE
END

TABLE FILE CAR
SUM
RETAIL_COST
BY COUNTRY

HEADING
"Retail"

ON TABLE PCHOLD FORMAT EXL2K CLOSE

ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='Retail', $
TYPE=HEADING, STYLE=BOLD, COLOR=BLUE, $
TYPE=TITLE, STYLE=BOLD, $
ENDSTYLE
END



Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
January 10, 2012, 02:19 PM
Mary Watermann
Not sure what you mean by the following:

quote:
On downloading HOLD2, it gives me the output of the secong loop only.


I copied your code above using the CAR file and was able to get two (2) tabs in Excel from each table.


WF 7.6.10, Windows, PDF, Excel
January 11, 2012, 03:53 AM
arsagg
Francis, the code given by you is for HTML i guess. I need the output in Excel.

Mary, Downloading here means copy the host output file from mainframe to local machine.

I am still not getting two tabs in one excel workbook. Running out of ideas on where the code is going wrong.

Thanks.


FOCUS 7.6.11
Windows, Excel, LOTUS, ALPHA
January 11, 2012, 08:07 AM
Tom Flynn
TABLE FILE A01 
PRINT 
ISSUE.ST.CD 
CNTRC.ST.CD 
BY BASE.POL.NUM 
ON TABLE PCHOLD FORMAT EXL2K OPEN 
END 
-RUN
TABLE FILE B01 
PRINT 
ISSUE.ST.CD 
CNTRC.ST.CD 
BY BASE.POL.NUM 
ON TABLE PCHOLD FORMAT EXL2K CLOSE 
END


The manual has examples of Compound Reports, as well as, Searching "Compound" on this forum...


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
January 11, 2012, 10:19 PM
arsagg
Thanks to all for the replies.

I am still looking for a fix. I did searched Compound in the forum, it didn't help me.

All I need is to attach a desclaimer to the excel report which I am generating in Focus. Nothing seeems to be working for me.


FOCUS 7.6.11
Windows, Excel, LOTUS, ALPHA
January 12, 2012, 11:56 AM
Emily Max
When there is a error on one report in a multi-tab Excel report, that report/tab will not render while the working tabs will appear. Try commenting out the working tab to see if the one that is not coming back works. If it doesn't, then you will at least get an error message to know where to work from.


WF 8.1.05 on Windows machines
Backend: Informix, SQL and Oracle databases
January 14, 2012, 11:51 PM
arsagg
I found my mistake. I was using different hold names in the two loops which never created the two sheets in one spreadsheet.

Thanks all for your replies and support. Smiler

Regards.


FOCUS 7.6.11
Windows, Excel, LOTUS, ALPHA
January 16, 2012, 12:32 PM
arsagg
Dear all,

I did manage to get two excel sheets in one workbook with your support, however this idea is costly to implement as I have some 50 focus codes which requires this change and consequently their JCL parms will also require the changes as earlier the output format was BIN which is now ASCII.

My purpose is to add a text in the report (Excel format) either at the top or at the bottom which can be achieved in FOCUS. My condition is that the report format shall be excel - bin.

Please put your thoughts on this otherwise my project will suffer heavy cost.

Thanks


FOCUS 7.6.11
Windows, Excel, LOTUS, ALPHA