Share via

AX 2012 - Run Reports without hitting the Production DB

  • Article

This blog target a very critical problem faced by the organization who are using Microsoft Dynamics AX. Getting accurate and timely reports is vital need of the management no matter which industry you belong to.  When we talk about accurate reports it means reports should show latest data, reports like Sales projection, inventory on-hand, forecasting etc. are very complicated and use huge number of records to populate the report. If you are running these reports on the production database, it can affect performance of Dynamics AX because users might be performing DML operations in the same areas of the application from where the reports are fetching data. Many customers complain about slowness of Dynamics AX, running these complicated reports using the same production database where Dynamics AX is performing the transactional operation is one of top reasons of slowness. So remedy of this problem is that run reports on the same AX clients but fetch data from clone/replicated SQL server, to achieve this we followed following architecture mentioned in a blog. http://dynamics-ax.blogspot.com/2011/12/ax-2012-run-reports-without-hitting.html

Following part of this article shows step by step implementation of above mention architecture.

  1. Create a new AOS instance which actually act as Report AOS pointing to the replicated SQL database.
  2. Change the reporting server configuration in the production AX server and specify the new reporting server instance in the configuration. Following is the detail about how to fill configuration related fields.
    1. In the Server Name field, enter the name of the newly created reporting server.
    2. In the Report Manager URL field, enter URL of newly created report server management portal.
    3. In the Web Service URL field, enter URL of newly created reporting service.

For more detail https://technet.microsoft.com/en-us/library/hh389773.aspx

  1. Click Validate setting button to validate and complete the configuration process.

  1. Create an AX Client configuration pointing to the newly created reporting AOS server.
    1. Click Start > All Programs > Microsoft Dynamics Client Configuration to open configuration utility.
    2. Click on the Manager button to open the configuration menu, click on Create configuration.

  1. In the Configuration field, enter name of the configuration “Microsoft.Dynamics.AX.ReportConfiguration”.
  2. Click on Connection Tab, then click on Add button in the bottom and enter Server Name, TCP/IP Port and WSDL Port.

  1. Click on Refresh Configuration to complete the configuration process.

  1. Save the configuration to a file with name “Microsoft.Dynamics.AX.ReportConfiguration.axc”

  1. Copy the exported file to the following location of Production Reporting Server instance.

“[Drive]: \Program Files\Microsoft SQL Server\MSRS11.[SSRSInstanceName]\Reporting Services\ReportServer\bin”

  1. Delete newly created configuration in step # 4 and select the original configuration of production AX.

  1. Now all the Reports of Dynamics AX will point to new Reporting AOS and data will be used from replicated database.

Note: While testing reports we faced following error.

"The DefaultValue expression for the report parameter ‘AX_CompanyName’ contains an error: Request for the permission of type 'System.Security.Permissions.EnvironmentPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. (rsRuntimeErrorInExpression)"

To resolve this error we updated “-\Reporting Services\ReportServer \rssrvpolicy.config” and use "FullTrust" permission set in place of "Execute" for the "Report_Expressions_Default_Permissions". Source of error solution: http://blogs.msdn.com/b/axsupport/archive/2012/02/02/microsoft-dynamics-ax-2012-reporting-extensions-error-system-security-permissions-environmentpermission-while-running-report.aspx