Sunday, 18 November 2012

Code to retrieve dimension value

The following code snippet will return a specific financial dimension value attached to a record. It can be used for example to retrieve the cost center attached to a customer.

DimensionValue getDimensionValue(RefRecID dimensionSetRecID,Name attributeName) 
{
    
    DimensionAttributeValueSet      dimAttrValueSet;
    DimensionAttributeValueSetItem  dimAttrValueSetItem;
    DimensionAttributeValue         dimAttrValue;
    DimensionAttribute              dimAttribute;
    
    dimAttrValueSet = DimensionAttributeValueSet::find(dimensionSetRecID);
    
    select dimAttrValueSetItem
        where   dimAttrValueSetItem.DimensionAttributeValueSet      == dimAttrValueSet.RecId
    join dimAttrValue    
        where   dimAttrValue.RecId                                  == dimAttrValueSetItem.DimensionAttributeValue
    join dimAttribute        
        where   dimAttribute.RecId                                  == dimAttrValue.DimensionAttribute
        &&      dimAttribute.Name                                   == attributeName;
    
    return dimAttrValue.getValue();        
}  


And to retrieve the default cost center attached to a customer:

info(getDimensionValue(CustTable::find('1101').DefaultDimension,'CostCenter'));

This code shows the general table relations, but you're better off using the DimensionStorage class/API to read and update individual dimension values. A good example can be seen in the responses to this StackOverflow topic: http://stackoverflow.com/questions/8984449/how-to-set-a-single-dimension-value-in-ax-2012


The following code shows how to check all customers with a specific cost center value:

static void ScanRecordsByDimensionValue(Args _args)
{

    // Check all customers that have a specific value for cost centre.    
    
    Name                            attrName = 'CostCenter';
    DimensionValue                  dimValue = 'OU_3566';
    
    CustTable                       custTable;

    DimensionAttributeValueSet      dimAttrValueSet;
    DimensionAttributeValueSetItem  dimAttrValueSetItem;
    DimensionAttributeValue         dimAttrValue;
    DimensionAttribute              dimAttribute;
    ;

    dimAttribute    = DimensionAttribute::findByName('CostCenter');
    dimAttrValueSet = DimensionAttributeValueSet::find(custTable.DefaultDimension);

    while select custTable
    join dimAttrValueSetItem
        where   dimAttrValueSetItem.DimensionAttributeValueSet      == custTable.DefaultDimension
        &&      dimAttrValueSetItem.DisplayValue                    == dimValue
    join dimAttrValue
        where   dimAttrValue.RecId                                  == dimAttrValueSetItem.DimensionAttributeValue
        &&      dimAttrValue.DimensionAttribute                     == dimAttribute.RecId
    {        
        info(custTable.AccountNum);                       
    }   

}

2 comments:

  1. This is nice .
    How do you filter now to show all cutstomers
    with the costcenter ='1101' ?

    ReplyDelete
    Replies
    1. I've added a section showing how to do that.

      Delete