Hierarchical NAV Dimensions

Microsoft Dynamics NAV offers a functionality called Dimensions. Dimensions allow you to define data you want to capture with your transactions and report on.

Microsoft Dynamics NAV is the most flexible ERP system on the market, and Dimensions are one of the features providing flexibility.

One of the most popular uses of this functionality is to set up a Dimension that serves as Sub-G/L Account. In this use, the Dimension is thought to be in a hierarchy below the G/L Account.

This presentation discusses a software modification that allows you to define a hierarchical ranking between two Dimensions. One Dimension serves as a Main Dimension, the other Dimension will be a Sub-Dimension to the Main Dimension. T

Suppose your company uses a PROJECT dimension in Dynamics NAV to record project data.

Microsoft Dynamics NAV - Dimension Values- Project
Microsoft Dynamics NAV – Dimension Values – Project

Your company also uses a third dimension BUDGET to track budget data. You intend to budget within projects.

In the General Ledger Setup, Project is defined as second dimension. Budget is defined as third Dimension. This numbering does not constitute a hierarchy. The third Dimension is not subordinated to the second Dimension.

Microsoft Dynamics NAV - Dimension Values - Budget
Microsoft Dynamics NAV – Dimension Values – Budget

For every project, a distinct set of budget codes has been set up.Budget codes starting with the letter B belong to the Boston project. Budget codes starting with the letter P belong to the project of the development of a new product line. Budget codes starting with the letter S belong to the project of setting up a subsidiary in Brazil.

This lettering is a logic assignment of budget codes to projects. The program does not recognize this assignment and cannot check postings for correct combinations of project and budget codes.

It is possible that a bookkeeper posts an invoice entering project BOSTON and the Brazil Budget code S0220 Installation.

The posted invoice contains an impossible combination of project and budget code. It is cumbersome to reverse the invoice and enter it again. 

The system should be set up so that it recognizes impossible combinations of projects and budget codes and does not allow the posting of transactions with such combinations.

Dimension Combinations

Under Financial Management, Administration you can find Dimension Combinations. Here you can establish limits for the posting of dimensions.

Microsoft Dynamics NAV - Dimension Combinations
Microsoft Dynamics NAV – Dimension Combinations

Here you can establish which project should be allowed to be posted with which budget code. You do that by blocking all other combinations.

You quickly realize this spreadheet will be too big for your to fill out and to maintain manually.

The system should be modified in a way so that you would fill out the permissible combinations rather than the non-permissible combinations.

Hierarchical Dimensions – Table Design

Open the Object Designer and find Table 349 Dimension Value

Microsoft Dynamics NAV - Object Designer
Microsoft Dynamics NAV – Object Designer

Open Table 349 Dimension Value in Design Mode and enter a remark into the Documentation() trigger that serves as a record of your software change.

Microsoft Dynamics NAV - Table Designer - Table 349 Dimension Value
Microsoft Dynamics NAV – Table Designer – Table 349 Dimension Value

Add a new field 50000 Project Code to the table.

The new field has the Data Type Code and the Length 20. The Description PC766 gives later programmers an idea where this field came from.

Microsoft Dynamics NAV - Project Code - Properties
Microsoft Dynamics NAV – Project Code – Properties

Give Field 50000 Project Code a Table Relation to itself, Table 349 Dimension Value.

Microsoft Dynamics NAV - Dimension Value - Table Relation
Microsoft Dynamics NAV – Dimension Value – Table Relation

The TableRelation has a Table Filter for the Global Dimension Value 2, which in our database has been defined as PROJECT.

Hierarchical Dimensions – Page Design

To make the new field visible, it has to be added to Page 537 Dimension Values. Open Page 537 in the Object Designer and add a new line of Type Field. Type “Project Code” into the Field SourceExpr. The system will then fill the other fields.

Microsoft Dynamics NAV- Page Designer - Dimension Values
Microsoft Dynamics NAV- Page Designer – Dimension Values

