Manage Financial Dimensions in MSDyn365FO with X++

With the arrival of the version 2012 of Microsoft Dynamics AX there was an important change in the way in which the ERP manages the Financial Dimensions. The data model changed significantly and with it, the way we, as developers, managed and worked with them through X++. Dynamics 365 for Finance and Operations has been a continuist in this topic, and the way we work with them is practically the same.

The main difference we found whit this change was that, while in the 2009 or earlier version we had limited the number of dimensions we could use in 10, in AX 2012 and MSDyn365FO that limit disappears. At a technical level, we used to work with an enum (SysDimension) to control the different dimensions, and an array EDT to store them, so if we wanted to access them, we have just to access to the array elements to get their values. Now, these dimensions are created directly in database, and the way they are created could remind us the way that inventory dimensions have been used historically on the ERP. What we do is store possible combinations of the different dimension values, and use a unique value (RecId) of this combination to assing the dimensions to the corresponding record.

This change obviously opens up a wide range of possibilities to exploit financial information through dimensions, but for technicians, it was a bit of a headache to learn to manage this new dimensions structure, which is why I want share with you a few methods that I use in all the projects in which I participate, and that make me forget about this management, greatly facilitating the use of these dimensions. You can download this class directly from my GitHub account and then I will explain how it works.

As I said, we used to manage the dimensions throug the SysDimensions enum, but now, we work with these dimensions through their name, which is given by the person who creates or modifies them, so the first thing we do is create an series of static constant variables that allow us to use the dimension names in any point of the application.
Important: If a dimension is added or modified at any time, it is mandatory to change its name in this class. The good point is that it will be centralized and the change will be minimal. Please, don’t use the name literal everywhere!!!

JATDimensionUtils

class JATDimensionUtils
{
    // Dimension names
    public static const DimensionRefFieldName CostCenterName	= "CostCenter";
    public static const DimensionRefFieldName BusinessUnitName	= "BusinesUnit";
    public static const DimensionRefFieldName DepartmentName	= "Department";
    public static const DimensionRefFieldName ProjectName	    = "Project";
    public static const DimensionRefFieldName CustomerName	    = "Customer";
    public static const DimensionRefFieldName VendorName	    = "Vendor";
    public static const DimensionRefFieldName WorkerName	    = "Worker";
    public static const DimensionRefFieldName CustomDimName	    = "Custom";
}

Thus, every time I want to use the name of a dimension, I will only have to use the following syntax:

JATDimensionUtils::CostCenterName

Now I am going to show you the different methods I have whithin this same class and that make my life easier.

GetDimensionAttributeValueSetId

This method will allow us to obtaine the DefaultDimension from a container with the dimensions that we want to associate to the record. It serves for example to associate dimensions to the account or the offset account in a journal line when the type of them are Vend, Cust, Bank…
Parameter: Container with the total number of dimensions, followed by the name and value of each one.
Return: RecId associated to the dimension combinaiton obtained by parameters.

public static RecId getDimensionAttributeValueSetId(container _dimensionValue, dataAreaId _dataAreaId = curext())
{
    RecId                               dimensionId;
    DimensionAttributeValueSetStorage   storage;
    DimensionAttribute                  dimensionAttribute;
    DimensionAttributeValue             dimensionAttributeValue;
    int                                 attributeCount, attributeIndex;
    str                                 attributeName, attributeValue;
    int                                 containerElementIndex;
    changecompany(_dataAreaId)
    {
        containerElementIndex = 1;
        storage = new DimensionAttributeValueSetStorage();
        // Get attribute count
        attributeCount = conPeek(_dimensionValue, containerElementIndex);
        containerElementIndex++;
        // Get attributes
        for (attributeIndex = 1; attributeIndex <= attributeCount; attributeIndex++)
        {
            // Get attribute name
            attributeName = conPeek(_dimensionValue, containerElementIndex);
            containerElementIndex++;
            // Validate the Financial Dimenion that was passed in.
            dimensionAttribute = DimensionAttribute::findByName(attributeName);
            // Get attribute value
            attributeValue = conPeek(_dimensionValue, containerElementIndex);
            containerElementIndex++;
            // Validate the Financial Dimenion Value that was passed in.
            dimensionAttributeValue = DimensionAttributeValue::findByDimensionAttributeAndValue(dimensionAttribute, attributeValue);
            // Add attribute
            storage.addItem(dimensionAttributeValue);
        }
        dimensionId = storage.save();
    }
    return dimensionId;
}

