Wednesday, 18 October 2017

Get Unused Financial Dimensions in AX 2012

Below is the code for getting the unused financial dimensions in AX 2012.
Here I wrote code for Department.

static void finacialDimValues(Args _args)
{
    DimensionAttribute dimAttribute;
    DimensionAttributeValue dimAttributeValue;
    DimensionAttributeLevelValue dimAttributeLevelValue;
    DimensionAttributeValueGroup dimAttributeValueGroup;
    DimensionHierarchy dimHierarchy;
    Dictionary dict = new Dictionary();
    DictEnum   dictEnum = new DictEnum(enumNum(OMOperatingUnitType));
    TableId         entityId;
    RecId           entityInstance;
    DataAreaId      dataAreaId;
    RecId           financialTagCategoryId;
    TableId roleViewId;
    OMOperatingUnit oMOperatingUnit;
    DimensionAttributeValueSetItem  dimAttributeValueSetItem;
    while select oMOperatingUnit where oMOperatingUnit.OMOperatingUnitType == OMOperatingUnitType::OMDepartment
    {
        roleViewId = dict.tableName2Id(strFmt('DimAttribute%1', dictEnum.index2Symbol(oMOperatingUnit.OMOperatingUnitType)));
        [entityId, entityInstance, dataAreaId, financialTagCategoryId] = DimensionEnabledType::getFieldsForDeleteOrRename(oMOperatingUnit, roleViewId);
       // select firstOnly dimAttributeValueSetItem where dimAttributeValueSetItem.DisplayValue == oMOperatingUnit.OMOperatingUnitNumber;
         select firstonly RecId from dimAttributeLevelValue
        exists join dimAttributeValue where
            dimAttributeValue.RecId == dimAttributeLevelValue.DimensionAttributeValue &&
            dimAttributeValue.EntityInstance == entityInstance &&
            dimAttributeValue.IsDeleted == false
        exists join dimAttribute where
            dimAttribute.RecId == dimAttributeValue.DimensionAttribute &&
            dimAttribute.BackingEntityType == entityId
        exists join dimAttributeValueGroup where
            dimAttributeValueGroup.RecId == dimAttributeLevelValue.DimensionAttributeValueGroup
        notExists join dimHierarchy where
            dimHierarchy.RecId == dimAttributeValueGroup.DimensionHierarchy &&
            dimHierarchy.StructureType == DimensionHierarchyType::Focus;
        if(dimAttributeLevelValue)//dimAttributeValueSetItem)
        {
            continue;
        }
        else if(!OMMultipleHierarchy::checkExistenceInHierarchy(omOperatingUnit.RecId))
            info(oMOperatingUnit.OMOperatingUnitNumber);
    }
}


SQL query:

select * from DIMATTRIBUTEOMDEPARTMENT where not exists(
select * from DimensionAttributeLevelValue
           join DimensionAttributeValue on
            DimensionAttributeValue.RecId = DimensionAttributeLevelValue.DimensionAttributeValue and
            DimensionAttributeValue.EntityInstance = DIMATTRIBUTEOMDEPARTMENT.RECID and
            DimensionAttributeValue.IsDeleted = 0
          join DimensionAttribute on
            DimensionAttribute.RecId = DimensionAttributeValue.DimensionAttribute and
            DimensionAttribute.BackingEntityType = 11765
         join DimensionAttributeValueGroup on
            DimensionAttributeValueGroup.RecId = DimensionAttributeLevelValue.DimensionAttributeValueGroup where
        not exists(select * from DimensionHierarchy where
            DimensionHierarchy.RecId = DimensionAttributeValueGroup.DIMENSIONHIERARCHY and

            DimensionHierarchy.StructureType = 6))

@Rahul Talasila

No comments:

Post a Comment

Sorting data in a form datasource in Dynamics Ax 2012 / D365F&O X++

In Microsoft Dynamics AX, you can sort a form datasource when the form is loaded. On the form, override the init method. After the call...