Microsoft Dynamics AX 2012 Reporting: How to run reports that executes longer than 10 minutes

Sometime ago we run into problem when reports that executes longer than 10 minutes are running into timeout error. To overcome this problem:

- For reports printed on screen job you need to install kernel hotfix KB 2642168 - the KB article is still not published on Partner source but you should be able to get hotfix through Microsoft Dynamics AX Support team

- For reports printed on screen or in batch. You need to:

1. Open configuration:
1. If a configuration file has been created for reporting services according tohttp://technet.microsoft.com/en-us/library/hh389774.aspx (make sure it’s in the right folder!), open that file in Microsoft Dynamics Ax Configuration Utility
2. If no such configuration file has been created, open the Microsoft Dynamics Ax Configuration Utility configuration - change configuration target to Business Connector (non-interactive use only) - this configuration is stored inHKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Dynamics\6.0\Configuration\
2. On the Connection tab, select Configure Services
3. Select the service binding to modify under the Bindings node (QueryServiceEndpoint in this case) and tweak the SendTimeout or ReceiveTimeout parameter as necessary. SendTimeout is the one that needs tweaking:
4. 
5. Restart SQL Reporting Services and Microsoft Dynamics Ax Client

How To: Addressing SSRS Session Timeouts

Dealing with Dynamics AX 2012 Reporting Timeouts and Thresholds

Dynamics AX 2012 uses SQL Server Reporting Services for rendering reports. SSRS gets the data from AOS by using a custom SSRS Extension that uses WCF to communicate with AOS.

Depending on the size of the data and the complexity of the report, it might take a long time for the report to execute, resulting in various timeout and other thresholds being hit, which might cause the report rendering to fail. This article attempts to identify all the places where rendering large reports may cause thresholds could be hit and suggest tweaks or workarounds to address them.

A. Getting data ready

If the report uses Report Data Provider (RDP) to get the data, then it should be modified to use a pre-processed RDP class as the data source to invoke processing logic before a call is made to Reporting Services. For more information about RDP classes, see Using Report Data Provider Classes to Access Report Data and Report Programming Guide.

B. Report Execution Timeout

SSRS defines a Report Execution Timeout, which specifies the number of seconds after which the reporting processing times out. The default value for this is 30mins. If the report execution takes longer than that, then the report execution will fail.

This setting can be updated at the Report Server level or at an individual report level

Site level Settings

The report execution timeout at the report server level should be set to a value greater than the time required for the largest report to render. Alternately it can be set to never time out. This can be done in one of two ways -

1. Using the Report Manager - From the Site settings, the Report Timeout property can be changed.

2. This can also be changed using SQL Server Management Studio – In SSMS, Right-click the name of a report server, then click Properties. On the Server Properties window, click the Execution page and change the value for “Limit report execution to the following number of seconds”.

Report level Settings

The report execution timeout can also be set on each report, using Report Manager. Go to the report properties (see how) and in Processing Options, select either “Do not timeout report” or change the “Limit report processing to the following number of seconds” option.

C. SSRS Session Timeout

SSRS maintains a User Session which may time out if the report takes a long time to execute, causing the report execution to fail. This can be fixed for the report server by setting the 2 properties SessionTimeout and SessionAccessTimeout using the rs.exe tool. Again, these should be set to be greater than the time taken to render the largest report.

You should configure these values to be no less than the time it takes to render your largest report. Here is a sample script for rs.exe which will set these values for you:

Public Sub Main()
    Dim props() as [Property]
    props = new [Property] () { new [Property](), new [Property]() }
    
    props(0).Name = "SessionTimeout"
    props(0).Value = timeout
    
    props(1).Name = "SessionAccessTimeout"
    props(1).Value = timeout
    
    rs.SetSystemProperties(props)
End Sub

You can run this script with the following command:

rs.exe -i <Path to SessionTimeout.rss> -s <Report Server URL> -v timeout="6000"

The tool rs.exe is usually located at “c:\Program Files(x86)\Microsoft SQL Server\110\Tools\Bin”.

The timeout is expressed in seconds, so this example sets the SessionTimeout and SessionAccessTimeouts to about an hour and a half. 

Example: c:\Program Files(x86)\Microsoft SQL Server\110\Tools\Bin\rs.exe -i c:\Temp\sessiontimeout.rss -s http://localhost/reportserver -v timeout="6000"

Important: Do this with caution, keeping a session around longer than necessary can cause your ReportServerTempDB database to grow larger since temporary session snapshots will not be aged out as often.  Also, this utility must be run as Administrator.

You can also check out this msdn blog post for more information.

D. WCF Timeouts and Thresholds

SSRS uses the Query Service (which is a WCF service exposed by the AOS) to get data. For reports with large datasets, the default WCF configuration may cause WCF to hit some thresholds at runtime. So the WCF configuration can be tweaked as follows -

Server side settings –

- Open the Ax32Serv.exe.config file (it is typically under c:\Program Files\Microsoft Dynamics AX\<version>\Server\MicrosoftDynamicsAX\Bin).

- Locate the QueryServiceBinding element. The default value for the sendTimeout on this element is 10mins.

- Increase the sendTimeout to a larger value, say 30mins, like so – sendTimeout=”00:30:00”

Client side settings –

- Create a new local client configuration using the Microsoft Dynamics AX 2012 Configuration as explained here.

- On the Connection tab, click on “Configure Services” to open the SVC configuration utility.

- Navigate to Bindings -> QueryServiceEndpoint (netTcpBinding).

Update the values for the following properties -

o SendTimeout – This is set to 10mins by default. Increase it to a larger value, like 30 mins.

o MaxReceiveMessageSize – This is set to 2147483647 by default. Increase it to double that value or 4294967295. The maximum allowed value is Int64.MaxValue.