Report showing incorrect data
I have a fairly complex coded report and a user has reported a single page (final summary page of an invoice) that has the wrong data on it.
The report is for self billing invoices, where there is the main detail followed by a summary page.
The reported problem only happens when the report includes multiple invoices and was the first time they had seen the issue.
The report uses a number of data pipelines:
Report is one pass with Cachepages on
Main report from (master pipeline)
Contains:
Subreport1 (main header/detail)
Subreport2 (final page)
Subreport2 final page also contains another Subreport3 that is in a group footer.
There is a master memory table (and pipeline) which contains the record id's for each invoice, other tables/pipelines as well that have a master detail relationship set up so that as the master memory table row changes the linked tables contain only the detail needed. There is two summary memory tables that are generated in the OnMasterPipeTraversal, in that function it checks to see if the record id has changed since last called and populates a summary memory table that is used for the report final page.
I managed to re-create the problem, I have also found a workaround but wanted your opinion as to if I am doing something incorrectly or it is indeed an issue with Reportbuilder (this is in 21.04).
The problem appears to be linked to PreviewFormSettings.PageIncrement
The default 100 is right in the middle of the report with the wrong data, if i change the selection of the invoices to print so a different invoice spans the 100 page point it also prints incorrect data in the summary.
Change PageIncrement to zero and the whole thing prints as it should.
The report is for self billing invoices, where there is the main detail followed by a summary page.
The reported problem only happens when the report includes multiple invoices and was the first time they had seen the issue.
The report uses a number of data pipelines:
Report is one pass with Cachepages on
Main report from (master pipeline)
Contains:
Subreport1 (main header/detail)
Subreport2 (final page)
Subreport2 final page also contains another Subreport3 that is in a group footer.
There is a master memory table (and pipeline) which contains the record id's for each invoice, other tables/pipelines as well that have a master detail relationship set up so that as the master memory table row changes the linked tables contain only the detail needed. There is two summary memory tables that are generated in the OnMasterPipeTraversal, in that function it checks to see if the record id has changed since last called and populates a summary memory table that is used for the report final page.
I managed to re-create the problem, I have also found a workaround but wanted your opinion as to if I am doing something incorrectly or it is indeed an issue with Reportbuilder (this is in 21.04).
The problem appears to be linked to PreviewFormSettings.PageIncrement
The default 100 is right in the middle of the report with the wrong data, if i change the selection of the invoices to print so a different invoice spans the 100 page point it also prints incorrect data in the summary.
Change PageIncrement to zero and the whole thing prints as it should.
Comments
The "incorrect data" does not appear to be a result of something ReportBuilder is doing but rather appears to be in the summary memory tables that you are populating inside the OnTraversal event of a pipeline. The timing in which calculations or data manipulation is done is very important when using report events. The OnTraversal was not designed with this in mind so it's possibly firing unexpectedly when the threaded page cache finishes its initial incremental run.
1. Have you considered using datapipeline linking of your datasets rather than memory tables for your master-detail relationship? This will not fix the problem per se, but will make data management simpler and faster.
2. If using OnTravesal, it appears you will need to make a complete run generating the report for the event to fire as you need. Another option is to try another event such as the OnRecordChange and check you are only firing once per record.
3. You might try setting the PreviewFormSettings.UseBackgroundThread property to False and see if that helps the issue.
4. Ultimately, creating the summary tables as the report generates could be troublesome. The best solution would be to traverse your dataset(s) manually and compile them before the report generates.
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
Thanks you for the Tips, helped me decide how to approach this problem.
1. I seem to remember in the distant past trying the linking and having an issue with the pipeline going in a single direction (e.g if the first row of a pipeline A linked to the last row of the pipeline B and the next row of pipeline A linked to the first row of pipeline B it would not return data). But this was probably early to mid 2000s.
2. After trying OnRecordChange, I decided to make a log file to trace what was happening as this event fires a lot more times.
The issue was that OnFirst and OnTraversal were linked to the same function (maybe this was needed on an older reportbuilder as the report is about 19years old).
When the report stopped at page 100, the pipeline was going back to record 1 and this was firing the OnFirst (which was building the table again for the first record). When Building resumed the wrong data was in the memory table and OnTraversal does not Fire Again.
Removing the OnFirst solved the problem and I have made sure that data is not being fetched multiple times from the SQL server.
3. I have not tried this, but I guess I dont need it now
4. This is how 99% of our coded reports are constructed with the data compiled and sorted prior to rendering.
Great find! Glad you got it sorted.
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com