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.

http://dynamics-ax.blogspot.com/2011/12/ax-2012-ax-ssrs-report-design-concepts.html

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

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.