Work with Financial dimensions [AX2012, X++]
NOTES: All codes in this document based on CU12.
Disclaimer:
All the programming examples in this article are for illustration purposes only. Microsoft disclaims all warranties and conditions with regard to use of the programming example for other purposes. Microsoft shall not, at any time, be liable for any special, direct, indirect or consequential damages, whether in an action of contract, negligence or other action arising out of or in connection with the use or performance of the programming example. Nothing herein should be construed as constituting any kind of warranty.
From AX2012 we got a more flexible financial dimension feature. We can do more settings and customization on it to meet our requests. In this article, we will discuss following topics:
- How to add Financial dimensions’ control on a form.
- How to add Financial dimensions’ filter in a query.
- How Financial dimensions stored.
- How to create and modify a financial dimension.
- Tips for Financial dimensions.
The Financial dimensions here we are talking include Default dimension and Ledger dimension (account and dimension segment combination that used in ledger journal).
Default dimension:
Ledger dimension:
- How to add Financial dimensions’ control on a form.
-
Default dimension
We have a table called FinancialDimensionDemo and it has a field call DefaultDimension which extends from EDT DimensionDefault.
We created a form called FinancialDimensionDemo and want add default dimension control for field DefaultDimension.
-
Add a tab page or group for show Default dimensions. And set AutoDeclaration to yes.
-
Define an object of class DimensionDefaultingController in form’s classDeclaration method.
public
class FormRun extends ObjectRun
{
DimensionDefaultingController dimensionDefaultingController;
}
-
Override and add following code in form’s init method to initialize DimensionDefaultingController object.
public
void init()
{
super();
dimensionDefaultingController = DimensionDefaultingController::constructInTabWithValues(false, true, true, 0, this, TabFinancialDimensions, “@SYS138491”);
dimensionDefaultingController.parmAttributeValueSetDataSource(FinancialDimensionDemo_DS, fieldStr(FinancialDimensionDemo, DefaultDimension));
dimensionDefaultingController.parmValidateBlockedForManualEntry(true); //Specifies whether validation should be enforced for dimension values marked as not allowing manual entry. The default value is false.
}
-
Open the form
-
Ledger Dimension
We have a table called FinancialDimensionDemo and it has 2 fields called LedgerDimension which extends from EDT DimensionDynamicAccount, and AccountType which extends from EDT LedgerJournalACType.
And table has following relation. If you create field by drag EDT, the relation will be created automatically.
We created a form called FinancialDimensionDemo and want add ledger dimension control for field LedgerDimension.
-
Drag AccountType and LedgerDimension fields from form Data sources to following grip.
Set AutoDeclaration property of FinancialDimensionDemo_LedgerDimension as Yes.
-
Go to define an object for class DimensionDynamicAccountController.
DimensionDynamicAccountController dimensionDynamicAccountController;
-
Override init method and add following code to init object of DimensionDynamicAccountController
dimensionDynamicAccountController = DimensionDynamicAccountController::construct(FinancialDimensionDemo_DS, fieldStr(FinancialDimensionDemo, LedgerDimension), fieldStr(FinancialDimensionDemo, AccountType));
dimensionDynamicAccountController.parmDimensionAccountStorageUsage(DimensionAccountStorageUsage::Transactional);
dimensionDynamicAccountController.parmPostingType(LedgerPostingType::LedgerJournal);
dimensionDynamicAccountController.parmValidateBlockedForManualEntry(true);//Specifies whether validation should be enforced for dimension values marked as not allowing manual entry. The default value is false.
-
Override following method of datasouce field LedgerDimension
public Common resolveReference(FormReferenceControl _formReferenceControl)
{
Common common = dimensionDynamicAccountController.resolveReference();
return common;
}
-
Override following methods of the SegmentedEntry Control FinancialDimensionDemo_LedgerDimension.
public
boolean validate()
{
boolean isValid;
isValid = super();
isValid = dimensionDynamicAccountController.validate() && isValid;
return isValid;
}
public
void segmentValueChanged(SegmentValueChangedEventArgs _e)
{
super(_e);
dimensionDynamicAccountController.segmentValueChanged(_e);
}
public
void loadSegments()
{
super();
dimensionDynamicAccountController.parmControl(this);
dimensionDynamicAccountController.loadSegments();
}
public
void loadAutoCompleteData(LoadAutoCompleteDataEventArgs _e)
{
super(_e);
dimensionDynamicAccountController.loadAutoCompleteData(_e);
}
public
void jumpRef()
{
dimensionDynamicAccountController.jumpRef();
}
-
Open form
- How to add Financial dimensions’ filter in a query
-
Default Dimension
Use following methods to filer default dimension
SysQuery::addDimensionAttributeRange()
SysQuery::addDimensionAttributeFilter()
static
void searchDefaultDimension(Args _args)
{
Query q;
QueryRun qr;
QueryBuildDataSource qbds;
FinancialDimensionDemo financialDimensionDemo;
//q = new Query(queryStr(FinancialDimensionDemoQ));
q = new Query();
qbds = q.addDataSource(tableNum(FinancialDimensionDemo));
//SysQuery::clearDimensionRangesFromQuery(q); //Clear exists filter value.
SysQuery::addDimensionAttributeRange(q,
qbds.name(),//’FinancialDimensionDemo’,
‘DefaultDimension’,
DimensionComponent::DimensionAttribute,
queryRange(‘001’, ‘004’),
‘Cashflow’);
//SysQuery::addDimensionAttributeFilter(q,
//qbds.name(),//’FinancialDimensionDemo’,
//’DefaultDimension’,
//DimensionComponent::DimensionAttribute,
//’001′,
//’Cashflow’);
qr = new QueryRun(q);
while(qr.next())
{
financialDimensionDemo = qr.get(tableNum(FinancialDimensionDemo));
info(financialDimensionDemo.JournalId);
}
}
-
Ledger Dimension
Use following methods to filer default dimension
SysQuery::addDimensionAttributeRange()
SysQuery::addDimensionAttributeFilter()
static
void searchLedgerDimensionByDimAttribute(Args _args)
{
Query q;
QueryRun qr;
QueryBuildDataSource qbds;
FinancialDimensionDemo financialDimensionDemo;
//q = new Query(queryStr(FinancialDimensionDemoQ));
q = new Query();
qbds = q.addDataSource(tableNum(FinancialDimensionDemo));
SysQuery::addDimensionAttributeRange(q,
qbds.name(),//queryBuildDataSource.name()
‘LedgerDimension’,//fieldStr(GeneralJournalAccountEntry, LedgerDimension)
DimensionComponent::DimensionAttribute,
queryRange(‘100000’, ‘110150’),
‘MainAccount’);//DimensionAttribute::find(DimensionAttribute::getMainAccountDimensionAttribute()).Name);
SysQuery::addDimensionAttributeRange(q,
qbds.name(),//queryBuildDataSource.name()
‘LedgerDimension’,//fieldStr(GeneralJournalAccountEntry, LedgerDimension)
DimensionComponent::DimensionAttribute,
‘004’,
‘Cashflow’);//DimensionAttribute::find(dimAttrId).Name
qr = new QueryRun(q);
while(qr.next())
{
financialDimensionDemo = qr.get(tableNum(FinancialDimensionDemo));
info(financialDimensionDemo.JournalId);
}
}
static
void searchWithLedgerDimension(Args _args)
{
Query q;
QueryRun qr;
QueryBuildDataSource qbds;
FinancialDimensionDemo financialDimensionDemo;
//q = new Query(queryStr(FinancialDimensionDemoQ));
q = new Query();
qbds = q.addDataSource(tableNum(FinancialDimensionDemo));
SysQuery::addDimensionAttributeRange(q,
qbds.name(),
‘LedgerDimension’,
DimensionComponent::LedgerDimensionDisplayValue,
‘*-*-025-*’);//,
//’??’);
qr = new QueryRun(q);
while(qr.next())
{
financialDimensionDemo = qr.get(tableNum(FinancialDimensionDemo));
info(financialDimensionDemo.JournalId);
}
}
- How Financial dimensions stored
[Tables]
DimensionAttribute: This table stores financial dimensions.
DimensionAttributevalue: This table stores the value of the financial dimension. There is a EntityInstance field in this table. That’s the relation to the value original table.
FinancialTagCategory: This table stores record of custom financial dimension.
DimensionFinancialTag: this table stores custom financial dimensions value.
[Views]
DimAttribute*: These views can show you the dimension value and it’s backing record.
-
Default dimension
[Tables]
DimensionAttributeValueSet: this table stores the record of the default dimension.
DimensionAttributeValueSetItem: this table stores the attribute value of the default dimension.
[Views]
DefaultDimensionView
DimensionAttributeValueSetItemView
static
void getSpecifiedDefaultDimension(Args _args)
{
DimensionAttributeValueSetItem dimensionAttributeValueSetItem;
DimensionAttributeValue DimensionAttributevalue;
DimensionAttribute dimensionAttribute;
select
firstOnly dimensionAttributeValueSetItem
where dimensionAttributeValueSetItem.DimensionAttributeValueSet == 52565471266
join DimensionAttributevalue
where DimensionAttributevalue.RecId == dimensionAttributeValueSetItem.DimensionAttributeValue
join dimensionAttribute
where dimensionAttribute.RecId == DimensionAttributevalue.DimensionAttribute
&& dimensionAttribute.Name == ‘Department’;
print dimensionAttributeValueSetItem.DisplayValue;
pause;
}
-
Ledger dimension
[Tables]
DimensionAttributeValueCombination: Stores combination of ledger dimension
DimensionAttributeValueGroup: Stores dimension group
DimensionAttributeValueGroupCombination: Store relation of DimensionAttributeValueGroup and DimensionAttributeValueCombination
DimensionAttributeLevelValue: Stores dimension value of ledger dimension
[Views]
DimensionAttributeLevelValueAllView
DimensionAttributeLevelValueView
static
void getSpecifiedLedgerDimension(Args _args)
{
DimensionAttributeValueGroupCombination dimensionAttributeValueGroupCombination;
DimensionAttributeLevelValue dimensionAttributeLevelValue;
DimensionAttributeValue dimensionAttributeValue;
BankAccountTable bankAccountTable;
while
select dimensionAttributeValueGroupCombination
where dimensionAttributeValueGroupCombination.DimensionAttributeValueCombination == 52565574540//ledgerDimension
join dimensionAttributeLevelValue order
by Ordinal asc
where dimensionAttributeLevelValue.DimensionAttributeValueGroup == dimensionAttributeValueGroupCombination.DimensionAttributeValueGroup
join dimensionAttributeValue
where dimensionAttributeValue.RecId == dimensionAttributeLevelValue.DimensionAttributeValue
//Specified dimension
//join DimensionAttribute
//where DimensionAttribute.name == ‘CostCenter’
// && DimensionAttribute.RecId == dimensionAttributeValue.DimensionAttribute
{
//Back entity table
//if (dimensionAttributeValue.getEntityInstance().TableId == tableNum(DimAttributeBankAccountTable))
//{
//bankAccountTable = BankAccountTable::find(dimensionAttributeValue.getName());//dimensionAttributeLevelValue.displayValue
//break;
//}
info(dimensionAttributeLevelValue.DisplayValue);
}
}
- How to create or modify a financial dimension.
-
Default dimension
[Create]
static
void CreateDefaultDimension(Args _args)
{
Struct struct = new Struct();
container defaultDimensionCon;
DimensionDefault dimensionDefault;
;
dimensionDefault = AxdDimensionUtil::getDimensionAttributeValueSetId([2,‘BusinessUnit’, ‘001’,
‘CostCenter’, ‘007’]);
dimensionDefault = AxdDimensionUtil::getDimensionAttributeValueSetId([3,‘BusinessUnit’, ‘001’,
‘CostCenter’, ‘007’,
‘Department’, ‘022’]);
struct.add(‘BusinessUnit’, ‘001’);
struct.add(‘CostCenter’, ‘007’);
defaultDimensionCon += struct.fields();
defaultDimensionCon += struct.fieldName(1);
defaultDimensionCon += struct.valueIndex(1);
defaultDimensionCon += struct.fieldName(2);
defaultDimensionCon += struct.valueIndex(2);
DimensionDefault = AxdDimensionUtil::getDimensionAttributeValueSetId(defaultDimensionCon);
}
[Modify]
static
void ReplaceDimensionAttributeValue(Args _args)
{
DimensionDefault dimSource, dimTarget, dimReplaced;
dimTarget = 52565471266;
dimSource = AxdDimensionUtil::getDimensionAttributeValueSetId([1, “Department”, ‘022’]);
dimReplaced = DimensionDefaultingService::serviceReplaceAttributeValue(dimTarget,
dimSource,
DimensionAttribute::findByName(‘Department’).RecId);
}
System will create a new default dimension which just the replaced attribute is different than source default dimension.
-
Ledger dimension
[Create]
static
void CreateLedgerDimension(Args _args)
{
//print DimensionStorage::accountNum2LedgerDimension(‘1101’,LedgerJournalACType::Cust);
print DimensionDefaultingEngine::getLedgerDimensionFromAccountAndDim(MainAccount::findByMainAccountId(‘110150’).RecId,
DimensionHierarchy::getAccountStructure(MainAccount::findByMainAccountId(‘110150’).RecId),
52565471266);//default dimension
pause;
}
static
void CreateLedgerDimension2(Args _args)
{
LedgerDimensionAccount ledgerDimension;
ledgerDimension = DimensionDefaultingService::serviceCreateLedgerDimension(DimensionStorage::getDefaultAccountForMainAccountNum(‘110150’),
52565471266);//default dimension
info(strFmt(“%1: %2”, ledgerDimension, DimensionAttributeValueCombination::find(ledgerDimension).DisplayValue));
}
[Modify]
static
void ReplaceLedgerDimensionValue(Args _args)
{
LedgerDimensionAccount ledgerDimension, ledgerDimensionReplaced;
DimensionDefault dimSource, dimTarget, dimReplaced;
ledgerDimension = DimensionDefaultingService::serviceCreateLedgerDimension(DimensionStorage::getDefaultAccountForMainAccountNum(‘110150’),
52565471266);//default dimension
info(strFmt(“%1: %2”, ledgerDimension, DimensionAttributeValueCombination::find(ledgerDimension).DisplayValue));
dimTarget = DimensionStorage::getDefaultDimensionFromLedgerDimension(ledgerDimension);
dimSource = AxdDimensionUtil::getDimensionAttributeValueSetId([1, “Department”, ‘022’]);
dimReplaced = DimensionDefaultingService::serviceReplaceAttributeValue(dimTarget,
dimSource,
DimensionAttribute::findByName(‘Department’).RecId);
ledgerDimensionReplaced = DimensionDefaultingService::serviceCreateLedgerDimension(DimensionStorage::getDefaultAccountForMainAccountNum(‘110150’),
dimReplaced);//default dimension
info(strFmt(“%1: %2”, ledgerDimensionReplaced, DimensionAttributeValueCombination::find(ledgerDimensionReplaced).DisplayValue));
}
Actually, here is to create a new ledger dimension. Update the records of ledger dimension or default dimension may cause data consistency issues.
- Tips for Financial dimensions.
-
Useful classes:
DimensionDefaultingEngine
DimensionDefaultingService
DimensionStorage
AxdDimensionUtil
These classes have some static methods that very useful. You can get details from MSDN.
-
Some time we may need to debug what’s kind of account and default dimensions be used when posting certain transaction. You can try set a breakpoint in following method:
DimensionDefaultingService::serviceCreateLedgerDimension()
-
The ledger dimension default account that used for settings on parameter form that without segment.
For example, the account that used for posting profile, inventory posting….
static
void accountNum2Dimension(Args _args)
{
print DimensionStorage::getLedgerDefaultAccountFromLedgerDim(DimensionDefaultingEngine::getLedgerDimensionFromAccountAndDim(MainAccount::findByMainAccountId(‘140200’).RecId,
DimensionHierarchy::getAccountStructure(MainAccount::findByMainAccountId(‘140200’).RecId),
0));
print DimensionStorage::getDynamicAccount(‘1001’, LedgerJournalACType::Vend);
print DimensionStorage::accountNum2LedgerDimension(‘1001’, LedgerJournalACType::Vend);
pause;
}
