SSRS Instalation & Configuration

Before start developing SSRS report for MS Dynamics AX, you need to install BI components and Visual Studio tools from MS Dynamics AX setup. The setup form is shown below with BI Components and Visual Studio tools options checked:

After installing BI Components and Visual Studio tools, you are ready to start working on SSRS reports for MS Dynamics AX. To verify that the BI Components installed successfully and report servers are correctly configured in MS Dynamics AX, Go to Tools -> BI tools -> BI reports servers. Configuration form is shown below:

Verify that the values are filled in and server name is the name of your machine. 

Query Report in AX

Steps for Creating Simple Query Report

1. Get required mapping from FDD(Functional Design Document) and list of required tables & fields.

2. Create Query with the Necessary fields.

3. Open Visual Studio

•  Select new project
•  Select Dynamics Ax template
•  Choose Report Model

Give name to to visual studio project with name appended with 'Report'.
Ex : SimpleQueryReport.

4. Now go to Solution explorer 

•  Right click on project 
•  Add new Report

The purpose of each report items is described below:

Ø  Datasets: The report dataset retrieves data from the AOT query. All fields which are available in AOT query are shown in the report dataset which can be referred in the report design later on.

Ø  Designs: The design or layout of the report on which the data would be displayed after retrieving from AX.

Ø  Images: Any embedded images that you want to display on SSRS report.

Ø  Data Methods: Business logic written in C#. Data methods are no more recommended.

Ø  Parameters: Report parameters which are to be displayed on SSRS report and user can filter the report based on those parameters.

5. Now go to Dataset  

•  Add new Dataset (Name it as Dataset1).

6. Go to Dataset1 properties

•  Select the Datasourse type as query
• Now select Query

7. Expand the fields and check the required fields

8. Go to Design and add design as per the requirement.

9. Go to the properties of Autodesign and set.

• Select layoutTemplate as ReportLayoutStyleTemplate.

10. Now Drag and drop Dateset1 into the Atodesign1 and select properties

• Give name as Dataset1Table
• Select StyleTemplate as TableStyleAlternatingRowsTemplate

11. Now Goto Buid and buid the project

• After build Deploy the project

12. After deploying Add the report to the AOT.

13. Once we deploy Report Goto AOT --> Visual Studio --> Dyamics Ax Model project and Refresh it.

14. Now go to AOT in Ax and Refresh SSRSReport.

• Select the report you created.

14. In AOT go to MenuItems

• Right click on Output Add new Menuitem ( as SimpleQueryReport).
• Goto Properties and Give Name, label, HelpText.
• Give ObjectType as SSRSReport.
• Select Object as we created in SSRSReport.

15. Select Report Design and Save it.

16. Click on the SimpleQueryReport in menu item and run it to get the desired Report.

Report Type & Designs

Report Types : In AX there are two types of Reports

• Query Report
• RDP Report

Query Report : We can create reports using AOT Query(static query). This approach is suggested when data is coming direct from tables.

RDP Report : RDP report involves business logic according to the requirement  This logic may be calculations or getting values from different tables based on conditions.

Report Designs : Reports in AX are used to display data to users in a format which is suitable for the way that the information is read.
A report can have multiple designs so that the code can decide at run-time which design to use. The designs can be totally different, but they would share the same data sources and form methods.

There are two common design patterns

• Auto Design
• Precision Design

Auto Design : This report is automatically generated based on the query in the data sources of the report. It Limits the possibilities for the developer as developer cannot make any changes in the design of report.

Precision Design : The developer has full control over the design, he can modify the design according to the requirement.

SSRS Program Flow

Controller Class : This class Controls flow of execution of reports.

• This class is used to perform some task before opening dialog, after closing dialog and before rendering report.
• It allows users to call the different Designs in a report based on conditions.
• Controller class also handles arguments passed from other classes and forms.

UI Builder : This class is used to create a dialog and handling events in dialog. Parameters in dialog are fetched from Query and Contract classes.

Contract Class : It consist of Parm methods which can be used for setting or getting values. this class is used for generating UI parameters.

RDP Class : It stands for Report Data Provider. This class contains business logic to populate the report.  

• Process report is a entry point for the RDP class where this method executes first.
• Insert method : This method is used to insert the values into the temporary table which are coming from different tables and some calculated fields of the table.
• Get method : This method is used to return table buffer to the SSRS to process report output based on the temporary fields and data.

SSRS ( SQL Server reporting services )

SSRS provides a tools and services to help you  to create, deploy, and manage reports for your organization. By this programming features that enable you to extend and customize your reporting functionality.
It is a report generation software that is a part of Microsoft SQL Server.
It enables users to execute reports using a web interface or even as embedded reports in applications by using a web services interface.

Report Life Cycle

Microsoft Dynamics Ax SSRS Reports

SSRS
SSRS Architecture
SSRS Program Flow
SSRS Instalation & Configuration
Reports Type & Design
Query Reports
RDP Reports
Controller Class
RDL/RDP Contract Class
RDP Classes
UI Builder Class
Auto Design
Precession Design
Static Parameters
Dynamic Parameters
Drill Through
Graphical Reports
CUBE/ MDX Reports