Creating Pivot Table Forms and Reports in AX

By - April 30, 2013

 

Many times in AX, users would like to manage their items in a matrix or dimensional grid.  I’ve used this technique many times in the past.  It is only now that I’ve decided to write about it.  I would like to see if I can perfect it so that it can easily be implemented for any scenario.  This walkthrough will be broken down into chunks so that you can continue to see progress as the product is developed.

 

Phase I:

It begins with a simple temporary table (look here for a great write up on how to use temp tables with forms and reports).  The primary index,  which allows duplicates of this table will be ItemId.  In addition, we’ll need the three generic data types: string, real, and date (about 20 fields each).   Name each field numerically so that they are all unique and easy to identify.

jsMatrix

Dynamics AX Matrix Style-pic1

 

Note: the screenshot shows the table is partially built with only five real numbers.  The final version of the table will be much larger.  The following job can help create all the generic fields in the table above.  Before running the job, start with a new table, jsMatrix, and drag the EDT ItemID as the first field.  In AX 2012, this will prompt the user to automatically create the index and relationship to InventTable.  The most important property setting for this table is to set it as temporary.  In AX 2012, this can be done by changing the table setting to TempDB.

Dynamics AX Matrix Style-TABLE 1

Job to build the temp table

Now that we have our temporary table built, we’ll need to create a method to populate it with data.  For now, we will keep it simple to test, and will modify it after we get the form and/or report working.  Add this method to the table:

Dynamics AX Matrix Style-TABLE 2

With the temporary table in place, we can simply add it to a form and/or report.  We will add in more functionality later.

Override the init method on the form:

Dynamics AX Matrix Style-TABLE 3

Override the fetch method on the report:

Dynamics AX Matrix Style-TABLE 4

And voila!  You have all you need for the generic matrix form.  It should look something like this:

Dynamics AX Matrix Style-pic2

 

Here are some examples of forms that I’ve replaced with this new functionality.

Original Purchase Order Create Line Form:

Dynamics AX Matrix Style-pic3

 

New Purchase Order Create Line Matrix:

Notice how I’ve added filters on the top for warehouse and ItemId.

Dynamics AX Matrix Style-pic4

 

Original Purchase Order

Dynamics-AX-Matrix-Style-pic5

 

New Purchase Order using the matrix:

With the report it’s easy to calculate subtotals and totals either by row or column.

Dynamics-AX-Matrix-Style-pic6

 

Original Sales Forecast form

Dynamics AX Matrix Style-pic7

 

New Forecast Grid using the matrix

Dynamics AX Matrix Style-pic8

 

 

 

Comments

Comments are closed here.

Receive Posts by Email

Subscribe to the Microsoft Dynamics blog and receive notifications of new posts by email.