Aggregate, Group by, and Having functions in SysQueryForm

Have you ever wanted to use the SysQueryForm in Microsoft Dynamics AX to define a query that had an aggregate (sum, min, max, etc.) and a group by clause? In Microsoft Dynamics AX 2012 this is now possible. Read on to learn more about this little known feature.

The SysQueryForm is used extensively in Microsoft Dynamics AX in order to manipulate queries. The query object in AX already supported defining aggregate and group by clauses, so why can't we also use the UI SysQueryForm to define these? The answer is that as of version 2012 you now can.

One of the features I worked on in Dynamics AX 2012 was the Audit Policy feature. In brief, this feature allows setting up Audit Rules defined as AX queries that when matching records are found create Audit Cases that can later be reviewed. This would be extremely limited if all you could do was set up where clauses, so we worked with the client and server teams to extend the SysQueryForm to also support aggregate, group by, and having clauses. By default these tabs are hidden on the sysQueryForm for obvious reason, but you can easily show these tabs and expose this functionality for your customizations if needed.

The key to utilizing the new functionality is to using and initialize the SysQueryRun class in place of QueryRun. SysQueryRun derives from QueryRun so the functionality is almost the same, but SysQueryRun contains some additional methods which can be used to manipulate the SysQueryForm. The method I am interested in this post is promptShowAggregate(boolean _newValue). By default this is false, but by setting this to true the Aggregate, Group by, and Having tabs are visible and functional on the form.

Here is a very simple job which shows turning on this tab:

static void SysQueryForm_example(Args _args)
{
    Query query;
    SysQueryRun queryRun;

    query = new Query();
    query.addDataSource(tableNum(InventTrans));

    queryRun = new SysQueryRun(query);

    queryRun.promptShowAggregate(true);

    queryRun.prompt();
}

Anywhere that the sysQuery form is used, this same method can be used to manipulate its appearance. I hope you appreciate and find use for this little known feature. Personally this is one of my favorite features we added to Dynamics AX 2012 on my team.