This is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.
The question in more detail is whether it is possible to print “Page: Y/X” or “Page: Y of X” on a Report Writer report, where Y is the current page number and X is the total number of pages for the report.
This question was asked again on the Partner Forum recently. It is one that has been asked on numerous occasions in the past and so I thought I would explain why the answer is “No …. well …. maybe”.
The Dynamics Report Writer is a single pass report writer. This means it runs through the data once and prints section of each page as it is completed until it reaches the end. As it cannot tell in advance how much data is being printed and what additional headers and footers will be printed, it will not know the total number of pages until it finishes printing the last page.
So if we are talking about only using the Report Writer, then including the total number of pages is not possible.
OK, why did I say “maybe”?
If you really want this feature and are registered for Visual Basic for Applications (VBA), I believe it would be possible to estimate the number of pages. This is just a concept and to date I have not actually written the code. You will need to decide if the amount of work required is worth the benefit of having the total number of pages.
You could use the Report_Start() event to read the legend values to get the range of records the report will be printing, then you can create a connection to SQL Server via ADO (ActiveX Data Objects) and identify the amount of data. For example: Identify a SOP transaction, read the number of lines and comments for those lines and lot and serial numbers for those lines.
Once you know how much data there is and how many lines can be printed on each page, you can calculate the estimated total number of pages. Using the Report_BeforePH() event you can increment the number of the current page and then set a blank string calculated field to the value of “Page Y of X”.
There are some other aspects that should be looked at to help derive the formula for the number of lines that will be printed. You need to take into account the restrictions on the report definition window as well as whether the body or additional headers and footers sections are suppressed for any conditions. Finally, you can use the method in the post below to identify any additional restrictions added by the Dexterity calling code:
One final point to mention is that this technique will be more complex when the report is based off a temporary table. When a temporary table is used, you would need to go back to the source data tables and reproduce some of the logic for creating the temporary table to identify the number of records that would be included in the temporary table. While not impossible, extra work is required.
Please see the following post for more information on working with Report Writer, VBA and ADO:
Please add a comment if you have written something similar to this concept.
This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.