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?

I have the same question (0)

All responses (4)

Answers (0)

Martin Dráb 236,972 Most Valuable Professional on at

Like (0)

Report
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.

Was this reply helpful? Yes No
D365FO DEV 406 on at

Like (0)

Report

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:

Was this reply helpful? Yes No
Martin Dráb 236,972 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
D365FO DEV 406 on at

Like (0)

Report

SSRS Report invoice journal calculation issue

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

thanks alot!

Was this reply helpful? Yes No

Join the conversation

Community site session details

SSRS Report invoice journal calculation issue

Helpful resources

Quick Links

Responsible AI policies

Pallavi Phade – Community Spotlight

Congratulations to the October Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

SSRS Report invoice journal calculation issue

Helpful resources

Quick Links

Responsible AI policies

Pallavi Phade – Community Spotlight

Congratulations to the October Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates