SSRS report using Query in Microsoft Dynamics AX 2012

Overview

There are a multiple of methods to develop SSRS reports in Dynamics AX. This tutorial will guide you through the process of developing SSRS reports using an AOT query.

Pre-requisites

1.      Microsoft Dynamics AX 2012

2.      Visual studio 2012

3.      SQL Server report server must be configured

4.      Reporting services extensions must be installed in Dynamics AX

Steps

1.      First of all we need an AOT query which will fetch data from AX and display it in a report. For this tutorial, I am using an existing query in AX which displays a list of customers.

2.      CustTableListPage query will be used in this tutorial. To find this query, open AOT àQueries àCustTableListPage.

3.      The development of the SSRS report is done in Visual studio. So a new project needs to be created in Visual studio.

4.      Open Visual studio. Go to File à New à Project.

5.      In the section Installed templates select Microsoft Dynamics AX and then select Report Model in the right pane. Name the project “QueryBasedDemo“. Press OK.

6.      A new project will be created as shown below:

7.      Now add a new report in the project by right clicking on the project QueryBaseDemo à Add à Report.

1.      A report will be added to the project with the name “Report1”. Rename the report to QueryBasedDemo.

2.      Now double click the report to open it.

8.      The description of the individual node is given below:

1.      Datasets: Datasets retrieve data from the AOT query. It acts as a bridge between AX and the SSRS report. Only the fields added in the datasets can be used in a report.

2.      Designs: It defines the layout of the report.

3.      Images: It contains the images that you want to display in the SSRS report.

4.      Data Methods: It contains the business logic which can then be used in the report.

5.      Parameters: It is used to apply filtering to the data in a report.

9.      First of all, we will create a dataset. Right click Datasets àAdd Dataset to create a new Dataset. Name it “CustTable”.

10.   Select the data source and open the properties window. Make sure the Data Source Type is set to Query. Then select the Query field. An ellipse button appears. Click it to open a dialog.

11.   This dialog lists all the queries present in the AOT. Select CustTableListPage and press Next.

12.   Select the fields you want to display in the report and press OK. Only the selected fields in this dialog can be shown in the report.

13.   There are two types of designs that can be created in a SSRS report:

1.      Auto Design: Visual studio automatically creates a design based on the dataset provided. Auto design is the preferred method because it is easy and usually fulfills the requirements for the majority of scenarios.

2.      Precision Design: It is used when you need custom placement of fields or the layout of the report is too complex.

14.   In this demo we will use Auto Design. Now right click the Designs nodeàAdd àAuto Design. A new design is added. Rename it to Design. It is recommended that you set the name of the Design to either ‘Design’ or ‘Report’

15.   Now drag CustTable form the Datasets node and drop it on the Design node. A table will be created which contain all the fields present in the data set. These fields will appear in the same order in the report. So if you want to arrange the fields, right click the field and select either move up or move down.

16.   The final design will look as shown below

17.   Now we have to define the layout of the report. Visual studio provides built in templates. Select the Design and open the properties window. Select ReportLayoutStyleTemplate in the LayoutTemplate field. Give a suitable title to the report.

18.   Select the CustTableTable under the Design node and open the properties window. Select TableStyleAlternatingRowsTemplate in the Style Template field.

19.   Report is now completed can be viewed. To preview the report, select the Design node, right click it and select preview. A preview window opens.

20.   Select Report tab. The report appears as shown below:

21.   To view this report from AX, it needs to be added in the AOT and deployed at the report server.

22.   Open the solution explorer and right click the project. Select Add QueryBasedDemo to AOT. This will add the report to the AOT. It will also add the project in the AOT with same name so if you want to modify the report in future, you can use that project.

23.   Now open AOT in AX. Go to SSRS reports à Reports à QueryBasedDemo. Right click the QueryBasedDemo Report and select Deploy Element

24.   A success message will appear if the report is successfully deployed.

25.   To open the report in AX, a menu item is required. Create a menu item that will open the report from AX.

26.   Go to Menu items à Output. Right click Output and select New Menu Item

27.   Set the following properties on the menu item as shown below.

28.   Right click the newly created menu item and select Open to view the report

29.   A parameter form will open. If you want to add parameters to report, you can add it by clicking Select. Press Ok to continue.

30.   The report is displayed as shown below.