PaulsenConsult.com, Microsoft Dynamics NAV Consulting
PaulsenConsult.com
 PaulsenConsult.com

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.

Dynamics NAV - Projects Dynamics NAV - Projects

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.

Dynamics NAV - Budget Codes Dynamics NAV - Budget Codes

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.

Dynamics NAV - Dimension Combinations Dynamics NAV - Dimension Combinations

In our example we want to limit the posting of budget codes to certain projects.

Right-click into the word Limited and then select DrillDown.

The Dimension Value Combinations window opens.

Dynamics NAV - Dimension Value Combinations Dynamics NAV - Dimension Value 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

Dynamics NAV - Object Designer - Table 349 Dimension Value Dynamics NAV - Object Designer - Table 349 Dimension Value

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.

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

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

Dynamics NAV - Table 349 Dimension Value - Field 50000 Dynamics NAV - Table 349 Dimension Value - Field 50000

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

 

Dynamics NAV - Table Relation Dynamics NAV - 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.

Dynamics NAV - Page 537 Dimension Values Dynamics NAV - Page 537 Dimension Values

 

Hierarchical Dimensions - Data Hierarchy

Dynamics NAV - Budget Codes with Project Dynamics NAV - 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.

Dynamics NAV - Object Designer - Codeunit 50006 Dynamics NAV - Object Designer - Codeunit 50006

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.

Dynamics NAV - Codeunit 409 Dimension Value-Indent Dynamics NAV - Codeunit 409 Dimension Value-Indent
Dynamics NAV - Indent Dimension Values 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.

Dynamics NAV - Purchase Invoice - Budget Code Lookup Dynamics NAV - Purchase Invoice - Budget Code Lookup

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.

Dynamics NAV - Codeunit 408 DimensionManagement Dynamics NAV - Codeunit 408 DimensionManagement

Table 39 Purchase Line gets a new function LookupShortcutDim3Code.

Dynamics NAV - Table 39 Purchase Line Dynamics NAV - Table 39 Purchase Line

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

Dynamics NAV - Page 55 Purch. Line Subform Dynamics NAV - Page 55 Purch. Line Subform

 

Result of a Limited Lookup for Hierarchical Dimensions

 

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

Dynamics NAV - Purchase Invoice - Budget Code Lookup Dynamics NAV - Purchase Invoice - Budget Code 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

Contact

PaulsenConsult.com
4300 Jog Road #541824

Green Acres, FL 33454


Phone: +1 561 275-9519+1 561 275-9519

E-mail: Thomas@PaulsenConsult.com

Print Print | Sitemap
©PaulsenConsult.com Microsoft Dynamics NAV and Microsoft Navision are registered trade marks of Microsoft Corporation