KB-Reporting Service Paging By Group

Compared to those 3rd party reporting tools (ex: Crystal Report), SQL Reporting Service, which is bundled with MS SQL Server,  is almost free and tightly integrated with VS.NET and IIS.  It seems the **BEST** option for ASP.NET developers. (Once upon a time, I doubted how Crystal Report can survive?)

After using Reporting Service(RS) at some real, complex, but 100% practical reports, I begin to find features that RS can't support and know why Crystal Report can survive.  The most serious problem is -- I can't show page number in group headers.(How serious? It made me want to give up RS and began to survey alternative reporting tools.)

For example, I want to generate credit card bill for 1,000 client, Of course I design a report, using "group by ClientId" to combine several clients' bill in single report.  When the client gets his n-pages bill, he surely wants to see the "Page m of n"  on the bill header.  It's really a common and must-to-have requirement, but I found RS just can't do it!!

You can use Globals!PageNumber and Globals!TotalPages on page header zone to get "Page m of n".  But these global properties can't be used outside page header, many smart guys had tried their best, including "Me.Report.Globals!PageNumber" method (failed, it always gets 1/1), resetting page number method (here, here) (it can't get total pages)

Why it's so hard to do so simple work?  After some research, I think there is no way to get total pages inside groups!  Here's my experiment:

I put three TextBox to trigger log writting and alocate them in page header, group header and list detail area.

Code.WriteLog() will append some information to a log file.

Here's the log:

18:18:39 Group Header
18:18:39 Detail 2301
18:18:39 Detail 2302
... Ignored ...
18:20:28 Detail 2398
18:20:28 Detail 2399
18:20:28 Page Header
18:20:28 Page Header

In the log file, I found the truth!!  The group header is rendered only at the first page of group, then details are rendered row by row.  Only after all group headers, detail rows are completely rendered, RS begins to assembly group headers, detail rows, calculating content height, split pages and finally gets the number of total pages.  When it knows TotalPages, group headers and detail rows are already rendered and can't be modified.  So there is no way to know TotalPages inside group header or detail row, unless RS change the way of rendering. (The order of rendering also explained why Me.Report.Globals method always return "1 / 1".) To get total pages inside group, maybe a "preview rendering" is necessary to calculate the total pages, then the "real rendering".  But if the height of group header changes (maybe depends on TotalPages) in the "real rendering", then the TotalPages need to be recalculated... (Recursive? orz)

Really hard, right?  Maybe it's tough for Microsoft Reporting Service team, too.  So this paging by group is not included.

I am still trying to find a solution.  The only idea I have is to make the report only for a client, then the PageNumber and TotalPages works fine in page header zone.  What if I want to print 1,000 clients' bill?  I wrote a ASPX to generate a list of 1,000 report link, use javascript to showModalDialog 1,000 times.  In the modal dialog, I use RSClientPrint OCX to print the report.

The only thing we have to worry is RSClientPrint.Print() will popup a printer selection dialog, need user to press "OK" button to print out.  The user must sit there, be waiting, and click 1000 times?  In IEUnit, there is a powerful Win32Dom.Desktoop class, it can find window in your desktop, get the button on it and click it.  Reference the source code, you can write a "OK-button robot", then everything goes fine! 

Maybe this solution is a little ugly, but it works.  At least, I saved more than 5,000 USD to purchase another reporting tool, but I hope Microsoft can put this feature in the wishlist of reporting service, that's the real way to solve this problem.