Monday, July 28, 2014

AX 2012 Max Buffer Size setting vs AX 2009 and before

'MaxBufferSize' is a system value for the Dynamics AX that may need to be adjusted to allow large queries or variables to be used. The adjustment becomes especially true when dealing with import/export of data in a single variable (like loading a large XML into an XML variable). It's primary use is to throw an error when a file or query falls outside of the 'expected max' value. This prevents the system from being brought to its knees from an unexpected scenario or file or query 'much' larger than expected. Very large report queries can draw an error triggered from this value as well.

In Dynamics versions AX 2009 and before, this setting was hidden in the depths of the registry. I streamlined changing this value via a job I posted in the below blog post: 'Insufficient Memory to Run Script - MaxBufferSize'. This was helpful for environments where changing registry values or restricted access was an issue. 
I think the reason it was hidden was to prevent people from nonchalantly changing the maximum buffer size to something like 500MB in a matter of seconds. Navigating and changing a value in the registry makes people think a little harder about the consequences of changing a value. 
Now in AX 2012, this parameter is available for everyone to use in the AX Server Configuration Utility. Users can change this value without crawling around in the registry but be very careful with changing this value from the default. Depending on application, it can be very useful to change this value quickly and may have little to no impact seen. In other applications, this can potentially take your system down to its knees by allowing a user to run something like a large query that will be 'too' large for the anticipated system load.


Friday, July 18, 2014

Dynamics AX - How to make a table an optional parameter in a method

I couldn't remember how to make a table value option for a method. I've done it a ton before but just couldn't recall at the time. Tried doing a few searches and didn't find anything so thought it would be a good post.

Dynamics AX can allow parameters to be optional for a given method. This is accomplished by assigning a parameter to an appropriate value at the top of the method (see below example). If there is no assignment, the parameter is required. The one rule is that all optional parameters must be after the mandatory ones. It is not allowed to have a mandatory following an optional one.

When calling the method, you can see which parameters are mandatory and which are optional as the optional values will show up with a bracket '[' in them in the intellisense.

All that being said, below is how you would accomplish making a table optional with a null value in its place. Simple assign the functions parameter to a value of 'null'.

public void processInbound(Table1 _mandoatoryTableParam, Table2 _optionalTableParam = null)

For additional information about certain data types being null values, see below:

Null Values for Data Types [AX 2012]
Value treated as null
Date 1900-01-01
Enum Element with its value set to 0.
Integer 0
Real 0.0
String An empty string
Time 00:00:00
Utcdatetime Any value with its date portion as 1900-01-01 is treated as null, regardless of the time portion value. Therefore the value 1900-01-01T22:33:44 is treated as null.
Any utcDateTime value with its date portion as 1900-01-01 is displayed as blank by the X++ print statement. Only the value 1900-01-01T00:00:00 is displayed as blank by the Global::info method. That is the value from the DateTimeUtil::MinValue method.

Friday, July 4, 2014

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.