SBX - Search With Button

SBX - Forum Post Title

how to select ledger data by dimensions in AX7?

Microsoft Dynamics AX Forum

raz asked a question on 4 Jan 2017 10:09 AM

Question Status

Unanswered

Is it possible to repeat this t-sql code inside AX7 using select statement or query?

I do not understand why I can see many columns in DimensionAttributeValueCombination in SQL and cannot see them in AX7.

select
sum(total.TRANSACTIONCURRENCYAMOUNT), total.MAINACCOUNTVALUE, total.TRANSACTIONCURRENCYCODE,
total.SUBLEDGERVOUCHERDATAAREAID, total.BUSINESSUNITVALUE, total.DEPARTMENTVALUE
from(
SELECT
d.MAINACCOUNTVALUE,
a.TRANSACTIONCURRENCYCODE,
a.TRANSACTIONCURRENCYAMOUNT,
a.ACCOUNTINGCURRENCYAMOUNT,
b.ACCOUNTINGDATE,
b.SUBLEDGERVOUCHER,
b.SUBLEDGERVOUCHERDATAAREAID,
d.BUSINESSUNITVALUE,
d.CENTERVALUE,
d.COSTCENTERVALUE,
d.DEPARTMENTVALUE,
d.LEGALENTITYVALUE,
d.VEHICLEVALUE

FROM GeneralJournalAccountEntry A
INNER JOIN GeneralJournalEntry B ON B.RECID = A.GENERALJOURNALENTRY
INNER JOIN DimensionAttributeValueCombination D on a.LEDGERDIMENSION = D.RecId
) total
group by total.MAINACCOUNTVALUE, total.TRANSACTIONCURRENCYCODE, total.SUBLEDGERVOUCHERDATAAREAID,
total.BUSINESSUNITVALUE, total.DEPARTMENTVALUE

Reply
raz responded on 5 Jan 2017 3:43 AM

I think I found the solution!

    private void test()
    {
        GeneralJournalAccountEntry          generalJournalAccountEntry;
        GeneralJournalEntry                 generalJournalEntry;
        DimensionAttributeValueCombination  dimensionAttributeValueCombination;

        Query                               query = new Query();
        QueryRun                            queryRun;
        QueryBuildDataSource                qbds1, qbds2, qbds3;

        str                                 strData;
        
        qbds1 = query.addDataSource(tableNum(GeneralJournalAccountEntry));
        qbds1.addSelectionField(fieldNum(GeneralJournalAccountEntry, TransactionCurrencyAmount), SelectionField::Sum);
        qbds1.addSelectionField(fieldNum(GeneralJournalAccountEntry, AccountingCurrencyAmount), SelectionField::Sum);
        qbds1.addGroupByField(fieldNum(GeneralJournalAccountEntry, TransactionCurrencyCode));

        qbds2 = qbds1.addDataSource(tableNum(GeneralJournalEntry));
        qbds2.relations(false);
        qbds2.addLink(fieldNum(GeneralJournalAccountEntry, GeneralJournalEntry), fieldNum(GeneralJournalEntry, RecId));
        qbds2.fetchMode(QueryFetchMode::One2One);
        qbds2.addGroupByField(fieldNum(GeneralJournalEntry, AccountingDate));
        qbds2.addGroupByField(fieldNum(GeneralJournalEntry, SubledgerVoucherDataAreaId));

        qbds3 = qbds1.addDataSource(tableNum(DimensionAttributeValueCombination));
        qbds3.relations(false);
        qbds3.addLink(fieldNum(GeneralJournalAccountEntry, LedgerDimension), fieldNum(DimensionAttributeValueCombination, RecId));
        qbds3.fetchMode(QueryFetchMode::One2One);
        qbds3.addGroupByField(fieldName2Id(tableNum(DimensionAttributeValueCombination), identifierStr(MainAccountValue)));
        qbds3.addGroupByField(fieldName2Id(tableNum(DimensionAttributeValueCombination), identifierStr(BusinessUnitValue)));
        qbds3.addGroupByField(fieldName2Id(tableNum(DimensionAttributeValueCombination), identifierStr(LegalEntityValue)));
        qbds3.addGroupByField(fieldName2Id(tableNum(DimensionAttributeValueCombination), identifierStr(DepartmentValue)));

        queryRun = new QueryRun(query);

        while (queryRun.next())
        {
            generalJournalAccountEntry         = queryRun.get(tableNum(GeneralJournalAccountEntry));
            generalJournalEntry                = queryRun.get(tableNum(GeneralJournalEntry));
            dimensionAttributeValueCombination = queryRun.get(tableNum(DimensionAttributeValueCombination));

            strData = strFmt('%1;%2;%3;%4;%5;%6;%7;%8;%9;', 
                generalJournalAccountEntry.TransactionCurrencyAmount,
                generalJournalAccountEntry.AccountingCurrencyAmount,
                generalJournalAccountEntry.TransactionCurrencyCode,
                generalJournalEntry.AccountingDate,
                generalJournalEntry.SubledgerVoucherDataAreaId,
                dimensionAttributeValueCombination.(fieldName2Id(tableNum(DimensionAttributeValueCombination), identifierStr(MainAccountValue))),
                dimensionAttributeValueCombination.(fieldName2Id(tableNum(DimensionAttributeValueCombination), identifierStr(BusinessUnitValue))),
                dimensionAttributeValueCombination.(fieldName2Id(tableNum(DimensionAttributeValueCombination), identifierStr(LegalEntityValue))),
                dimensionAttributeValueCombination.(fieldName2Id(tableNum(DimensionAttributeValueCombination), identifierStr(DepartmentValue))),
                );
        }
    }


Reply
raz responded on 10 Jan 2017 6:12 AM

Do anyone know another, more rightful way to do the query?

I do not like to use the way I found, because it depends on some "hack" which can be obsolete in a new version.

Reply

SBX - Two Col Forum

SBX - Migrated JS