Microsoft Dynamics AX 2012 Reporting Services – Integrated with SharePoint Farm

Configuring report servers to run in SharePoint integrated mode provides some additional functionalities that are only available when you deploy a report server in this mode. SharePoint integrated mode is supported if you are using Microsoft Dynamics AX 2012 R2 or later. This article guides you how to set up and configure SQL Server Reporting Service integrated with SharePoint 2013 Web Farm in Dynamics AX 2012 R2.

Network setup

Machine Name	Role	Prerequisites
Terminal Server	Dynamics AX Client	· Windows Server 2012 R2 Enterprise (64 bit) · Microsoft Dynamics AX 2012 R2 - Client
IIS01	SQL Server Reporting Services instance 1 SharePoint Application server SharePoint Central Admin server	· Windows Server 2012 R2 Enterprise (64 bit) · SharePoint Server 2013 · SQL Server 2012 · Microsoft Dynamics AX 2012 R2 – Reporting Extension
IIS02	SQL Server Reporting Services instance 2 SharePoint Application server	· Windows Server 2012 R2 Enterprise (64 bit) · SharePoint Server 2013 · SQL Server 2012 · Microsoft Dynamics AX 2012 R2 – Reporting Extension
IIS03	SQL Server Reporting Services instance 3 SharePoint Web Front-end Server	· Windows Server 2012 R2 Enterprise (64 bit) · SharePoint Server 2013 · SQL Server 2012 Reporting Service · Microsoft Dynamics AX 2012 R2 – Reporting Extension
IIS04	SQL Server Reporting Services instance 4 SharePoint Web Front-end Server	· Windows Server 2012 R2 Enterprise (64 bit) · SharePoint Server 2013 · SQL Server 2012 Reporting Service · Microsoft Dynamics AX 2012 R2 – Reporting Extension
SQL01	Database Server for SharePoint, Reporting Services and Microsoft Dynamics AX	· Windows Server 2012 R2 Enterprise (64 bit) · SQL Server 2012 Enterprise – Database Engine Services · SQL Server 2008 R2 Enterprise – Full-Text Search · Microsoft Dynamics AX 2012 R2 - Database
AOS01	Dynamics AX Application Object Server	· Windows Server 2012 R2 Enterprise (64 bit) · Microsoft Dynamics AX 2012 R2 - Application Object Server

 

Configure SharePoint Server 2013

Create a Business Intelligence Center on SharePoint

1) On IIS01, Log on as the SharePoint Farm Administrator, go to Start >> Apps >> Administrative Tools >> SharePoint 2013 Central Administration.

2) Click Application Management >> Create Site Collections

3) Select Business Intelligence Center as the template

4) Set

• Primary Site Collection Administrator = @SharePoint_Farm_Administrator
• Secondary Site Collection Administrator = @Business Connector
• Site URL = <axportal.contoso.com/sites/AXBI>

 

Enable Content Types in all document libraries

1) From SharePoint 2013 Central Administration, Go to Site settings. In SharePoint 2013, click the Settings icon

2) Click Site collection features

3) Find the Report Server Integration Feature and click Deactivate.

4) Refresh the browser then click Activate for the Report Server Integration Feature.

 

To enable content type management for a document library

1) Navigate to the BI Center (<axportal.contoso.com/sites/AXBI>)

2) Click Dashboards on the left hand side menu items.

3) Click Library in the ribbon.

4) On the Library ribbon, click Library Settings.

5) Under Content Types, click Add from existing site content types

6) In the Available Site Content Types list, click Report Builder Report, and then click Add to move the selected content type to the Content types to add list.

7) To add Report Builder Model and Report Data Source content types, repeat the previous step.

8) When you finish adding content types, click OK.

Create a document library

1) From the BI center (<axportal.contoso.com/sites/AXBI>), click Libraries, and then click add and app

2) Select Document Library and specify the Name (AX SSRS Reports), click Create.

3) Now you can see the SSRS document library is created.

 

Install the Reporting Services extensions

Log on as SharePoint farm administrator on IIS01 (before this, please add this account as a Dynamic AX System Administrator in AX).

After you have created the document library, complete the following procedure to specify the URL of the document library in the Report servers form in Microsoft Dynamics AX.

1) Start Microsoft Dynamics AX Setup. Under Install, select Microsoft Dynamics AX components.

2) On the Select installation type page, select Custom installation. Click Next.

3) Select the Reporting Services extensions check box.

4) On the Specify a location for configuration settings page, specify whether you want the Reporting Services extensions to access configuration information from the registry on the local computer or from a shared configuration file. If you select to use a shared configuration file, you must enter the network location of the file. Click Next.

5) On the Connect to an AOS instance page, enter the name of the computer that is running the Application Object Server (AOS) instance that you want to connect to. You can optionally specify the name of the AOS instance, the TCP/IP port number, and the WSDL port for services. Click Next.

6) On the Specify Business Connector proxy account information page, enter the password for the proxy account that is used by Business Connector. Click Next.

7) On the Specify a Reporting Services instance page, complete the action listed in the following table.

• Set Instance name = @Sharepoint.
• Set Site URL = <axportal.contoso.com/sites/AXBI>

8) On Connect to a SQL Server Database page, specify the transaction database Dynamics AX.

9) Click Next, then Install.

10) Repeat steps 1 to 9 on IIS02, IIS03 and IIS04.

 

Complete the Reporting Services integration

Specify the URL of the document library

Complete the following procedure to specify the URL of the document library in the Report servers form in Microsoft Dynamics AX.

1) Open Microsoft Dynamics AX.

2) Click System administration > Setup > Business intelligence > Reporting Services > Report servers.

3) In the Configuration ID field, enter a name that identifies the Reporting Services instance and the Application Object Server (AOS) instance that you are connecting.

4) In the Description field, enter a brief description to help you identify the Reporting Services instance and the AOS instance that you are connecting.

5) Select the Default configuration check box to make the Reporting Services and AOS instances that are specified in this record the active connection.

6) On the Reporting Server information tab, enter the following information

• In the Server name field, enter the name of the server that is running Reporting Services. Leave setting as defaults and click Next.
• In the Server instance name field, enter the name of the Reporting Services instance: @Sharepoint.
• Leave the Report Manager URL field blank. This field becomes unavailable when you select the SharePoint integrated mode check box in a later step.
• In the Web service URL field, enter the URL of the Reporting Services web service: <axportal.contoso.com/sites/AXBI/_vti_bin/reportserver>
• Select the SharePoint integrated mode check box.
• In the Microsoft Dynamics AX report folder field, enter the URL of the document library that you created to store reports: axportal.contoso.com/sites/AXBI/AX SSRS Reports
• On the Application Object Server information tab, select the name of the AOS instance.

Deploy the default reports

You need to deploy reports to all nodes (IIS01, IIS02, IIS03 and IIS04) using configuration ID parameter.

1) Open Windows PowerShell as an administrator by following these steps

• Click Start > Administrative Tools.
• Right-click the Microsoft Dynamics AX 2012 Management Shell option.
• Click Run as administrator.

2) Run the following command

Publish-AXReport –Id SSRSConfigID –ReportName *

 

-- Author: Fred Shen

-- Date:     21/Nov/2014

Fred is a Technical Architect from Microsoft Dynamics Global Practice team

Comments

• Anonymous
  March 09, 2015
  Hey man it works!

• Anonymous
  May 16, 2018
  Helpful article...thanks!