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.
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