Last week I conducted a NAV Development update to the development team of a Dutch NAV partner. One of the topics we touched was the One-Page Matrix introduced in NAV 2009 SP1. The What's New in Microsoft Dynamics NAV 2009 Service Pack 1 - Installation and Development manual contains a lab that lets you build a One-Page matrix, but the end result is not complete and the overview (what am I doing exactly?) is a bit missing. Therefor I decided to try my best and make up some kind of step-by-step recipe for this (see below).
It's good to realize that a matrix is a very useful mean to show the relation between two entities (i.e. tables in NAV). In other words: to see what these two have in common. As in the screen below it might be of interest to get insight in what items our customers have ordered (not yet invoiced/shipped).
This relation between two entities (in our example Customer and Item) is to be found in a third entity being the Sales Line.
From a mathematical perspective a matrix is a 2-dimensional image, with the one entity (Customer) along the X-axis and the other (Item) along the Y-axis. Any combination of X and Y, being a cell in our matrix, represents the relation between this two specific instances of these two entities.
Knowing your math you might ask if we could create a 3-dimensional matrix. No and Yes (or Jein in German),
... if we image ourselves something like a Rubik's cube.
... if we would add a filter to the matrix where it would allow us to show (for a specific filter value) a specific intersection of a 3-dimensional matrix.
At a high level my step-by-step recipe looks like this:
And adding all relevant details:
In this first step we setup the first part of the skeleton for our One-Page matrix: i.e. the page structure for the mutli-columns subpage that will display the vertical and horizontal dimensions and their intersecting matrix cells. We create this subpage by using the wizard as introduced in NAV 2009 SP1:
In this second step we setup the second part of the skeleton for our One-Page matrix: i.e. the page structure for the main page including FastTab(s) and subpage placeholder.
We create this main page by also using the wizard:
To this we need to manually add MatrixForm Part that links to matrix subpage, i.e. set PagePartID
Now that we have defined the vertical dimension, we need to create controls (i.e. columns) to define the horizontal dimension. For this we add columns to matrix subpage by a simple copy & paste from an existing matrix subpage:
To get the object compiled:
To be able to control the content of the column headings (horizontal dimension) from the main page we need to declare the function SetColumns (copy from PAG491). A call to SetColumns should be made from the OnOpenPage trigger (see PAG491) to initiate the headings content when opening the page.
Now that we have created a function to control the column headings from the main page, we also need an interface to exchange the result from main to subpage, i.e. a function called Load (copy from PAG9231).
Having set the vertical and horizontal dimensions we should now calculate the content of the crossings/intersections of the vertical and horizontal dimensions (on matrix subpage) coding OnAfterGetRecord (copy from PAG9231). For this we need to determine number of columns being used in the OnOpenPage trigger.
In order to have only relevant cells (i.e. columns) shown we set the columns visibility variables to TRUE (OnInit - copy from PAG9231) and declare function SetVisible (copy from PAG9231) to allow us to dynamically update the visibility.
See PAG9231 for example, but code depends on the specifics of our matrix
All code set to update the dimensions and intersecting matrix cells. Now one function is needed that will pass the updates from main to subpage: UpdateMatrixSubform (copy from PAG213 (One-Page matrix!)). Note that the call of Load function (on subpage) should be updated to fit the definition of Load on subpage and should be first called when opening page (OnOpenPage).
The only remaining major part of our One-Page matrix is the set of buttons that alow us to browse through the horizontal dimension:
Now the matrix is doing quite well, but to make it complete we have some attributes left:
Note that these attributes can be specific to each matrix page.
Using the Windows Script Host Object Model will ask the user if this call to this external component is allowed:
Of course NAV has a number of matrices that have most of the above in common. However they all have some differences too. Have a look at them to learn from these differences. The list below show the main pages for the standard One-Page matrices in NAV 2009 SP1
Res. Group Capacity
MyDim Value Combinations (previously form 539)
Purchase Budget Overview
Sales Budget Overview
Hellow. I would like to know how to do a filter to head of columns (Cod of items in your example). Could you help me? Thanks