Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Unanswered

SSRS Report invoice journal calculation issue

(0) ShareShare
ReportReport
Posted on by 404
hello, im having a calculation issue in my invoice journal ssrs report. so here is my problem:
as u can see in the picture below, the LCY Amount and amount in currency should be the same as the total amount.
the field of lcy amount and amount in currency are:
 
amount in currency:VendAmountLCY          
LCY Amount:VendAmountCurr    
 
instead of showing the sum of the total amount, its showing the sum of the amount field. so instead of  345+115, its calculating 300+100
as you can see in my code logic, the vendamountlcy and vendamountcurr calculation is the field amount + vat amount,
           while select LedgerJournalTrans where  LedgerJournalTrans.JournalNum == ledgerJournalId && LedgerJournalTrans.Voucher == voucher            {                                    if(ledgerJournalTrans.AmountCurCredit == 0)                    {                        Ex_InvoiceJournalTmp.VendAmountLCY          = ((Ledgerjournaltrans.AmountCurDebit + abs(Ex_InvoiceJournalTmp.Ex_VATAmount)) * Ledgerjournaltrans.ExchRate)/100;                        Ex_InvoiceJournalTmp.VendAmountCurr         = Ledgerjournaltrans.AmountCurDebit + abs(Ex_InvoiceJournalTmp.Ex_VATAmount) ;                                   }                    else if(ledgerJournalTrans.AmountCurDebit == 0)                    {                        Ex_InvoiceJournalTmp.VendAmountLCY          = abs(((Ledgerjournaltrans.AmountCurCredit + abs(Ex_InvoiceJournalTmp.Ex_VATAmount)) * Ledgerjournaltrans.ExchRate)/100) *-1;                        Ex_InvoiceJournalTmp.VendAmountCurr         = abs(Ledgerjournaltrans.AmountCurCredit + abs(Ex_InvoiceJournalTmp.Ex_VATAmount)) * -1;                                    }                }
 
