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

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

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

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

Select a query

 

5. Check the nodes as shown below

Select fields from the tables/datasources

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

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

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 to show on report

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 Autodesign to the Design node

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

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

Deploy SSRS Report from AOT

2. Ensure that you get this success message

Deploy Report 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

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

SSRS Report

Abubaker Siddiq Shekhani

Abubaker Shekhani is an IT Entrepreneur and Full Stack Developer. He is the co-founder and the Developer behind Mytabeeb, a health care solution. He has worked for 5 years in Microsoft Dynamics AX space with Techno-functional role and glad to be one of few Microsoft Dynamics AX developers/consultants in Pakistan. He is TEDx speaker and likes to speak in public. He is an Amateur Astronomer and Astrophotographer. He is the founding member of Karachi Astronomers Society. He can be reached at me@abubakershekhani.com.

You may also like...