A Guide to setup SQL Server Reporting Services (SSRS) with Dynamics AX

Often AX consultants are a bit dubious when it comes to setting up SSRS to work with Dynamics AX. This is simply because of the number of different platforms involved (sql server and dynamics ax) and making them work together can be a bit tricky at times..

Anyways.. I recently saw a post on http://community.dynamics.com, and the user asked for steps to setup ssrs. Rather than writing a huge reply on the forum, I decided to write a blog, as there could be others out there searching for info on the same problem.

So here goes...

SQL Server 2005 Reporting Services is a server-based reporting platform for creating reports from relational or multidimensional data sources. Reports are managed and viewed in a Web browser. Microsoft Dynamics AX uses Reporting Services to create report models and to generate ad hoc reports

This document applied to SQL server 2005 and Dynamics AX 4...

Points to Note before you begin:

1. Make sure that the user accounts to run the appropriate services (AX service, sqlServer, reporting Server) have been setup.. also make sure that ax and sql are running under the defined user accounts. Also, add the new Reporting Server user account to the Dynamics AX account user group (if you don’t have a group, then assign the reporting user the same permissions)

2. Make sure that you have a fully functional instance of Dynamics AX (preferably with data loaded)

3. For convenience I will assume that all 3 services are running on the same machine (Note: In a Production Environment, its HIGHLY Recommended to run the each of the services on individual dedicated machines)

Step 1: Install and Configure IIS on the Reporting Server

IMPORTANT: Be aware that if you have installed Microsoft .NET 2.0 prior to installing IIS 6.0, IIS 6.0 will by default use the .NET version installed with your OS. To change this, as you must run .NET 2.0, do the following:

