Self-Service Reporting in Microsoft Dynamics AX User Interface

By Gary Stoll posted Apr 6, 2017 4:03:26 PM

Any discussion of reporting and Microsoft Dynamics AX invariably leads to tools like Management Reporter, business intelligence (BI) applications, and SQL paired with Excel. These can all provide powerful analysis capabilities, and each have their place – we use all of them. They tend to have similar (though not identical) strengths and weaknesses. All can offer tailored and sophisticated analyses in a static or interactive format. However, all require a level of skill/training that varies from a power User to a dedicated IT resource to create reports, and BI tools require extra cost licenses and training even for (interactive) consumers of reports. They all share the characteristic that the level of flexibility and interactivity in a report must be designed into the report when it is created; they are really post hoc, not ad hoc, reporting tools.


In contrast – and complementary – to these tools is the self-service reporting functionality available within the Microsoft Dynamics AX User interface (UI). Surely most readers of this article are aware that the filtering capabilities that exist in Microsoft Dynamics AX forms/tables effectively provide simple reporting. However, I commonly find that people neither realize nor take full advantage of the depth that is available. In spite of much preaching and teaching, I still find people requesting SQL/Excel reports from IT for information that could easily be generated in minutes using filtering on a Microsoft Dynamics AX table. One of the subtle and underappreciated beauties of the Microsoft Dynamics AX UI is that there is a powerful set of simple tools that are consistently available in any table anywhere in the solution. The combination of grid filters, advanced filters, personalization, cues, and Excel export can provide a surprising amount of power that excels in simplicity and flexibility. The complementary nature results from the fact that the skill level required ranges from beginner to power User, the fields/criteria can be changed on the fly, any data in Microsoft Dynamics AX can be accessed (not just that which was synced to the application or defined into a report), and no additional licenses are needed.


Every Microsoft Dynamics AX table has grid filters at the top of each column (Ctrl-G or can be set on as default in Options) that enable filtering on any data – and personalization allows adding additional columns to have more fields to filter on. The advanced filter (Ctrl-F3) ratchets up the capability by allowing table joins to define sophisticated queries using related data in many tables. Lesser known is the fact that you can use SQL statements and built-in functions as query criteria instead of static values (Google “advanced filter queries in Microsoft Dynamics AX”). Cues are saved advanced filter queries – essentially self-service reports – that can be opened and refreshed with a single click from a User’s home page. Cues are an incredibly powerful tool in Microsoft Dynamics AX; if you are not using them, you are giving up a tremendous capacity to tailor the solution to your business. Our company has hundreds in use across all departments.


Because this is integral to the Microsoft Dynamics AX UI, it lends itself to a very wide range of uses across all functional areas. Common uses in our company include departmental inquiries/reporting, catching error conditions, and process monitoring/management.


For any department, Microsoft Dynamics AX provides out-of-the-box forms for common information such as my quotations, delayed purchase orders, Customer invoices past due, etc. Simple grid filtering and personalization allow any User to refine those into ad hoc reports showing things like: All sales orders for a discount promotion in three states during 2015, or every production order for a specific item with quantities and sorted by month. When you add advanced filtering into the mix, you can easily answer more complicated questions such as: Which Customers in the western region purchased a specific item in the last six months?


Catching errors in time is a continual challenge for most organizations. Cues provide a great way to “manage by exception”. A quick scan of your home page leads your eye to any cue with a yellow warning triangle – indicating a problem situation (Fig. 1). Clicking on the cue drills into the relevant form, directly accessing just the entities with the problem. The ability of advanced filtering to join different tables using complex criteria makes it possible to catch many very specific situations – like showing all sales order lines due to ship in the next week with no inventory and no production order in process.

Figure 1_stoll.png


Quite often reporting – especially using SQL/Excel – is used to monitor and manage processes. Sales reps want to see their sales orders due to ship in the next week, buyers want to see which PO lines have only partially been received, and engineers need to see which quotation lines need an engineering estimate. The Microsoft Dynamics AX cue functionality supports this extremely well and can eliminate the need for many Excel reports. The fact that it is an intrinsic part of Microsoft Dynamics AX makes it not only easier to use, but it also allows using the built-in automatic notifications via Microsoft Dynamics AX pop-up/email alerts. In some cases, the process requires calculation and analysis that the Microsoft Dynamics AX UI does not support; for example: Calculating month-end WIP value per production department or raw material usage by week per item number. For these situations, a combination of cues and Excel can be a simple and effective solution. Cue data can be exported to Excel (Ctrl-T) for ad hoc analysis, or it can be copied/pasted to predefined pivot table templates to generate reports.


The raw material usage per week report mentioned above is an illustrative example. A cue is created from the transactions table to generate the raw data for the report. The advanced filter is used to set the criteria to select the transactions that represent raw material being taken from the main warehouse (note that the physical date field contains the built-in function (DayRange(-90,0)) to always select the 90 days prior to today) (Fig. 2). An Excel template file is created with an empty pivot table using the same columns as the Microsoft Dynamics AX cue with an additional column to calculate the week number (in this case additional columns are added for a VLOOKUP of the product name, UOM, and buyer from a standard Microsoft Dynamics AX item list data connection; however, this is not required). The pivot table to show weekly usage is created in a separate sheet.

 Figure 2_Stoll.png

To generate this report, a User simply opens the cue and does a select all (Ctrl-A) and copy (Ctrl-C). Next the Excel template is opened, the data is pasted (Ctrl-V) on the bright yellow cell (Fig. 3), and refresh is picked from the pivot table ribbon bar. The User now has a report of raw material usage by week for the last 90 days (Fig. 4). To change the data set (different items or time period), the User can open the advanced filter on the cue (Ctrl-F3), make changes, and repeat the process – all with no IT resource required. Simple, flexible, and powerful.

Figure 3_Stoll.png

Figure 4_stoll.png



Welcome to the User Group for Dynamics 365 & AX [D365UG|AXUG], we’re so glad you’re here! While we realize you may be here to troubleshoot a technical issue or simply learn new tips and tricks, we’d love the chance to share with you the incredible benefits the User Group has to offer [and don’t forget, if you’re interested in an incredible in-person learning opportunity don’t forget to join us in Nashville for our Summit event]!