I didn't run into any forum-topic mentioning this, but it would appear that the page numbering in excel output is off by one. For some reason the first page is numbered 'Page 2/n'!

For a simple example:

APP APPENDPATH IBISAMP
-RUN

DEFINE FILE CAR
	DUMMY/A1 = ' ';
END
TABLE FILE CAR
SUM
	DUMMY NOPRINT
	CAR
	RETAIL_COST
	DEALER_COST
COMPUTE DIFFERENCE/D12.2 = RETAIL_COST - DEALER_COST;
BY DUMMY
BY CAR
BY COUNTRY

ON CAR PAGE-BREAK
ON CAR RECOMPUTE AS 'Subtotal'

ON DUMMY SUMMARIZE
	RETAIL_COST
	DEALER_COST
	DIFFERENCE AS 'Grand Total'

ON COUNTRY RECOMPUTE AS 'Subtotal'

ON TABLE PAGE-BREAK AND SUBHEAD
"Sales"
"<COUNTRY"
HEADING
"Cars"
FOOTING
"Page <TABPAGENO / <TABLASTPAGE"
ON TABLE SET PAGE-NUM ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K FORMULA
ON TABLE SET STYLE *
$
TYPE=TABHEADING, BACKCOLOR='CYAN',$
TYPE=HEADING,BACKCOLOR='YELLOW',$
TYPE=FOOTING,BACKCOLOR='ORANGE', SIZE=8, JUSTIFY=RIGHT,$
ENDSTYLE
END

In our actual report - which uses EXL2K FORMULA - I was running into an additional issue where cell references (for example, '$H279') weren't matching the rows that they were on, causing calculation errors. For example, the row where that reference could be found was row 281, and not row 279! I hoped to reproduce that behaviour with above example (hence the slightly more complicated than necessary example), but alas, it behaves well in that respect...

Has anyone seen this?

I read a lot of issues about the formula format, but up to this point I've managed to put a fairly complex report on it without running into too many issues. It even contains some percentages (which apparently didn't work for some people)!

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

quote:

ON TABLE PAGE-BREAK AND SUBHEAD

That's the reason for it seemingly starting on page 2.
If you do a HOLD FORMAT PDF in stead of excel or html, you will see that the first page only has the SALES banner and nothing else on it. The second page starts off with the first record. So the pagination is very much ok. If you remove the 'PAGE-BREAK AND' from the above statement, the page numbers will also be visually correct in Excel.

Aha! Thanks.

I don't suppose you have any suggestions where to start looking for the other issue I was having - namely cells referring to other cells a few rows above them instead of the same row? They sometimes refer to cells in titles and such, so it's clearly not proper behaviour :/
That issue seems page-break related, as on the first page it's alright, but further into the report this starts occurring more often and in worse ways the further you get.

Some things to look into in trying to reproduce the problem with the sample data would be a start at least.

No not really. But what you state about how it gets worse the further you get in the report, I would start by either eliminating page-break, or if that's not an option limiting the header/footer and column titles to just one line max. And if that helps, then your hunch was correct, but it is of course not a solution. But by fooling around with these things, you might arrive at an acceptable workaround, whilst reporting this to IB of course.

I caught the problem! Apparently applying borders causes it.

This code shows the problem:

APP APPENDPATH IBISAMP
-RUN

TABLE FILE CAR
SUM
	RETAIL_COST
	DEALER_COST
COMPUTE DIFFERENCE/D12.2 = RETAIL_COST - DEALER_COST;

BY CAR
BY COUNTRY

ON CAR RECOMPUTE
	RETAIL_COST
	DEALER_COST
	DIFFERENCE	AS 'Grand Total'

ON CAR PAGE-BREAK
ON COUNTRY RECOMPUTE AS 'Subtotal Car'

ON TABLE PCHOLD FORMAT EXL2K FORMULA
ON TABLE SET STYLE *
     UNITS=CM,
     PAGESIZE='A4',
     ORIENTATION=LANDSCAPE,
$
TYPE=REPORT,
	 BORDER=LIGHT,
$
TYPE=TITLE, BACKCOLOR=RGB(200 200 200),$
TYPE=SUBTOTAL, BACKCOLOR=RGB(255 255 185),$
ENDSTYLE
END

If you comment the BORDER=LIGHT out, the sheet works as expected. With it, the cell references gets their row-numbers shifted by one per page break.

I'll report this to IBI, as it clearly seems to be a bug (they're probably getting a bit tired of me by now :P).