// Use of getDimensionAttributeValueSetId
Container   conDimension    = [2, JATDimensionUtils::CostCenterName, "CC1", JATDimensionUtils::DepartmentName, "DEP01"];

ledgerJournalTrans.DefaultDimension = JATDimensionUtils::getDimensionAttributeValueSetId(conDimension);

GetLedgerDimensionId

This method will allow us to obtain the LedgerDimension from a container with the accounting account (MainAccount) and the dimensions that we want to associate with the record. For example, it is used to associate dimensions to the account or offset account of a journal line when they are Ledger type.
Parameter: Container with the MainAccount, followed by the name and value of the dimension.
Return: RecId associated to the combination of account and dimensions obtained by parameters.

public static DimensionDynamicAccount getLedgerDimensionId(container _dimensionValue)
{
    DimensionStorage        dimensionStorage	= DimensionStorage::construct(0, LedgerDimensionType::Account);
    DimensionAttributeValue dimAttributeValue;
    DimensionStorageSegment dimensionStorageSegment;
    DimensionHierarchyLevel	dimHierarchyLevel;
    MainAccount		        mainAccount;
    recid			        dimHierarchyId;
    recid			        mainAccountRecId;
    DimensionValue          dimensionValue;
    container		        dimensions;
    
    for(int i = 1; i <= conLen(_dimensionValue); i++)
    {
        try
        {
            dimensionValue = conPeek(_dimensionValue, i);

            if(i == 1) // MainAccount
            {
                dimAttributeValue	= DimensionAttributeValue::findByDimensionAttributeAndValueNoError(DimensionAttribute::findByName("MainAccount"), dimensionValue, false, true);
                mainAccountRecId	= DimensionAttributeValue::find(dimAttributeValue.RecId).EntityInstance;
                dimHierarchyId	    = DimensionHierarchy::getAccountStructure(mainAccountRecId);
                dimensionStorage.addHierarchy(dimHierarchyId);

                while select DimensionAttribute, Level
                    from dimHierarchyLevel order by Level
                    where dimHierarchyLevel.DimensionHierarchy == dimHierarchyId
                {
                    dimensions		+= DimensionAttribute::find(dimHierarchyLevel.DimensionAttribute).Name;
                }

                dimensionStorageSegment	= DimensionStorageSegment::constructFromValue(dimAttributeValue.CachedDisplayValue,
                                                                                    dimAttributeValue);
                dimensionStorage.setSegment(i, dimensionStorageSegment);
            }
            else
            {
                // Rest of dimensions
                if(dimensionValue)
                {
                    dimAttributeValue = DimensionAttributeValue::findByDimensionAttributeAndValueNoError(DimensionAttribute::findByName(conPeek(dimensions, i)),
                                                                                                        dimensionValue,
                                                                                                        false,
                                                                                                        true);
                    if (!dimAttributeValue)
                    {
                        // @JAT:DimensionNotFound = The value '%1' of the dimension '%2' does not exist.
                        throw error(strFmt("@JAT:DimensionNotFound", dimensionValue, conPeek(dimensions, i)));
                    }

                    dimensionStorageSegment = DimensionStorageSegment::constructFromValue(dimAttributeValue.CachedDisplayValue, dimAttributeValue);
                    dimensionStorage.setSegment(i, dimensionStorageSegment);
                }
                else
                {
                    dimensionStorageSegment = DimensionStorageSegment::emptySegment();
                    dimensionStorage.setSegment(i, dimensionStorageSegment);
                }
            }
        }
        catch
        {
            return 0;
        }
    }
    
    return dimensionStorage.save();
}

// Use of getLedgerDimensionId
Container   conDimension    = ["705001", JATDimensionUtils::CostCenterName, "CC1", JATDimensionUtils::DepartmentName, "DEP01"];

