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“.
- 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.
We will use an existing AOT query named “CustTableListPage”
In this post, I will demonstrate “Auto Design” type of report with creation method as “query-based”.
For the query based reporting, we need to have an AOT query added in the DataSet.
1. Create New project in Visual Studio
2. Once the project is created, add a new report to it by right-clicking as shown in below figure
3. A report will be created. Change the name of the report. In AOT, this report name will be used for your SSRS report.
Note the properties of Dataset table
Data Source: Dynamics AX
Data Source Type: Query
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.
5. Check the nodes as shown below
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.
6. On Autodesign node (Customers), go to properties and specify the report style as shown below
8. On Customers node under Autodesign node (Customers), go to properties and specify the table style as below
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
9. Preview report by right clicking Auto Design (Customers) node under Design node.
10. 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”
2. Ensure that you get this 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
Object Type: SSRS Report
4. When you open the menu item, a dialog will open, click ok and view the report