Dimension Provider Class and Run-time dimension ranges [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 *FROMDimensionAttributeLevelValueView(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.
static void addDimensionRange(Args _args)
    Query                   query = new Query();
    QueryRun                queryRun;
    QueryBuildDataSource    qbds;
    DimensionProvider       dimensionProvider = new DimensionProvider();
    GeneralJournalAccountEntry  accEntry;
    DimensionAttribute      dimAttr;
    Name    dimAttrNameEmpl, dimAttrNameCostCenter;
    int i;

    select firstOnly dimAttr where dimAttr.BackingEntityType ==tableNum(DimAttributeHcmWorker);
    dimAttrNameEmpl = dimAttr.Name;
    select firstOnly 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);
        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

1 comment:

  1. Hello ,

    Thanks for this helpful post.

    Actully i want to apply financial dimension filter on my report and i am using LedgerJournalTrans Table in this job by replacing generalJournalAccountEntry table but its not working . Please give me some help to resolve this problem .


Note: Only a member of this blog may post a comment.