DAX 2012 Select Statement Order By Dimension

This question is answered

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!

Verified Answer
  • 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));        
        }
    }
    

    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.

All Replies
  • 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));        
        }
    }
    

    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.