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
|
1
2
3
4
5
6
7
8
9
10
11
12
|
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:
|
1
|
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.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
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;
}
|
|
1
2
3
4
|
// 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.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
|
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();
}
|
|
1
2
3
4
|
// 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.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
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;
}
|
|
1
2
3
4
|
// 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.
|
1
2
3
4
5
6
7
8
9
10
|
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;
}
|
|
1
2
3
4
|
// 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.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
|
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;
}
|
|
1
2
|
// 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
posted on 2021-11-10 12:49 lingdanglfw 阅读(50) 评论(0) 编辑 收藏 举报
