How to get data from multiple joined tables in AX to Excel.
The report wizard in AX is a powerful tool that lets users design their own query and fields to add to a report. Building on this idea I created a modification to allow users to design a query and choose fields and table display methods that can be exported to either excel or file.
First start by creating a new template for the export. Templates can either be shared publicly or made private for the user.
Second step - The tables tab allows the user to select tables to add to the query. The tables are grouped by the perspectives in the AOT to make searching easier.
Once the first table is added to the query then a list of related tables shows in the list. Adding one of these related tables will create a join between the two tables.
Once all the tables for the query have been selected the join type and table relations can also be modified.
Third step - The Fields tab allows the user to select which fields from the tables will be exported. The list also includes the table display methods which can be helpful in showing data that needs to be calculated or that is from other tables not in the query.
Once the fields have been selected, the field properties can also be modified. In the below example we are adding two additional fields to the table. "Delivery date year" and "Delivery date month" which can be useful for data analysis.
Much like the Report Wizard creates a report in the AOT, the data export creates a view in the AOT. Clicking [show] will open the preview form. From here results can be filtered to only show the relevant data. Data can then be exported to excel by clicking the [Export to Excel] button.
Excel opens showing data
Pivot table tool can be used to analyse data. Below shows the number of sales orders delivered to each city by month. This allows for many combinations of summarising and viewing data.
Would this modification make your life easier?
