sumitsaxfactor: Dimension Provider Class and Run-time dimension ranges [AX 2012]

[Blog bot]

Источник: http://sumitsaxfactor.wordpress.com/...anges-ax-2012/
==============

While working on a requirement for ledger amounts, I had to find out a way to filter the transactions for a specific dimension value of a specific dimension type; from ledger transactions of a main account.

Now had it been Ax 2009, it was pretty simple where you could provide a range on Dimensions[arrayIndex] field. But in Ax 2012 the dimensions on a transaction are always stored a combination value rather than a separate value.

While running the query, I found a support provided by MS where-in all the dimensions will be added as fields for a table having Ledger dimensions on run-time. Now if we were running a query manually, we will be able to specify the range manually as shown below:

For demo purpose, I am using GeneralJournalAccountEntry table that holds the amounts for transactions posted to a main account



Now try and add a range, when you select the drop-down on the Field column, you will notice new fields added to the drop-down



The concept behind is that, these fields are added at run-time and when you add a range to any of these fields, a view (DimensionAttributeLevelValueView) will be dynamically added for each field range record you create, as a child DS for GeneralJournalAccountEntry

 

This is what the SQL statement looks like at the backend

SELECT*FROM GeneralJournalAccountEntry(GeneralJournalAccountEntry_1)JOIN*FROM DimensionAttributeLevelValueView(DimAttCol_GeneralJournalAccountEntry_1_LedgerDimension_5637145354)ON GeneralJournalAccountEntry.LedgerDimension = DimensionAttributeLevelValueView.ValueCombinationRecId AND((DimensionAttribute = 5637145354))AND((DisplayValue =N’000001′))

Now when we are running queries manually, we can specify such kind of a range, what if we have to do the same thing while running a query at a backend or through a code?

Here comes the DimensionProvider class to our rescue. This class will help us add such ranges as required. Look at the sample job below for some guidance.

staticvoid addDimensionRange(Args _args)

{

    Query                   query = new Query();

    QueryRun                queryRun;

    QueryBuildDataSource    qbds;

    DimensionProvider       dimensionProvider = new DimensionProvider();

    GeneralJournalAccountEntry  accEntry;

    DimensionAttribute      dimAttr;

    Name    dimAttrNameEmpl, dimAttrNameCostCenter;

    int i;

    ;

 

    selectfirstOnly dimAttr where dimAttr.BackingEntityType == tableNum(DimAttributeHcmWorker);

    dimAttrNameEmpl = dimAttr.Name;

   

    selectfirstOnly dimAttr where dimAttr.BackingEntityType == tableNum(DimAttributeOMCostCenter);

    dimAttrNameCostCenter = dimAttr.Name;

   

    qbds = query.addDataSource(tableNum(GeneralJournalAccountEntry));

 

    dimensionProvider.addAttributeRangeToQuery(query, qbds.name(), fieldStr(GeneralJournalAccountEntry, LedgerDimension), DimensionComponent::DimensionAttribute, SysQuery::valueNotEmptyString(), dimAttrNameEmpl, true);

    dimensionProvider.addAttributeRangeToQuery(query, qbds.name(), fieldStr(GeneralJournalAccountEntry, LedgerDimension), DimensionComponent::DimensionAttribute, SysQuery::valueNotEmptyString(), dimAttrNameCostCenter, true);

 

    queryRun = new QueryRun(query);

    queryRun.prompt();

   

    while(queryRun.next())

    {

        accEntry = queryRun.get(tableNum(GeneralJournalAccountEntry));

        info(strFmt("%1 %2", DimensionAttributeValueCombination::find(accEntry.LedgerDimension).DisplayValue, accEntry.AccountingCurrencyAmount));

    }


}Here is the sample output log



This is how the query will be if you prompt the query






Источник: http://sumitsaxfactor.wordpress.com/...anges-ax-2012/

[SRF]

Похоже в методе addAttributeRangeToQuery класса DimensionProvider в строке 58 используют не правильный тип соединения

public QueryBuildRange addAttributeRangeToQuery(Query _query, str _dataSourceName, str _dimensionFieldName, DimensionComponent _dimensionComponent, anytype _value, str _dimensionAttributeName='', boolean _isDefaultRange = false)
{
...
           // Change the join to an inner join since it is now restricting
            qbds.joinMode(JoinMode::InnerJoin);
...
}

При таком соединении из выборки уходят все строки, у которых не определено значение фин. аналитики, по которой накладываем условие.

Т.е. когда мы используем конструкцию вида

...
    SysQuery::addDimensionAttributeRange(
                query,
                qbds.name(),
                fieldStr(Example, DefaultDimension),
                DimensionComponent::DimensionAttribute,
                SysQuery::valueUnlimited(),
                dimensionName,
                true);
...

будут отобраны все записи из таблицы Example, у которых аналитика dimensionName не пустая, даже не смотря на то, что значение мы передаем любое.

И еще один момент, в соседнем методе addAttributeFilterToQuery(он почти повторяет данный метод за исключением методов к которым применяются значения условий) этого же класса, используется уже OuterJoin.

public QueryFilter addAttributeFilterToQuery(Query _query, str _dataSourceName, str _dimensionFieldName, DimensionComponent _dimensionComponent, anytype _value, str _dimensionAttributeName = '')
{
...
           // Change the join to an outer join since it is now restricting with a filter
            qbds.joinMode(JoinMode::OuterJoin);
...
}