SQL Reporting 2008 disallows me to put the PageNumber and TotalPages (global) variables on the data level; only allows them in the page header & footer. But their values go hand-in-hand with data. So is there any way of knowing the page number on a non-header level?
A:
Page and TotalPages and even RecordCount is a relative concept for a DB.
The page a row is on depends on sorting order, timing (someone might insert data, thus pushing all records down on or more rows) and modification (soemone might update a field of your sorting key).
So I wouldn't say that the values "PageNumber" and "TotalPages" are associated with the data, it's associated with the query.
Paging up and down the data is trivial and deterministic only if your query can take some kind of snaphot based on your key. Where that snapshot isn't possible, since it is too expensive to calculate or in too big to fit into memory, then the meaning of "PageNumber" associated with an item begins tu blur.
My guess is, that this is the reason reporting-services takes the approach it does, seemingly row by row, until the page is full, then update the headers..
lexu
2009-10-27 15:01:10
So you are saying I should return the page count and page number in the query, using math? It is doable in Crystal Reports; CR does not distinguish between header row and data the way SQL does.
JonathanWolfson
2009-10-27 15:03:02
What I am saying, is that it never precise and there are several approximations how to do it, depending on DB engine and reporting tool. Sorry .. no silver bullet.
lexu
2009-10-27 15:13:59
A:
The converse is true; i.e. A report header can suck values from data even if not vice versa. Thus I add a page header that simply extracts values from hidden data cells. The pagination portion works normally, using Globals.PageNumber and .TotalPages, and the data-dependent dynamic portion of the same header line of the page number is =ReportItems!DynamicTitle.Value.
JonathanWolfson
2009-10-27 16:25:02