AX 2012 Maximum Export to Excel and Performance Data Issues

One of the most valuable features of AX to an organization is its capability to get the right information to the right people at the right time (e.g. ad hoc reporting). A major way to accomplish this is to leverage AX's tight integration Excel for virtually limitless reporting capabilities across multiple data sources.

In AX 2012, there is a way to export data to Excel from an AX grid, with possible filters and user form preferences (e.g. MorphX field changes). The shortcut to this has changed over time (ctrl+e vs Ctrl+t) but there may also be an actual Excel Export button on the form. While this feature is incredibly useful, there are a few downsides to this:

• it does require the AX client's full attention while exporting   
• User's AX client will be unusable 
• File generation can take a significant amount of time (sometimes >45 mins for large data set)
• There is a limit to the number of rows that can be exported
• CRM to Excel is 10k lines
• AX to Excel is 65k lines (I believe)

Depending on the organization, these limitations can be incredibly debilitating. Some limitations can be configurable but others cannot.

So how would we overcome these limitations? We can address them point by point:

1. AX client usage -> Move the processing to the server freeing up the user's AX instance to do other tasks  
2. File Generation time -> Processing on the server should be faster than on the client so file will be ready sooner  
3. Row Limit -> Create and save an excel file via X++ rather than using AX's export feature

Unfortunately, we can't leverage this solution for exporting using a keyboard shortcut (e.g. Ctrl+t) but we can add it when exporting using a 'Export to Excel' button. As examples, there are a number of these buttons in base AX already. The key is to change the functionality behind these buttons and utilize the code to alleviate the issues detailed above.

To accomplish this, we need to:

• Override the clicked method on the 'Export to Excel' button (or add one to the form)
• Remove the super() and paste in the code below (includes the file location field which is required)
• Create a class and action menu item which will loop over a data source and form/usage data of the grid to create a file in Excel which will reflect exactly what the user sees within AX. This should extend run base batch.

With the above, the user can get a prompt asking if and where they would like to create a file with the specific data to Excel. This will utilize base AX's batch functionality so the user can either run it manually at that exact moment or have it run it batch for a specific time. The later of this option allows the user to continue with their daily functions.
 

The code is not too complex and shouldn't take too long to assemble including testing. There are a few gotchas in the code like creating the fields in the Excel to the exporting user's usage data preferences, but a pretty fun overall mod. Hopefully the above will give some people a direction to take to extend AX in a very practical application.