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));        
    }
}
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));        
    }
}
Reply