≡ Menu




Exploring Drill through Reports in Dynamics Ax 2012 R3.

Creating basic drill through reports in Dynamics Ax 2012 R3 is really simple. Consider following scenario where we have to show the list of customer and their sales Orders in particular legal Entity.  In this report when User click on customer account, a new report open which contains list of all possible sales Order.

For this Purpose I created two Static/AOT queries. First for Customer, and second for Sales Order.

 

Customer Query is as follow.

  • Create a new AOT query with Name DyWorldCustomerList.
  • In DataSource add CustTable table.
  • In fields dynamic to true.

CustListTable

 

Similarly Create another Query “DyWorldSalesTable” for SalesTable. With Name DyWorldSalesTable

 

SalesTable

Now Open a Visual studio and create a new Dynamics Ax Report Project

 

Report Project

Add a new report with name

  • DynamicCustomerList

 

  • Add New dataset with Name
  • DynamicCustomerList set its properties as follow

CustomerDataSet

  • Select Following fields
    •   AccountNum
    •    CustGroup
    •    Party

 

  • Drag this DSCustomerList data set on Report Designer to create an AutoDesign.
  • Change the AutoDesign1 properties as follow

o   Name to DesignCustomerList

o   LayoutStyle to ReportLayoutStyleTemplate

 ReportDesinger2

 

Now create another report inside same Dynamics Ax Model Project, this report will call from customer Report

SalesOrderReport

Create and DataSet with DSSaleOrder with query  “DyWorldSalesTable”

SalesOrderDataSet

Drag this dataset on Report Designer and update newly Designer as follow.

SalesOrderDesignerProperties

 

 

Now open the parameter Section of report and add new parameter as “CustId” of string type

CustIdParameter

Now expand report designer and expand Table and add filter with following Properties

NewParameter

Set following Properties of filter

o   From  Expression select “=Fields!CustAccount.Value”

o   Name= Customer

o   Operator =Equals

o   Value “=Parameters!CustId.Value”

Properties

 

Save the report.

 

Now Open the first report “DynamicCustomerList”. And expend the reports design, “DSCustomerListTable” and then Data and select AccountNum.

Right click on AccountNum and select “Report Drill Through Action”

AddDrillThrough

 

Now Double click and Select the report designer which will open against the click on field

ActionProperties

Select SalesOrderReport and its Desinger form dialog

ReportSelectionSalesOrderList

It will create, number of parameters, all belongs to  DynamicSalesOrderList Report as follow

DrillThroughParameters

 

Delete all parameters except AX_CompanyName and CustId. If any other parameter remains, you definitely got following error when you call Sales Order report form drill through option.

“The Microsoft Dynamic AX parameter ID should be a RECID. The exception is System.FormatException: Input string was not in a correct format.”

AfterDeleteParameters

 

Please Select following Values for these Properties AX_CompanyName

Select its value “=Parameters!AX_CompanyName.Value

CompanyNameValue

Please select the following value for CustId as “=Field!AccountNum.Value”

SaleIdValue

Now saves the reports and right click on Report Project to Add to AOT and then deploy on Report Server

 

AOT and Deploy

Now Go to AOT, and refresh the Select the Visual Studio Model Project and SSRS reports into Dynamics Ax project so it will easy to edit or locate easily

Menu2

Now create a Menu Item of Display type and set its following Properties

Menu

Now run the report as follow

 

CusttomerList

As click on first Customer “CNMF-000001” To its salesOrderList , The second report successfully open as follow

SaleOrderList

{ 0 comments… add one }

Leave a Comment