Hi guys I am getting different behavior when i run a report to excel that spans multiple spreadsheets. When i do a straight data dump exceeding 65536 rows with no grouping totals the report runs fine and spreads data across worksheets using code like below.... When i include the recomputes and totals on different grouping options excel will pop up the error stating too many rows. You hit ok and it will return all the data across speadsheets titled TOTAL, Recovered_Sheet1, Recovered_Sheet2 etc. Still getting the data but would like to get rid of annyoing error. Anyone come across this and have any suggestions. Thanking you in Advance...
-* CREATES WORKSHEET INT WITH 65000 ROWS PER -*WORKSHEET FROM STORED PROC RAN ABOVE

TABLE FILE SQLOUT
ON TABLE SET PAGE-NUM OFF
PRINT *
COMPUTE COUNTER/I9 = COUNTER+1;
COMPUTE WORKSHEET/I4 = COUNTER/65000;
ON TABLE HOLD AS PHIRE

END

Then doing something like this below using dynamic grouping

TABLE FILE PHIRE
SUM
hours/P13.1C AS 'Hours' AND
gross_services
net_services
billing_adjustments
reserve_adjustments
COMPUTE REALPERC/D8.1% = net_services/gross_services * 100; AS 'Realization %'
-*&GRPBYEMP
BY WORKSHEET NOPRINT
&GROUPEU
&GRPBYOFF
-*&GRPBYOFFN
&GRPBYFUN
&GRPBYSUBFUN
&GRPBYLVL
&GRPBYSUBLVL
&GRPBY1
&GRPBY2
&GRPBYEMP
&GRPBY3
&GRPBYCLIENT
&GRPBYFOLDER
&GRPBYPL
&GRPBYPLID
&GRPBYSL
&GRPBYSLID

&EURECOMP
&OFFICERECOMP
&FUNRECOMP
&SUBFUNRECOMP
&LVLRECOMP
&SUBLVLRECOMP
&GRP1RECOMP
&GRP2RECOMP
&EMPRECOMP
&GRP3RECOMP

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

There is obviously part of your code missing, we don't know what all of these amphere variables do. If you are having subtotals and subheadings or footings they are going to take up part of your 65K rows. Is your user really going to use a 65K lines report?

I did omit the output section where &OUTPUT = EXL2K

-EXLFORMAT

ON TABLE PCHOLD FORMAT &OUTPUT BYTOC
ON TABLE SET HTMLCSS ON

The ampersands are all grouping strings built from grouping options passed from an ASP filter window. The odd part is the first sheet will randomly have between 13K & 17K rows with the headers and footers and subtotals. The remaining sheets will have the max of 65536 rows. And yes our users do like large volumes of dataI work for an accounting firm and the bean counters want to see the data down to the details.

Cut your 65000 constant in half.

Thanks for your response Jack, the annoying error message is now gone. Excel still names the worksheets TOTAL, Recovered_Sheet1, etc. Myself I could live with this, our QA dept will ask why ? Any ideas why it doesnt use 0,1,2 ie the number derived from my divisor the way it does on a straight data dump without formatting ? Also how did you come up with half for the divisor?

quote:

how did you come up with half for the divisor?

Arbitrarily -- so you could inspect output not plaugued by 65k overflow. (I figured heading lines : data rows ratio is not likely to exceed 1:1). Take it from there.

Your totals will still come out as TOTALS that is a function of the BYTOC. Also, the way you currently have it your worksheets will begin with zero. You may need to play with this syntax but in your stylesheet under TYPE=REPORT add
TITLETEXT="

Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes