Question Status

Verified
Ricky Scott asked a question on 17 Apr 2013 2:46 PM

Hi, How would I add add an Order By Dimension in 2012?

Example: Select * from InventTable Order By ______ ?

I would like to order alphabetically by one of the 5 dimension values we are using (not the description).

Thanks!

Reply
Verified Answer
Dominic Lee responded on 17 Apr 2013 9:01 PM

With the new implementation of financial dimension framework it'll be *slightly* different compare to AX2009:

//The example below sort Vendor by "Deprtment" financial dimension in descending order
static void SortVendorByDepartmentDimension(Args _args)
{
    VendTable   vendTable;
    DimensionAttributeValueSet      dimAttrValueSet;
    DimensionAttributeValueSetItem  dimAttrValueSetItem;
    DimensionAttributeValue         dimAttrValue;
    DimensionAttribute              dimAttr;
    
    // Find dimention attribute Department
    select firstOnly dimAttr
        where dimAttr.Name == "Department";
    
    // Order by Department
    while select firstOnly10 * from vendTable        
        order by dimAttrValueSetItem.DisplayValue desc
        join dimAttrValueSet        
        where dimAttrValueSet.RecId == vendTable.DefaultDimension                   
            join dimAttrValueSetItem 
            where dimAttrValueSetItem.DimensionAttributeValueSet == dimAttrValueSet.RecId               
                join dimAttrValue
                where dimAttrValueSetItem.DimensionAttributeValue == dimAttrValue.RecId
                   && dimAttrValue.DimensionAttribute == dimAttr.RecId
    {
        
        info(strFmt("Vendor (%1): %2 - Department: %3",
                    vendTable.AccountNum,
                    VendTable.name(),
                    dimAttrValueSetItem.DisplayValue));        
    }
}

kind regards,

Dominic Lee

My blog | PBC

This forum post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

Reply
Verified Answer
Dominic Lee responded on 17 Apr 2013 9:01 PM

With the new implementation of financial dimension framework it'll be *slightly* different compare to AX2009:

//The example below sort Vendor by "Deprtment" financial dimension in descending order
static void SortVendorByDepartmentDimension(Args _args)
{
    VendTable   vendTable;
    DimensionAttributeValueSet      dimAttrValueSet;
    DimensionAttributeValueSetItem  dimAttrValueSetItem;
    DimensionAttributeValue         dimAttrValue;
    DimensionAttribute              dimAttr;
    
    // Find dimention attribute Department
    select firstOnly dimAttr
        where dimAttr.Name == "Department";
    
    // Order by Department
    while select firstOnly10 * from vendTable        
        order by dimAttrValueSetItem.DisplayValue desc
        join dimAttrValueSet        
        where dimAttrValueSet.RecId == vendTable.DefaultDimension                   
            join dimAttrValueSetItem 
            where dimAttrValueSetItem.DimensionAttributeValueSet == dimAttrValueSet.RecId               
                join dimAttrValue
                where dimAttrValueSetItem.DimensionAttributeValue == dimAttrValue.RecId
                   && dimAttrValue.DimensionAttribute == dimAttr.RecId
    {
        
        info(strFmt("Vendor (%1): %2 - Department: %3",
                    vendTable.AccountNum,
                    VendTable.name(),
                    dimAttrValueSetItem.DisplayValue));        
    }
}

kind regards,

Dominic Lee

My blog | PBC

This forum post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

Reply