SSRS Report invoice journal calculation issue

(0) Share

Report

Posted on by D365FO DEV

406

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?

All responses (4)

Answers (0)

Sort by

Martin Dráb 240,783 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
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.
D365FO DEV 406 on at

Like (0)

Report
Copy link

Link copied!

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 240,783 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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 406 on at

Like (0)

Report
Copy link

Link copied!

my issue resolved. just added the field AmountMST *-1 in the report designer

thanks alot!