Tag Archives: Reporting

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

#TipADay: Day 1: Extract Reports in Microsoft AX2012

Today we will explore an informative feature in AX 2012. AX provides a feature of some reports based on data retrieve from transaction processing database for Microsoft Dynamics AX. These reports are easy to create and modify based on user’s requirement.

Some of the reports these kind of reports are as follows:

1. Management reporter

Management reporter (MR) tool will help users for financial reporting in a customized format which also offers function to update statement on rolling basis by adding rows and columns as required. It also provide hypertext which further extend to detail content of respective areas , like if user click Sales figure on Profit and loss account it extends into detail sales analysis.

2. Dynamics AX adds in Excel

MS Dynamics AX 2012 provide adds in excel which connects excel to database of AX Client. To extract new report just select required table and add lines and refresh the data, all fields are populated with data required. This feature also helps users to add new and update information in AX, like extract Main accounts from AX using adds in functionality.

Step: 1 Click Dynamics AX adds in in excel

yt1

Step: 2 Click Refresh

yt2

  • 3. Auto report wizard

Auto reports can easily extracted from many forms however they are preconfigured reports that users can generate by clicking File > Print > Print in a Microsoft Dynamics AX form but can be created, saved, modified, edit and deleted whenever required. These reports ca and can be re used in future.the reports are customized from wizard by adding required fields from tree add sum of quantity, prices and cost etc to reports from wizard.

Below is a sample of custom auto report for On hand

yt3

4. And finally an export to excel functionality

AX provides a functionality to export data from one form to excel and then users can easily make their own customized form of report by using pivots or just print them for tracing and recording. This feature is normally available on every form.

A sample of this report is as follows;

yt4

I hope this will help you to explore AX reports more. Keep exploring MS Dynamics AX 2012 and let me know your suggestions