Open IIS manager -> Right click Default Web Site -> click properties -> Click the tab page ASP .NET. In the ASP .NET version filed click the drop down and choose version 2.0. Now restart your IIS server.(to restart IIS server click on Start> Run > type in ‘iisreset’ and presses enter.

It is important that you install IIS 6.0 on the SQL 2005 Server as reporting service is dependent of IIS running.

You must add FrontPage server extension and ASP.NET. Installation of IIS 6.0 can be done via ‘Start -> Control Panels -> Add remove programs. Choose windows components’, click ‘Application Server’, Click ‘Details’, check mark ASP.NET, and click ‘Internet information Services (IIS)’, check mark ‘FrontPage 2002 Server Extensions’. OK. OK. Next.

As we will be accessing reporting services using windows integrated authentication, you should enable anonymous access to the Default Web Site. Later you will also grant anonymous access to any underlying virtual directories, created by the reporting Server Configuration Manager, which we will be running later on in this document.

To enable anonymous access to the default web site do the following:

Open IIS manager -> right click the default web site -> click properties -> click the Directory Security tab page -> for Authentication and Access Control locate and click the Edit button -> Add the check mark for enable anonymous access - > click OK -> Click OK.

Step 2: Install SQL Server Reporting Services

1. Launch the SQL 2005 installation program; click next until you reach Components to install.
2. Check reporting services. Next.
3. Instance name, this referees to already installed SQL Server Instances. Click Installed Instances, under installed instances choose your existing SQL Server, click OK. Next.
4. Service Account. This refers to the Domains user account which will run the reporting Server Service. Next.
5. Report Server installation Options, Use default settings. Configuration of the Reporting Server will be done after the installation has been completed. Next.
6. Error and Usage Report Settings. Leave as default. Next.
7. Ready to install. Install. Next.
8. Finish.

Step 3: Configure SQL Server Reporting Services

1. Go to Start -> All Programs -> Microsoft SQL Server 2005 -> Configuration Tools –> Reporting Services Configuration. Click.
2. Instance Selection, choose default settings. Connect.
3. In the Report Server Configuration Manager, first verify that Server Status is running, you will also be able to verify that this server is not initialized yet. To initialize the server follow the below steps:
   1. Click report Server Virtual Directory, locate and click new, the window create a new Virtual directory is opened, choose default settings. Be aware that the choice here refers to your IIS and by default a virtual directory called ReportServer will be created under the Default Web Site. Click Ok.
   2. You can see that a task status windows is opened, telling you that your virtual directory was successfully created, named ReportServer and located under the Default Web Site in you IIS Manager.
   3. Click Report Manager Virtual Directory, locate and click new, the window create a new Virtual directory is opened, again the default settings for this virtual directory is that it is created under the default Web Site. Ok
   4. You can see that a task status windows is opened, telling you that your virtual directory was successfully created, named Reports and located under the Default Web Site in you IIS Manager.
   5. Click Windows Service Identity, this part of the configuration manager is already setup, no changes are needed.
   6. Click Web Service Identity; here we have to specify an ASP .Net service account as well as the application pool in which to run the report server web service. This section is already configured, but you will have to click Apply.
   7. Click Database Setup, Connect to your Db server Instance; be aware that you have to refer to your SQL 2005 DB instance; this can easily be done by clicking the down arrow and choosing your instance, depending on your choices when installing your Database server.
   8. To create a new database for reporting services click new, in the SQL Server connection dialogues window, choose default setting. Click OK
   9. Again in the Database setup windows, you will have to locate and click the Apply button, for these setting to be saved.
   10. For credential type, verify that “Current user – Integrated Security” is chosen. Click OK and verify that all tasks completed without errors in the Task Status windows. Be aware that you may get a warning “Setting connection Info for reporting Server”, this is not an error and can be ignored.
   11. Click Initialization, it might seem that the report Server configuration we have just created has not been initialized; before doing anything click the refresh button, this should result in Initialization goes to green.
   12. There are a few remaining tasks like Encryption Keys, Email settings and Execution Account but these are not needed.
   13. Exit
   14. Go to http://localhost/reports. You should see the Report Manager home page.

Note: You may experience a problem when accessing the Report Manager Homepage; you may get a error relating to access rights to the c:\windows\Microsoft.Net\Framework\v2.0.50727\Temporary ASP.Net files folder, if this happens edit the security setting for this folder and add, the network service “user” and grant write permissions.

Step 4: Installing Reporting Services for Dynamics AX

This part of the Dynamics Ax installation program adds user rights to your Dynamics Ax Database for the domains user account assigned to your Reporting Server Service and creates the required access permission to this Dynamics Ax database.

1. On the server where you have installed Reporting Services for SQL 2005 Server launch the Dynamics AX installation program
2. Choose custom installation
3. Under, select computer role, select Reporting Server (You may experience a message windows telling you that Reporting Services 2005 SP1 is not installed, if SP1 has been released please install this service pack before you continue). Next
4. Select an instance of SQL Server 2005 reporting Services. All information is automatically chosen. Next.
5. Select a database for the reporting server data source. Add your Dynamics AX database server instance in server name. Chose your Dynamics AX database. Next
6. Provide credentials for the reporting server data source. Add a domain account so that the reporting server can connect to the Dynamics AX database. Next
7. Ready to Install. Install.
8. Finish.

The above steps completed the installation and deployment of the needed bits, what remains is configuring Dynamics Ax to be able to publish reports to the SQL 2005 Reporting Server.

Step 5: Grant the required rights for the anonymous web user (IUSR). 

As all users accessing the Report Mangers website and the Reports website, all use the same account to gain access, the local IUSR_YourServerName we must ensure that this user has the needed access right and execution rights on the reporting server.

First thing we need to do is to add the IUSR_YourServerName to the System User, as well as the Content Manager role on the Reporting Server, to complete this task do the following.

1. Make sure you start by removing the check mark in Enable Anonymous Access on the Default Web Site.
2. On the reporting Server box open http://localhost/reports.
3. Locate and click the Site Settings menu..
4. Locate and click, Configure Site-Wide Security.
5. Locate and click New Role Assignment.
6. In the field Group or user name, write IUSR_YourServerName.
7. Check mark the Role System User and click OK.
8. Now click Home
9. Locate and click the properties Tab page.
10. Now locate and click New Role Assignment.
11. In the Group or user field write IUSR_YourServerName.
12. Locate the Content manager role and check mark this role, click OK.
13. Done. You can close the IE browser window
14. Before we are completely finished, you will have to Re-enable anonymous Access to the Default Web Site

Step 6: Configure Reporting Services from Dynamics AX

Complete the following steps to have reporting services up and running from within Dynamics Ax.

1. Open Dynamics Ax 4.0; click Tools -> Reporting Tools -> click reporting Servers.
2. In the Reporting Servers form in the field Server name write the NETBIOS name of your reporting server.
3. In the description field add a description for this reporting server
4. In the Web service URL field, notice: https:// is automatically added, this would indicate that we are running Secure Socket Layer on this reporting server, if this is not the case then remove the ‘s’ in https, so that you are left with http://
5. In the field report Manager URL, we also have the https:// again you might have to remove the‘s’, so that you are left with http://
6. Locate the button Validate and click this button, this will validate that the information we have edited is correct.
7. An info log form will open and inform you that validation was successful.
8. Close this window and you can also close the Reporting Server Form, validation and setup of the reporting servers from within Dynamics Ax is done.

Step 7: Select Model Generation Options and Update Models

Setting Model generation Options

1. Click Tools -> Reporting Tools -> Click Model Generation Options.
2. In the Model Generation options form on the General tab page, you can setup a log file to log model update events. You can also setup the Destination server; this is the server to where your report model will be deployed. No changes are needed here.
3. Click the Content tab page, on this tab page you will be able to setup the Default Exchange rate company as well as other options related to the Model Elements.

Update Models

1. Click Tools -> Reporting Tools -> Click Update models
2. From the Update Models form you can setup option like Languages as well as define if model updating should be a batch job.
3. Do any changes you need and click OK.
4. Model generation will start and your Perspective will be deployed to the Reporting server, as a data source for use when creating your reports.
5. An info log will appear informing you that the Model has been published.

Step 8: Building Reports Using Report Builder

1. Click Tools -> Reporting Tools -> click Report Builder.
2. The Report Builder form open’s. In this for you will again be able to choose the data and language for your report. Data refers to the Perspective you have created, you can create many perspectives in the AOT and using the drop down from this form you can chose which perspective you want to use for this report. Click OK
3. The first time you use the Report Builder you will have to accept to be able to run the Report Builder web application. Click Run.
4. The report Builder web application is downloading and is installed on your local machine.
5. All you have to do is drag and drop the fields from your Report Model data source to the report layout and then click Run report, this will generate the report with live and updated Dynamics Ax data.
6. Done

Enjoy…

more Articles.... www.dynamic-ax.co.uk

 

Technorati Tags: SSRS,Dynamics AX

Transfer from http://dynamic-ax.spaces.live.com/blog/cns!13619E6948204DE3!356.entry