Create SSRS Report (Query based) – MS Dynamics AX
In this post I will demonstrate creation of SSRS reports in MS Dynamics AX 2012. If you want to know the basics, please read my previous post “Reporting in MS Dynamics AX 2012 – SSRS Concepts“.
Pre-Requisites
- Microsoft SQL Server 2012
- SQL Reporting Services
- MS Dynamics AX 2012
- SSRS extensions for Dynamics ax
- Visual Studio 2012
- Visual Studio Tools
And most importantly you must configure reporting services.
Queries
We will use an existing AOT query named “CustTableListPage”
Design
In this post, I will demonstrate “Auto Design” type of report with creation method as “query-based”.
Steps:
For the query based reporting, we need to have an AOT query added in the DataSet.
Visual Studio
1. Create New project in Visual Studio
Create Report Model project in Visual Studio.
2. Once the project is created, add a new report to it by right-clicking as shown in below figure
Add report to the project
3. A report will be created. Change the name of the report. In AOT, this report name will be used for your SSRS report.
Add a query in the Dataset node
Note the properties of Dataset table
Data Source: Dynamics AX
Data Source Type: Query
Name: Customers
4. Add a query to the Dataset.
The below figure shows that we have added a required query in the Dataset which is in our case is Customers List. Once this is created, in properties, go the query property and click on “…”. A dialog as shown below will be opened. Select the query.
Select a query
5. Check the nodes as shown below
Select fields from the tables/datasources
6. Add a design of type Auto Design for the report. Do this by following the below screen shot.
In this post, I am demonstrating the Auto Design. Other design type is Precision Design.
Add Auto Design to the design node
6. On Autodesign node (Customers), go to properties and specify the report style as shown below
Specify Report Layout Template
8. On Customers node under Autodesign node (Customers), go to properties and specify the table style as below
Drag and Drop fields in auto design node (Customers) to show on report
8. Drag and Drop fields from Dataset to Data node of the Autodesign node (Customers)
Drop only those fields that you want to show on report
Add Auto Design to the Design node
9. Preview report by right clicking Auto Design (Customers) node under Design node.
10. Add report to AOT
Add Report to AOT
Microsoft Dynamics AX
From this point we have got the following ready
1. Report Project Created
2. Visual Studio project added to AOT under Visual Studio projects
3. Report added to the SSRS report node of AOT
Steps to deploy and run the report from AOT
1. Go to SSRS node and find the report and right click and select “Deploy Element”
Deploy SSRS Report from AOT
2. Ensure that you get this success message
Deploy Report success message
3. Open SSRS report from AOT using a Menu item
Now you can create a menu item and assign it to the relevant menu. For the demonstration purposes I have created a Output type menu item and have assigned the following settings in the properties of menu item
Name: CustomerListReport
Object Type: SSRS Report
Object: CustomerList
ReportDesign: Customers
Create Output type menu item and open report
4. When you open the menu item, a dialog will open, click ok and view the report
SSRS Report