ledgerJournalTrans.LedgerDimension  = JATDimensionUtils::getLedgerDimensionId(conDimension);

ChangeDimensionValue

This method will allow us to modify the values of some of the dimensions that are already associated to the record.
Parameter: RecId of the dimensions that the record has already associated and Container with the Name and Value pairs of the dimensions that you want to modify.
Return: RecId of the combination of old dimensions with the new values associated to the record.

public static RecId changeDimensionValue(DimensionDefault _defaultDimension, container _dimensionValue) 
{
    DimensionAttributeValueSetStorage	dimensionAttributeValueSetStorage;
    DimensionAttribute			        dimensionAttribute;
    DimensionValue				        oldDimensionValue;
    DimensionValue				        newDimensionValue;
    DimensionDefault			        newDimensionDefault;
    DimensionRefFieldName			    dimensionName;
    int					                i = 1;

    while (i <= conLen(_dimensionValue))
    {
        dimensionName	= conPeek(_dimensionValue, i);
        i++;
        newDimensionValue	= conPeek(_dimensionValue, i);
        i++;
        // Get current value
        oldDimensionValue = JATDimensionUtils::getDimensionValue(_defaultDimension, dimensionName);
        // Build DimensionAttributeValueSetStorage
        dimensionAttributeValueSetStorage = DimensionAttributeValueSetStorage::find(_defaultDimension);

        // Remove old dimension value
        dimensionAttribute = DimensionAttribute::findByName(dimensionName);
        dimensionAttributeValueSetStorage.removeDimensionAttributeValue(DimensionAttributeValue::findByDimensionAttributeAndValue(dimensionAttribute, oldDimensionValue).RecId);

        // Set new dimension value
        if(newDimensionValue != "")
        {
            dimensionAttribute = DimensionAttribute::findByName(dimensionName);
            dimensionAttributeValueSetStorage.addItem(DimensionAttributeValue::findByDimensionAttributeAndValue(dimensionAttribute, newDimensionValue));
        }

        _defaultDimension = dimensionAttributeValueSetStorage.save();
    }
    return _defaultDimension;
}

// Use of changeDimensionValue
Container   conDimension    = [JATDimensionUtils::CostCenterName, "CC1", JATDimensionUtils::DepartmentName, "DEP01"];

projTable.DefaultDimension  = JATDimensionUtils::changeDimensionValue(projTable.DefaultDimension, conDimension);

GetDimensionValue

This method will allow us to obtain the value of an specific dimension associated to the record.
Parameter: RecId of the combination of dimensions associated to the record and name of the dimension we want to obtain.
Return: Value of the required dimension.

public static DimensionValue getDimensionValue(DimensionDefault _dimensionDefault, DimensionRefFieldName _dimensionName)
{
    DefaultDimensionView defaultDimensionView;
    
    select firstonly DisplayValue
        from defaultDimensionView
        where defaultDimensionView.Name			                == _dimensionName
            && defaultDimensionView.DefaultDimension	== _dimensionDefault;
    return defaultDimensionView.DisplayValue;
}

// Use of getDimensionValue
DimensionValue  dimValue;

dimValue    = JATDimensionUtils::getDimensionValue(custTable.DefaultDimension, JATDimensionUtils::CostCenterName);

CheckDimensionValue

With this method we can validate if a specific value exists in a specific dimension, no matters if the dimension is standard or custom.
Parameter: Name and value of the dimension we want to validate.
Return: Boolean that indicates if the value exists or not in the given dimension.

