Exploring Drill-down SSRS Reports in Dynamics Ax 2012 R3

Drill drown reports in Dynamics Ax 2012 R3 are very vasy. For this purpose, I have to create a new AOT/Static Query AOT. This query is a join between CustTable and SalesTable. I created an Inner Join so only those Customer were selected who have associated Sales Orders. Consider following steps to create a new report:
Create a new Query with Name “DyWorldCustSales”
Drop or add Data Source with CustTable
6-21-2014 9-02-30 PM

Right click on Fields and add following fields from Customer table.

CustomerFileds

Expand Data Source inside Custtable, Add or Create DataSource with SalesTable and fields

SalesOrder

Right Click on  SalesTables Data Source and set its join properties as follows:

DataSetProperties

Expand Relationship node of SalesTable_1

Add following relationship:

Relations

Set it properties as follows:

RelationshipDetail

Now, create a new report In existing or New DataModel Report in Visual Studio project:

NewReport Project

Add New report with DyCustSalesOrderList

Add Dataset with Name DSCustomer and Point to Query which we created in above steps:

PointToQueryQueryDataSet

Now drag and drop the dataset on designer to create a designer

And Update, rename it to  DyCustSalesOrderList

Set DataTable’s Propeties visible to set false

DataTable

Add a List and set its name to CustList

Create two groups, one for CustomerGroup and second for AccountNum:

Groups

Now drop the fields from dataset which you want to display on report

Fields

Right click on CustList and set Data Navigation Style to DrillDown from properties window

DrillDownProperties

Now right click add report to aot and then deploy to Report Server

When you run the report,  from meu Item you will find following

CustomerGroupClick

Click on Customer group this will open:

reportCustomerLevel

Click on Customer account it will drill down report on Detail level:

Details

This post was originally published on http://tech.alirazazaidi.com/exploring-drill-down-ssrs-reports-in-dynamics-ax-2012-r3/

Ali Raza Zaidi is MVP, one of the only 6 people qualified from Pakistan! He is our top notch technical consultants and works in the capacity of Advisory Software Engineer for Dynamics AX 2012 in Systems Limited.

original article


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 clicks on customer account, a new report opens, which contains list of all possible sales Orders. For this purpose I created two Static/AOT queries. First for Customer, and second for Sales Order. Customer Query:

  • 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 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 be called 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 belonging to  DynamicSalesOrderList Report as follows: DrillThroughParameters   Delete all parameters except AX_CompanyName and CustId. If any other parameter remains, you definitely got following error when you called 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   This post has been excerpted from http://tech.alirazazaidi.com/exploring-drill-through-reports-in-dynamics-ax-2012-r3/ Ali Raza Zaidi is MVP, one of the only 6 people qualified from Pakistan! He is our top notch technical consultants and works in the capacity of Advisory Software Engineer for Dynamics AX 2012 in Systems Limited.