Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX forum
Suggested answer

Relation Between MainAccount Table And GeneralJournalAccountEntry

Posted on by Microsoft Employee

Hi ,

I need to make ssrs report by x++ code , insert main Account Id and Interval From Date and To Date 

The result is TransactionCurrencyAmount for every transaction 

any help to bind tables 

Thanks .

  • Suggested answer
    Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at
    RE: Relation Between MainAccount Table And GeneralJournalAccountEntry

    Hi,

    Please try once with the below code:

    static void Job12(Args _args)
    {
        DimensionAttributeValueCombination  DimensionAttributeValueCombination;
        DimensionHierarchyLevel             hierarchyLevel;
        GeneralJournalEntry                 generalJournalEntry;
        GeneralJournalAccountEntry          generalJournalAccountEntry;
        DimensionAttribute                  dimensionAttribute;
        MainAccount                         mainAccount;
        AmountMSTDebCred                    CurCodeCont, trans;
        DimensionFocusBalance               DimensionFocusBalance;
        
        while select generalJournalAccountEntry
           Join generalJournalEntry 
           where generalJournalAccountEntry.GeneralJournalEntry == generalJournalEntry.RecId
            && generalJournalEntry.AccountingDate == str2Date('01/01/2016',123)
           Join DimensionAttributeValueCombination
           where DimensionAttributeValueCombination.RecId == generalJournalAccountEntry.LedgerDimension
          //  && DimensionAttributeValueCombination.LedgerDimensionType == LedgerDimensionType::DefaultAccount
           Join MainAccount
           where mainAccount.MainAccountId =='2385'
         && mainAccount.RecId == DimensionAttributeValueCombination.MainAccount
       {
          CurCodeCont += generalJournalAccountEntry.AccountingCurrencyAmount;
          trans += generalJournalAccountEntry.TransactionCurrencyAmount;
           
          // GJN += generalJournalEntry.RecId;
       }
        
        info(strFmt("%1, %2", CurCodeCont, trans));
    }
  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Relation Between MainAccount Table And GeneralJournalAccountEntry

     This is my code but the output need more filter

    while select AccountingCurrencyAmount from generalJournalAccountEntry

           Join generalJournalEntry Join DimensionAttributeValueCombination

       Join MainAccount Join DimensionHierarchy

       where mainAccount.MainAccountId =='2385'

       && mainAccount.RecId == DimensionAttributeValueCombination.MainAccount

       &&DimensionAttributeValueCombination.RecId == generalJournalAccountEntry.LedgerDimension

       &&generalJournalAccountEntry.GeneralJournalEntry == generalJournalEntry.RecId

       && generalJournalEntry.AccountingDate == str2Date('01/01/2016',123)

       {

          CurCodeCont += generalJournalAccountEntry.AccountingCurrencyAmount;

           GJN += generalJournalEntry.RecId;

       }

  • Suggested answer
    Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at
    RE: Relation Between MainAccount Table And GeneralJournalAccountEntry

    Hi,

    You can find the query in the method populateTmpTransSummary(Line#  194)of class LedgerTrialBalanceDP.

     // Insert transactions prior to start date as Opening transactions
            insert_recordset _ledgerTrialBalanceStagingTmp
                (AccountingDate,
                LedgerDimension,
                DetailSummary,
                AmountDebit,
                AmountCredit,
                PostingLayer,
                TransactionType,
                PrimaryFocus)
                select minOf(AccountingDate), FocusLedgerDimension, summary, sum(DebitAccountingCurrencyAmount), sum(CreditAccountingCurrencyAmount), PostingLayer, opening
                from dimensionFocusBalance
                         group by dimensionFocusBalance.FocusLedgerDimension, dimensionFocusBalance.PostingLayer, dimensionAttributeValueCombination.DisplayValue
                    where
                        dimensionFocusBalance.FocusDimensionHierarchy == _primaryDimensionSet.RecId &&
                        ((dimensionFocusBalance.AccountingDate < _startDate && dimensionFocusBalance.AccountingDate >= periodStartDate) ||
                         (dimensionFocusBalance.AccountingDate >= periodStartDate && dimensionFocusBalance.FiscalCalendarPeriodType == FiscalPeriodType::Opening && !_includeOpeningInDetail)) &&                    dimensionFocusBalance.Ledger == Ledger::current() &&
                        dimensionFocusBalance.AccountingDate <= _endDate &&
                        dimensionFocusBalance.IsSystemGeneratedUltimo == NoYes::No &&
                        dimensionFocusBalance.Ledger == Ledger::current()
                join DisplayValue from dimensionAttributeValueCombination where
                    dimensionAttributeValueCombination.RecId == dimensionFocusBalance.FocusLedgerDimension;


  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Relation Between MainAccount Table And GeneralJournalAccountEntry

    How can I see the X++ code i need to know the path

  • Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at
    RE: Relation Between MainAccount Table And GeneralJournalAccountEntry

    I feel that information is available on TrailBalance form, are you looking for X++ code which provides this details.

    Path: General ledger/Common/Trial balance

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Relation Between MainAccount Table And GeneralJournalAccountEntry

    Hi Sukrut Parab ,

    OperationsTax::Current   Operand types are not compatible with the operator.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Relation Between MainAccount Table And GeneralJournalAccountEntry

    I need Opening balance , Debit  And credit

  • Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at
    RE: Relation Between MainAccount Table And GeneralJournalAccountEntry

    Hi,

    Can you provide the details of the data you want to extract by passing this values.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Relation Between MainAccount Table And GeneralJournalAccountEntry

    Hi Chaitanya Golla  ,

    But I need To Insert  Account Number Id And Filter All Data By Acc. No. And Date

  • Suggested answer
    Andrew Xu Profile Picture
    Andrew Xu 3,875 on at
    RE: Relation Between MainAccount Table And GeneralJournalAccountEntry

    Do you have a dimension set only contains ledger account? If not, you'd better to add one. And then you can put your main account number as a criterion in the query.

Helpful resources

Quick Links

Can you answer this forum question?

You could make someone's day!

Community Newsletter - May 2024

Kudos to our community stars!

Community Spotlight of the Month

Kudos to Mohamed Amine Mahmoudi!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 283,663 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 224,741 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,146

Featured topics

Product updates

Dynamics 365 release plans