Relation between TaxTrans and LedgerJournalTrans tables for vendor invoice journal report

(0) Share

Report

Posted on by D365FO DEV

406

Hi, im facing an issue with my ssrs report amount value. what is happening that if we have an invoice with 2 lines for example, with the SAME VOUCHER NUMBER, the amount value of the first line is coming correct, but for the second line its coming the same value as first line which is wrong.( check below screenshots)

as u can see in the second picturem the amount for the 2nd line is the same as the first line which is wrong. i believe the issue is with my rdp class with the relation. i also think a condition is missing since they have the same voucher number.

my relation is between two tables:taxtrans and ledgerjournaltrans.

select firstonly TaxTrans
where TaxTrans.JournalNum == LedgerJournalTrans.JournalNum &&
TaxTrans.Voucher == LedgerJournalTrans.Voucher;

any advise?

I have the same question (0)

All responses (2)

Answers (1)

Verified answer

Martin Dráb 239,040 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
I looked at LedgerJournalTrans relation on TaxTrans table and according to it, the right relation is this:
select firstonly taxTrans where taxTrans.SourceRecId == ledgerJournalTrans.RecId && taxTrans.SourceTableId == tableNum(LedgerJournalTrans);

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

Like (0)

Report
Copy link

Link copied!

thank you martin, your answer helped, but for some reason, a new error is showing which is showing for excluding vat:

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

here is my logic from the rdp class:

while select LedgerJournalTrans where LedgerJournalTrans.JournalNum == ledgerJournalId && LedgerJournalTrans.Voucher == voucher
{
if(ledgerjournaltrans.AccountType == LedgerJournalACType::Vend && LedgerJournalTrans.OffsetLedgerDimension == 0)
{
Ex_InvoiceJournalTmp.Ex_VATAmount = LedgerJournalTrans.totalTaxAmountSingleLine(true,false,true);
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;

}

Was this reply helpful? Yes No