Deciding on Reporting Architecture (SSRS + Dynamics AX 2009) for a new Implementation

A few days ago, we were brainstorming on the reporting architecture for a new implementation.

We had the following options on the table:

 

1. SSRS fetching data through EP

Pros: 

•  Ax Security (Configuration key, Security key etc.) set at table levels can be leveraged 
•  No bypass of Business Logic as the communication is through AOS
•  Leverage data methods in Reports  

Cons:

• Tight integration of Reporting IDE with AOT
• AX Resource need to spend time learning SSRS
• Additional components such as EP and Role Centers needs to be configured

2. SSRS fetching data from Ax Database using Stored Procedure

 Pros:

 

• Ax team and SSRS team can simultaneously work on Data and Report design resp
• Additional component such as EP does not have to be installed 
• It provides an option of writing logic inside Ax or in SQL store procedure
• Stored Procedures provide faster access to data
• Reports can be loosely coupled and extendable say tomorrow if you want to extract data from another data source say for that matter SAP, you need to just write one more SP inorder to do that

 

 Cons:

•         Internal AX Security is bypassed as SP directly access the data
•         In case of an upgrade, the metadata or the field names used in SP needs to be modified if they change    

3. AX Client

         Client was not interested in using AX thick client as it will be tedious job to install AX Client on each PC just for reports purpose and then tomorrow if the client is upgraded, then again the setup has to be done on each PC. Client preferred web based UI.

 

So, our vote was for the second option as it's loosely coupled and provides flexibility to have multiple datasources