AX 2012 - Make SSRS Report Using SQL Stored Procedure

Today we will learn how to create AX SSRS reports using SQL queries/Stored Procedure

Open SQL management studio and navigate to your dynamics AX database. Create a new stored procedure.

Write the query in store procedure according to your need.

 

 

Open Visual studio and create a new project.

 

 

Select template: Microsoft Dynamics AX, Project: Report Model

Right click on your project in solution explorer and add Report Datasource. This will be your SQL Datasource.

 

Change the provider to SQL.

 

Provide your SQL Connection String.

Server = <your server name> ; database = <your database name> ; Integrated Security=SSPI

Now Add new Report.

 

Right click your Report and add Dataset.

Give the dataSource to your SQL datasource which you just created above.

Datasource type could be query or stored procedure. In our case we created a stored procedure , so we will use stored procedure.

Now navigate to query property of dataset.

Select the stored procedure you want to use.

Test it by supplying a default value and clicking execute.

Notice your dataset is now filled with fields and parameter which comes from stored procedure.

Make the design according to your need.

Deploy the report.

ADD the report to AOT.

To run the report you need a menu item.

Create an output type menu item.

Properties of menu item:

Object Type: SSRSReport

Object : <your report name>

ReportDesign : <your report design name>

Now you are ready. Run the Menuitem and supply value to parameter accountNum which we created in stored procedure.

Report displays the results.

Hurray, you are done….! J