Why I see phantom records in my SSRS report

OR: How to clean up RDP tables for Print Management reports

There is a couple of possible classifications of SSRS reports considering different aspects. SSRS reports can be Query or RDP (Report Data Provider) based. Query based reports gets the data through an AX query that defines report datasets. RDP based reports have a data provider class that contain logic for fetching the report data, and AX tables serving as datasets.

Furthermore, some of SSRS reports might have predefined print destinations that can be configured via Print Management Setup. These are so called Print Management reports, and include Invoice, Orders, Packing slips, etc. They are the most commonly used reports in AX.

All Print Management reports are RDP based meaning that they all use AX tables temporarily filled with report execution data, visible only to the current user (session).

In general, report tables of RDP based reports can be any of three possible AX table types: InMemory, TempDB or Regular. InMemory tables should be used for smaller datasets (cca. 1000 records), because InMemory tables are held in memory and written to the local disk once they grow beyond certain point. For larger datasets TempDB tables should be used.

Non Print Management reports usually use InMemory or TempDB tables while Print Management reports commonly use Regular AX tables.

Pre-processed SSRS reports

There is one more classification regarding the execution flow of a SSRS report: we can distinct between
pre-processed and “normally” processed reports:

Almost all Print Management reports are pre-processed reports with Regular AX tables serving as report datasets.

There are also pre-processed Non Print Management reports with TempDB AX tables such as Customer aging report.

In this article we will inspect Purchase order, a Print Management pre-processed report with two Regular AX RDP tables: PurchPurchaseOrderHeader and PurchPurchaseOrderTmp.

How data are stored and cleaned for pre-processed SSRS reports

If more than one user runs Purchase order report for the same purchase order at the same time, and the same Regular AX tables are used, how it comes that each user sees only his datasets on the report printout? This is accomplished by using the SrsReportPreProcessedDetails table that contains the unique Pre-Processing ID (RecId) for each report run. In addition, the same transaction is used when records are inserted into the PurchPurchaseOrderHeader and PurchPurchaseOrderTmp tables.

These are classes that ensure inserting records in both the SrsReportPreProcessedDetails table and the report tables (PurchPurchaseOrderHeader and PurchPurchaseOrderTmp) within the same transaction:

They also serve for cleanup of these tables when report execution is completed.

Methods for cleanup pre-processed Regular and TempDB SSRS reports:

The occurrence of phantom records for a pre-processed SSRS report with Regular AX tables (Print Management reports)

The question is, what happens when a report is started but an unhandled exception occurs (or AX client crashes) before the report is printed? The SrsReportPreProcessedDetails table and the report tables (PurchPurchaseOrderHeader and PurchPurchaseOrderTmp) are filled with the data related to a specific createdTransactionId, but the logic that deletes those records didn’t get to be executed.

Let’s run Purchase order report from the Purchase order confirmation journal.

We get the following printout showing a purchase order with three purchase lines:

Now let’s simulate the crash of AX client or an unhandled exception. Put the breakpoint on the line shown below and when the debugger hits the line, stop the code execution.

Repeat this exactly three times. Then open the SrsReportPreProcessedDetails table and locate the most recent record. Copy the createdTransactionId field value.

Locate the report RDP tables PurchPurchaseOrderHeader and PurchPurchaseOrderTmp, open and filter them by the copied value.

If we started the report for the same purchase order again, we would get:

We can see that we got the 6 lines instead of 3, for the same purchase order. And if we run the report again, we would get the 12 lines, next time the 24 lines and so on. How this is possible? The answer lays in the PurchPurchaseOrderDP class (in Dynamics AX 2012 R3), a RDP data provider class for the Purchase order report:

Cleanup of pre-processed SSRS reports with Regular AX tables (Print Management reports)

Can we clean up report RDP tables each time before report is executed

You could easily came up with the idea of cleaning up the report RDP tables before fill them in the processReport() method of DP class (e.g. PurchPurchaseOrderDP). But as we learned so far, the PurchPurchaseOrderHeader and PurchPurchaseOrderTmp are Regular AX tables which might contain data for more than one user (session) at the same time; some of these data are currently in use and will be deleted after report execution, and some of the data represents ‘phantom records’ which never get deleted due to unhandled report execution errors. Therefore we cannot just delete everything from these tables.

Also, in the execute() method of the SrsReportRunRdpPreProcessStrategy class we can see that:

  • SrsReportPreProcessedDetails is filled with ID of the current transaction and serves to Reporting Services to properly select only these records from the PurchPurchaseOrderHeader and PurchPurchaseOrderTmp tables that have the same createdTransactionId.
  • User connection instead of default connection is used (for the current transaction). This means that if you use the default connection to delete the data from the PurchPurchaseOrderHeader and PurchPurchaseOrderTmp tables within the scope of the processReport() method (i.e. the current transaction), you might get a deadlock.

Using default connection means that you don’t use a user connection to delete records from a table. Here it is how the cleaning of RDP tables (that are Regular AX tables) is performing using user connection (not default connection), after report execution. Deletion is done by Transaction ID read from the corresponding SrsReportPreProcessedDetails record:

The right way

In both report RDP tables and the SrsReportPreProcessedDetails table it can be found ‘phantom records’ for a pre-processed SSRS report, which can be safely deleted when report is not executed by any of users.

For example, we can execute this built-in method in order to activate Periodical Clean Up (a batch job which runs once per day):

As a temporary solution for Purchase order we can use the following job:

 

Cleanup of pre-processed SSRS reports with TempDB AX tables

From AX 2012 R2 on there is a feature that enables report RDP TempDB tables to carry report data across sessions – from AX data pre-processing session to SSRS data retrieval session. AOS kernel will not purge report RDP TempDB tables when AX data pre-processing session ends. This is because the base SrsReportRunRdpPreProcessStrategyTempDB class of report data provider classes for this kind of reports takes the ownership of report RDP TempDB tables. Then, the same class will release the ownership that causes cleaning of these TempDB tables after report execution is over.

If some unhandled exception happens (e.g. AX client crashes) and the ownership of a report RDP TempDB table has not been released, there won’t be any phantom records in the report because for the next report execution a new (physically different) TempDB table will be used.

Customer aging is an example of pre-processed SSRS report which uses an TempDB AX table as RDP table.

During report execution the TempDBNames field is used to store SQL Server TempDB table name:

If AX client crashes, this temporary table remains on SQL Server; it doesn’t get deleted after AOS is stopped (or restarted) – namely, this is the point when all used TempDB tables get deleted immediately. We can then delete such tables manually.

The lists of all pre-processed SSRS reports

The list of all pre-processed Regular SSRS reports

This list comprises both Print Management (e.g. Sales invoice) and Non Print Management (e.g. Asset mass transfer) reports with Regular RDP tables.

The list of all pre-processed TempDB SSRS reports

This list comprises both Print Management (e.g. Customer account statement) and Non Print Management (e.g. Customer aging report) reports with TempDB RDP tables.

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Docentric respects your privacy. Learn how your comment data is processed >>