if(ledgerJournalTrans.AmountCurCredit == 0)                    {                        Ex_InvoiceJournalTmp.Amount = taxTrans.SourceBaseAmountCur;                        Ex_InvoiceJournalTmp.VendAmountCurr = ((abs(Ex_InvoiceJournalTmp.Amount) + abs(Ex_InvoiceJournalTmp.Ex_VATAmount)) * Ledgerjournaltrans.ExchRate)*-1/100;                        Ex_InvoiceJournalTmp.AmountMST              = ((abs(Ex_InvoiceJournalTmp.Amount) + abs(Ex_InvoiceJournalTmp.Ex_VATAmount)) * Ledgerjournaltrans.ExchRate)*-1/100;                        Ex_InvoiceJournalTmp.VendAmountLCY = ((abs(Ex_InvoiceJournalTmp.Amount) + abs(Ex_InvoiceJournalTmp.Ex_VATAmount)) * Ledgerjournaltrans.ExchRate)*-1/100;                                              x   = x +  Ex_InvoiceJournalTmp.AmountMST;                        Ex_InvoiceJournalTmp.ConversionAmount       = numeralsToTxt(abs(x));                    }                    else if(ledgerJournalTrans.AmountCurDebit == 0)                    {                        Ex_InvoiceJournalTmp.Amount = taxTrans.SourceBaseAmountCur;                        Ex_InvoiceJournalTmp.VendAmountLCY = ((abs(Ex_InvoiceJournalTmp.Amount) + abs(Ex_InvoiceJournalTmp.Ex_VATAmount)) * Ledgerjournaltrans.ExchRate)*-1/100;                                               Ex_InvoiceJournalTmp.AmountMST              = ((abs(Ex_InvoiceJournalTmp.Amount) + abs(Ex_InvoiceJournalTmp.Ex_VATAmount)) * Ledgerjournaltrans.ExchRate) /100;                             Ex_InvoiceJournalTmp.VendAmountCurr         = abs(Ledgerjournaltrans.AmountCurCredit + abs(Ex_InvoiceJournalTmp.Ex_VATAmount)) * -1;
 
any advise?
 
 
  • D365FO DEV Profile Picture
    D365FO DEV 404 on at
    SSRS Report invoice journal calculation issue
    my issue resolved. just  added the field AmountMST *-1 in the report designer
    thanks alot!
  • Martin Dráb Profile Picture
    Martin Dráb 230,853 Most Valuable Professional on at
    SSRS Report invoice journal calculation issue
    You're saying that you want to sum AmountMST, but it's not what you do in your code. My question is why.
     
    If "amount in currency should be the same as the total amount", you can add lines to your tmp table and then use select sum, or you can hold the value in a variable, keep updating it when adding lines and then write it somewhere (probably a header-level tmp table) at the end.
  • D365FO DEV Profile Picture
    D365FO DEV 404 on at
    SSRS Report invoice journal calculation issue
    hello sir,
    thanks for your reply.
     
    Yes , i need to show the sum of the total amount (sar). i already showed you the logic of the calculation and here is my report design:
  • Martin Dráb Profile Picture
    Martin Dráb 230,853 Most Valuable Professional on at
    SSRS Report invoice journal calculation issue
    First of all, let me re-post your code, so we can read it.
    while select ledgerJournalTrans
        where ledgerJournalTrans.JournalNum == ledgerJournalId
           && ledgerJournalTrans.Voucher == voucher
    {
        if (ledgerJournalTrans.AmountCurCredit == 0)
        {
            ex_InvoiceJournalTmp.VendAmountLCY    = ((ledgerJournalTrans.AmountCurDebit + abs(ex_InvoiceJournalTmp.Ex_VATAmount)) * ledgerJournalTrans.ExchRate) / 100;
            ex_InvoiceJournalTmp.VendAmountCurr = ledgerJournalTrans.AmountCurDebit + abs(ex_InvoiceJournalTmp.Ex_VATAmount) ;
        }
        else if (ledgerJournalTrans.AmountCurDebit == 0)
        {
            ex_InvoiceJournalTmp.VendAmountLCY    = -abs(((ledgerJournalTrans.AmountCurCredit + abs(ex_InvoiceJournalTmp.Ex_VATAmount)) * ledgerJournalTrans.ExchRate)/100);    
            ex_InvoiceJournalTmp.VendAmountCurr = -abs(ledgerJournalTrans.AmountCurCredit + abs(ex_InvoiceJournalTmp.Ex_VATAmount));
        }
    }
    
    // --- Second snippet ---
    
    if (ledgerJournalTrans.AmountCurCredit == 0)
    {
        ex_InvoiceJournalTmp.Amount = taxTrans.SourceBaseAmountCur;
        ex_InvoiceJournalTmp.VendAmountCurr = -((abs(ex_InvoiceJournalTmp.Amount) + abs(ex_InvoiceJournalTmp.Ex_VATAmount)) * ledgerJournalTrans.ExchRate)  / 100;
        ex_InvoiceJournalTmp.AmountMST = -((abs(ex_InvoiceJournalTmp.Amount) + abs(ex_InvoiceJournalTmp.Ex_VATAmount)) * ledgerJournalTrans.ExchRate) / 100;
        ex_InvoiceJournalTmp.VendAmountLCY = -((abs(ex_InvoiceJournalTmp.Amount) + abs(ex_InvoiceJournalTmp.Ex_VATAmount)) * ledgerJournalTrans.ExchRate) / 100;
    
        x += ex_InvoiceJournalTmp.AmountMST;    
        ex_InvoiceJournalTmp.ConversionAmount = numeralsToTxt(abs(x));
    }
    else if(ledgerJournalTrans.AmountCurDebit == 0)
    {
        ex_InvoiceJournalTmp.Amount = taxTrans.SourceBaseAmountCur;    
        ex_InvoiceJournalTmp.VendAmountLCY = -((abs(ex_InvoiceJournalTmp.Amount) + abs(ex_InvoiceJournalTmp.Ex_VATAmount)) * ledgerJournalTrans.ExchRate) / 100;
        ex_InvoiceJournalTmp.AmountMST = ((abs(ex_InvoiceJournalTmp.Amount) + abs(ex_InvoiceJournalTmp.Ex_VATAmount)) * ledgerJournalTrans.ExchRate) / 100;
        ex_InvoiceJournalTmp.VendAmountCurr = -abs(ledgerJournalTrans.AmountCurCredit + abs(ex_InvoiceJournalTmp.Ex_VATAmount));
    }
    If understand your description correctly, you want to use the value of Total Amount (SAR), but you aren't. Why? Is it because you don't where to find the value of Total Amount (SAR)? If so, you can look into the report design to see what expression it used to populate the control value.
     
     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Congratulations to the January Top 10 leaders!

Check out the January community rock stars...

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,996 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,853 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans