≡ Menu

Query Based SSRS report from Scratch Dynamics Ax 2012 R3.

In Microsoft Dynamics Ax 2012, we can create less complex reports with AOT Query or Static Query.

Simple or model based report is as follow.

QueryBased Report



The steps are as follow  We can create simple or model based report with following steps

  • Step 1: Create a Microsoft Dynamics AX query
  • Step 2: Create a new report in Visual Studio
  • Step 3: Apply Layout template
  • Step 4: Add Column sorting
  • Step 5: Group report data
  • Step 6: Filter report data
  • Step 7: Add a dynamic parameter
  • Step 8: Save and deploy the report
  • Step 9: View the report in Microsoft Dynamics AX


We can see these steps below


Now consider a scenario, where we have to display sales item detail with respect to its customer. As this report is not much complex and on exploring Dynamics Ax 2012 default tables we found that Sales order detail at Item level can be found on “SalesLine” table. Complexity is less so we decide to create this report with Static Query or AOT query based Report.

This Article is based on Contoso demo data, and Dynamics Ax 2012 R3 on demo licences



Open MS Dynamics Ax 2012 Client And press Ctrl+Shift+W to open dev environment or open the AOT.

When AOT open expand it and at AOT node add new query


Rename Query to simple “QSalesLine”.

Expand its data Source Node, right click and add new data Source

Add DataSource

Rename the Data Source to SalesLine and select SalesLine table


Now expand the field Node and set its dynamic property to no


Right Click on Field and add new field and select salesId





Similarly you can add following fields

  • CustAccount
  • SalesId
  • OrderQty
  • SalePrice
  • LineAmount
  • ItemId,
  • Name,
  • CustGroup



Now save the Query.


Open Visual studio and create a new report project




Open Visual studio environment and create a new Report and Rename It to SalesLineReport


From solution Explorer add new report


Now expand report and right click on data Source to create a new data source Rename It to DSSaleLine



Right click on “DSSaleLine” it Edit it or click on properties and open property window


From Property window select


Click on Query and from Brower window select Required Query


Click on next window



Select all fields and click on save






Now drag the data set and drop on designs node, It will create auto Design


Right click on report designer and do the following

Rename AutoDesign to as “SalesLineDesign”

Select ReportLayoutTemple to ReportLayoutTemplate

Title to “CustomSalesLineReport”

and click on save.


Now expand “SalesLineDesign” design and drop and drop CustAccount in Group and Sort node. This will create the records group and line according to CustAccount



Now Compile the report, Deploy it and At to AOT.



Now open the AOT. Change the legal entity to “USMF” expand the menuItem node, and create a new display menu item





Change its Name to mnuCustomSalesLineReport

Change object Type to SSRS report and select the object is SalesLinereport and Report Design to SalesLineDesign




Now save the menu and let’s run the report


Click on ok to run the preview


Adding Dynamic Parameter

Create another query in AOT name it QCustomer, and add AccountNum is in field. You have to follow the same step which we used to create QSalesLine Query. Save the Query


Now switch back to model project in Visual studio and add new Dataset and Name it DSCustomer and pointed to QCustomer query the same way we select the Query.


Now expand parameter node of report

And add new Parameter with Name “CustomerParm”

Expand Values and set



Now expand Report designer and under filter node add new node


Right Click on AccountFilter and from property window

Click on expression  To select = Fields!CustAccount

Operator select “Like”

And Value select =Parameters!CustomerParm.Value







Save the report and deploy it, and then Add to AOT.


Open Aot and from item display menu to run report


You will found a new drop down for parameter in




Form this parameter You can select and run the report for specific customer


{ 0 comments… add one }

Leave a Comment