When designing reports I sometimes faced a strange problem. After testing a new SSRS-report successfully I am always checking the SSRS-report’s temporary tables for table fields which were created by me during design-process but which are not used at the end (e.g. because of design-changes).
If I find one or more of this obsolete table fields I just delete the fields because they are no longer needed for the report. In a second step I refresh the datasets of the SSRS-report in Visual Studio. (Without refreshing the datasets the report would run into an error because of the missing table fields.) As last step I deploy the SSRS-report again.
When testing the report just after refreshing the datasets and redeploying the SSRS-report in most times there is no problem. SSRS-report just executes as expected because the deleted table-fields are not needed by the report.
But sometimes I get a strange error message like this (sorry, only available in German language):
The error-message says that Dynamics AX was not able to run report because “PurchOrderCollectLetterIndic”-field in “PurchPurchaseOrderHeader”-table is missing.
Indeed I just had deleted the mentioned field from the mentioned table. (FYI: The table holds the data for Header of a Purchase Order.) I also just refreshed the datasets in Visual Studio and redeployed the report. But the SSRS report failed.
We tried (mostly) everything: Restarting the client. Restarting IIS. Even restarting AOS. But nothing helped. The only solution was to wait some hours because magically the error disappeared after some time.
Long story short: We missed the obvious…
…. Restarting SQL Server Reporting Services!!!!
Restarting Reporting Services just costs you some seconds. The worst effect which is caused by an restart is that for about one minute reports are not available or just a little bit delayed.
Now the question is why restarting Reporting Services solves the problem? The solution is simple:
Reporting Services caches report-data for some time and does not recognize the change in table-data. After some time of not running a SSRS-report Reporting Services shuts down most of its application pool. When requesting a new report, SSRS restarts its application pool. When doing this it loads the report data completely new and recognizes the change in table data. The result is that SSRS Report now runs perfectly fine.
Conclusion: If you face an error regarding SSRS reports in Dynamics AX 2012 which could be caused by refreshing-problems first restart SQL Server Reporting Services before you restart AOS or something other which may affect other users