Hierarchical Dimensions – Data Hierarchy

Microsoft Dynamics NAV - Dimension Values - Budget Codes with Project
Microsoft Dynamics NAV – Dimension Values – Budget Codes with Project

The page Dimension Values now shows a new column Project Code where you enter the Project Code a Budget Code should belong to.

Your database now contains a relationship between Project and Budget Code.

Now we need to set up a control mechanism that enforces this relationship when posting transactions.

Refreshing Hierarchical Dimensions

We designed a new Codeunit 50006 Dimension 3 Management which reads the assignment of Budget Codes to Projects and then blocks all other Dimension Value Combinations.

Microsoft Dynamics NAV - Object Designer - Codeunit 50006 Dimension 3 Management
Microsoft Dynamics NAV – Object Designer – Codeunit 50006 Dimension 3 Management

After a user edits any Budget Codes, Codeunit 50006 must be called.

When editing Budget Codes it is recommended to call the Dimension Value Indent function, because it ensures the totaling formulas are correct.

You can easily connect the blocking of Dimension Value Combinations to the indentation of Dimension Values. To this end Codeunit 409 Dimension Value-Indent has been modified.

Microsoft Dynamics NAV - C/AL Editor - Codeunit 409 Dimension Value-Indent
Microsoft Dynamics NAV – C/AL Editor – Codeunit 409 Dimension Value-Indent
Microsoft Dynamics NAV - Indent Dimension Values
Microsoft Dynamics NAV – Indent Dimension Values

As a result of this software modification the system refreshes the Dimension Value Combinations every time you indent Dimension Values. Now a Budget Code can only be posted with an allowed Project Code.

As you can see in this picture, Budget Code B0220 Installation is connected to Project Code BOSTON.

If the bookkeeper working at an invoice makes a mistake and enters another Project Code like SBRAZIL, the system does not post such an invoice and generates an error message.

Lookup for Hierarchical Dimensions

What we have accomplished so far with our software modification is that the system does not allow postings of transactions with wrong combinations of Budget Codes and Project Codes.

Now we want to make the bookkeeper’s live easier. When entering data into a purchase invoice, only Budget Codes belonging to the selected Project should be listed.

Microsoft Dynamics NAV - Purchase Invoice
Microsoft Dynamics NAV – Purchase Invoice

In this example invoice, Project Code BOSTON has been selected. When the bookkeeper clicks on the lookup arrow in field Budget Code, only the Boston Budget Codes should be listed.

Designing a Lookup for Hierarchical Dimensions

To limit the lookup of Budget Codes to the corresponding Projects, new pieces of software have to be added in three different places of the application.

Codeunit 408 DimensionManagement gets a new function LookupDim3ValueCode.

Microsoft Dynamics NAV - Codeunit 408 Dimension Management
Microsoft Dynamics NAV – Codeunit 408 Dimension Management

Table 39 Purchase Line gets a new function LookupShortcutDim3Code.

Microsoft Dynamics NAV - C/AL Editor - Table 39 Purchase Line
Microsoft Dynamics NAV – C/AL Editor – Table 39 Purchase Line

In Page 55 Purch. Invoice Subform we replace the standard lookup function call with our own call.

Microsoft Dynamics NAV - C/AL Editor - Page 55 Purchase Invoice Subform
Microsoft Dynamics NAV – C/AL Editor – Page 55 Purchase Invoice Subform

Result of a Limited Lookup for Hierarchical Dimensions

The result of our software modification can now be seen in the purchase invoice:

Microsoft Dynamics NAV - Purchase Invoice - Dimension Lookup
Microsoft Dynamics NAV – Purchase Invoice – Dimension Lookup

The bookkeeper selected Project BOSTON in the invoice line. If he now clicks the lookup arrow in field Budget Code, only the Budget Codes belongin to the Boston Project will be listed.

This helps prevent data entry errors and makes the live of the bookkeeper easier.

Author: Thomas Paulsen

Published: 12-Jun-2012