public static boolean checkDimensionValue(DimensionRefFieldName _dimensionName, DimensionValue _dimensionValue)
{
    DimensionAttributeDirCategory   dimAttributeDirCategory;
    DimensionAttribute		        dimensionAttribute;
    DimAttributeOMCostCenter	    dimAttributeOMCostCenter;
    DimAttributeOMBusinessUnit	    dimAttributeOMBusinessUnit;
    DimAttributeOMDepartment	    dimAttributeOMDepartment;
    DimensionFinancialTag		    dimensionFinancialTag;
    DimAttributeCustTable		    dimAttributeCustTable;
    DimAttributeVendTable		    dimAttributeVendTable;
    DimAttributeProjTable		    dimAttributeProjTable;
    DimAttributeHcmWorker		    dimAttributeHcmWorker;
    boolean				            ret = true;
    
    switch (_dimensionName)
    {
        case JATDimensionUtils::CustomDimName:
            dimensionAttribute	= DimensionAttribute::findByName(_dimensionName);
            select firstOnly RecId 
                from dimAttributeDirCategory
                    where dimAttributeDirCategory.DimensionAttribute == dimensionAttribute.RecId
                join dimensionFinancialTag
                    where dimensionFinancialTag.FinancialTagCategory == dimAttributeDirCategory.RecId
                        && dimensionFinancialTag.Value		 == _dimensionValue;

            if (!dimAttributeDirCategory.RecId)
            {
                ret = checkFailed(strFmt("@JAT:DimensionNotFound", _dimensionValue, _dimensionName)); 
            }
            break;

        case JATDimensionUtils::BusinessUnitName:
            select firstonly RecId
                from dimAttributeOMBusinessUnit
                where dimAttributeOMBusinessUnit.Value	== _dimensionValue;
            if (!dimAttributeOMBusinessUnit.RecId)
            {
                ret = checkFailed(strFmt("@JAT:DimensionNotFound", _dimensionValue, _dimensionName));
            }
            break;

        case JATDimensionUtils::CostCenterName:
            select firstonly RecId
                from dimAttributeOMCostCenter
                where dimAttributeOMCostCenter.Value	== _dimensionValue;
            if (!dimAttributeOMCostCenter.RecId)
            {
                ret = checkFailed(strFmt("@JAT:DimensionNotFound", _dimensionValue, _dimensionName));
            }
            break;

        case JATDimensionUtils::DepartmentName:
            select firstonly RecId
                from dimAttributeOMDepartment
                where dimAttributeOMDepartment.Value	== _dimensionValue;
            if (!dimAttributeOMDepartment.RecId)
            {
                ret = checkFailed(strFmt("@JAT:DimensionNotFound", _dimensionValue, _dimensionName));
            }
            break;

        case JATDimensionUtils::ProjectName:
            select firstonly RecId
                from dimAttributeProjTable
                where dimAttributeProjTable.Value	== _dimensionValue;
            if (!dimAttributeProjTable.RecId)
            {
                ret = checkFailed(strFmt("@JAT:DimensionNotFound", _dimensionValue, _dimensionName));
            }
            break;

        case JATDimensionUtils::CustomerName:
            select firstonly RecId
                from dimAttributeCustTable
                where dimAttributeCustTable.Value	== _dimensionValue;
            if (!dimAttributeCustTable.RecId)
            {
                ret = checkFailed(strFmt("@JAT:DimensionNotFound", _dimensionValue, _dimensionName));
            }
            break;

        case JATDimensionUtils::VendorName:
            select firstonly RecId
                from dimAttributeVendTable
                where dimAttributeVendTable.Value	== _dimensionValue;
            if (!dimAttributeVendTable.RecId)
            {
                ret = checkFailed(strFmt("@JAT:DimensionNotFound", _dimensionValue, _dimensionName));
            }
            break;

        case JATDimensionUtils::WorkerName:
            select firstonly RecId
                from dimAttributeHcmWorker
                where dimAttributeHcmWorker.Value	== _dimensionValue;
            if (!dimAttributeHcmWorker.RecId)
            {
                ret = checkFailed(strFmt("@JAT:DimensionNotFound", _dimensionValue, _dimensionName));
            }
            break;
            
        default:
            ret = checkFailed(strFmt("@JAT:DimensionNotFound", _dimensionValue, _dimensionName));
            break;
    }
    
    return ret;
}

// Use of checkDimensionValue
ret = JATDimensionUtils::checkDimensionValue(JATDimensionUtils::CostCenterName, "CC01");

And so far my little help for working with dimensions. Any correction, contribution, idea or comment will be more than welcome. Regards!