AX 2012 - Order by a Financial Dimension

Financial dimensions with AX 2012 collected in RecId fields and that RecId referes a couple of tables so it's a bit complicated. Fortunately there is a class for make it easy:

DimensionProvider           dimProvider = new DimensionProvider();
    dimProvider.addOrderByAttribute(BorAXJournalTrans_DS.query(),BorAXJournalTrans_DS.query().dataSourceNo(1).name(),
       FieldStr(BorAXJournalTrans,DefaultDimension),
        DimensionComponent::DimensionAttribute,SortOrder::Ascending,
"Department");
BorAXJournalTrans_DS.executeQuery();
  
If you don't want to use that class alternate is:

RecId  depRecId = 5637152827; //RecId value of department dimension at DimensionAttribute table
    BorAXJournalTrans   boraxJournalTrans;
    DimensionAttributeValueSet dimensionAttributeValueSet;
    DimensionAttributeValueSetItem dimensionAttributeValueSetItem;
    DimensionAttributeValue dimensionAttributeValue;
    DimensionAttribute dimensionAttribute;

while SELECT firstOnly10 borAXJournalTrans 
    ORDER BY DimensionAttributeValueSetItem.DisplayValue
 JOIN dimensionAttributeValueSet 
    where borAXJournalTrans.DefaultDimension == dimensionAttributeValueSet.RecId 
 JOIN dimensionAttributeValueSetItem 
    where dimensionAttributeValueSet.RecId == dimensionAttributeValueSetItem.DimensionAttributeValueSet 
 JOIN dimensionAttributeValue 
    where dimensionAttributeValueSetItem.DimensionAttributeValue == dimensionAttributeValue.RecId 
 JOIN dimensionAttribute 
    where dimensionAttributeValue.DimensionAttribute == dimensionAttribute.RecId && 
     dimensionAttribute.RecId == depRecId
    {
        info(dimensionAttributeValueSetItem.DisplayValue);
    }

AX 2012 - Add filter for one dimension for a query's financial dimension

If you try to this with add tables you'll see you have to add lots of table to query. There is a class which do this for you DimensionProvider with AX 2012:

DimensionProvider           dimProvider = new DimensionProvider();


        dimProvider.addAttributeRangeToQuery(element.query(),element.query().dataSourceNo(1).name(),
       FieldStr(MyTable,DefaultDimension),
        DimensionComponent::DimensionAttribute,
        OMOperatingUnit::find(depRecId,OMOperatingUnitType::OMDepartment).OMOperatingUnitNumber,
            "Department",true);

BTW if you get an error like Unknown type: RefRecId it would be about EDT of your table. In my case they used RefRecId EDT instead of DimensionDefault EDT with DefaultDimension field. In your case it would be other than RefRecId. There is a bug like this with original AX table HcmEmployment.

Axapta - Add dimension based filter to a query

When we try to add filter based on StrFmt  like this; dimension[2] = "0001" it gives error because of brackets. I found the solution from a  forum page :

QueryBuildDataSource    qbds = query.addDataSource(tablenum(EmplTable));
...
 qbds.addRange(fieldid2Ext(fieldnum(EmplTable, Dimension),1)).value("600742");

AX 2012 - Create a new financial dimension which related a table(s)

I found the easy way that the way from a blog which referred Microsoft'un related white paper:

1-Duplicate one of views from AOT which beginned DimAttribute and new views name should be begin same DimAttribute word.
2. Main datasource name should be BackingEntity unrelated what name used with original table name.
3. All field names of view should be like these whatever original names:
Key - data source tables Surrogate key field. Like RecId...
Value - data source tables primary key . Like AccountNum...
Name - Description field. Like Description or CustomerName...

There are two ways to use this new dimension implementation:

AOS restart

or

DimensionCache::clearAllScopes(); 

command.