Tag: range

Development

Using the class DimensionProvider to filter a query with multiple financial dimensions criteria

The very first subject we’ve discussed here at the blog was financial dimensions. And honestly, I felt like it would be cool to dive into it once again.

The reason is that we all know that the dimension framework was completely redesigned for the AX 2012, and even after a while, you still can find some new stuff about it.

A friend of mine was developing a report using data provider. He needed to use some filters to the default dimension present on his query, but he was doing it the hard way and struggling. He was trying to use all tables from the dimension structure to finally reach the intended criteria – using DimensionAttribute, DimensionAttributeValueSet, DimensionAttributeValueSetItem, etc.

Going down that road, he would eventually get everything to work, but the code was getting bigger and bigger, with low quality and bad performance. He asked for my help and I told him about the class DimensionProvider.

Let me show you a quick example in how to use it:

static void EDC_dimensionProvider(Args _args)
{
    #define.dimAttr("CostCenter")
    #define.dimRangeValue("012")
    
    Query query = new Query();
    QueryRun queryRun;
    QueryBuildDataSource queryCustTable;
    QueryBuildRange queryDimRange;
    
    CustTable custTable;
    DimensionProvider dimensionProvider;
    
    DimensionAttributeValueSetStorage dimAttrValueSetStorage;
    int countLine = 1;
    int i;
    
    queryCustTable = query.addDataSource(tableNum(CustTable));
    dimensionProvider = new DimensionProvider();

    dimensionProvider.addAttributeRangeToQuery(
        query,
        queryCustTable.name(),
        identifierStr(DefaultDimension),
        DimensionComponent::DimensionAttribute,
        #dimRangeValue,
        #dimAttr,
        true);

    queryRun = new QueryRun(query);

    while (queryRun.next()) {
        custTable = queryRun.get(tableNum(CustTable));
        
        dimAttrValueSetStorage = DimensionAttributeValueSetStorage::find(custTable.DefaultDimension);

        for (i = 1; i <= dimAttrValueSetStorage.elements(); i++) {
            info(strFmt("AccNum %1 <-> %2: %3",
                custTable.AccountNum,
                DimensionAttribute::find(dimAttrValueSetStorage.getAttributeByIndex(i)).Name,
                dimAttrValueSetStorage.getDisplayValueByIndex(i)));
        }
    }
}

Keep in mind that this job is only made for this blog post and we have some hard-coding on purpose.

I’ve used the addAttributeRangeToQuery() method, but you have other ways to use it. Basically, the DimensionProvider class will take your query, data source name, dimension set type (both financial or ledger), component, range value and dimension attribute and work it all out. If you navigate inside those methods, you’re going to see a lot of things that you would have to do it by yourself.

In my development VM, I’ve put the cost center code 012 in 3 different customer records just to check the range effectiveness. This is the generated infolog:

dimensionprovider

See ya 🙂