Small and medium business | Business Central, N...

How to remove Repeated values

(0) Share

Report

Posted on by Maro9595

105

Hello All,

I have developed a report from VS code and designed a simple RDLC layout to print output, but when I run my report the first row is correct according to the formula but the following rows are repeating/duplicating with the same values while values are expected to vary according to the transaction, can someone please guide me on how to prevent duplicates.

below is my code and report output

report 50100 CustLedgerEntry

{

UsageCategory = ReportsAndAnalysis;

ApplicationArea = all;

Caption = 'Customer vs Balance';

DefaultLayout = RDLC;

RDLCLayout = 'CustomerBalance.rdl';

dataset

{

dataitem("Cust. Ledger Entry"; "Cust. Ledger Entry")

{

RequestFilterFields = "Customer No.", "Posting Date";

column(Posting_Date; "Posting Date") { }

column(Document_Type; "Document Type") { }

column(Document_No_; "Document No.") { }

column(Description; Description) { }

dataitem(Customer; Customer)

{

DataItemLink = "No." = field("Customer No.");

column(No_; "No.") { }

column(Balance__LCY_; "Balance (LCY)") { }

column(Debit_Amount__LCY_; "Debit Amount (LCY)") { }

column(Credit_Amount__LCY_; "Credit Amount (LCY)") { }

column(BalanceTotal; BalanceTotal) { }

trigger OnAfterGetRecord()

begin

BalanceTotal := "Balance (LCY)" + ("Debit Amount (LCY)" - "Credit Amount (LCY)");

end;

}

var

Cust: Record Customer;

BalanceTotal: Decimal;

}

Thank you for your support

Categories:

Dynamics 365 Business Central

All responses (13)

Answers (0)

Suggested answer

Steven Renders 5,512 Moderator on at

Like (0)

Report

RE: How to remove Repeated values

That is because you are fetching the ledger first and then the customer. The Customer fields are the same for every related ledger.

Try to start with the Customer data Item first, then go to the Customer Ledgers.

From the Customer Ledger data item, add the Amount field(s).
Suggested answer

Nitin Verma 21,569 Moderator on at

Like (0)

Report

RE: How to remove Repeated values

Hi,

You can refer my RDLC report design I sent you with an attachment in my last comment, and put your fields by replacing my fields and change your RDLC with my report design. This way it will help you to get your data in correct way.

Thanks.
Maro9595 105 on at

Like (0)

Report

RE: How to remove Repeated values

Hello Nitin,

Sorry, can you please guide me a bit on "Add all the fields into Group row, not in details row?"
Suggested answer

Nitin Verma 21,569 Moderator on at

Like (0)

Report

RE: How to remove Repeated values

Please check my last message with report layout and image it’s working fine for me.

Now you have to check why your custom fields is repeating the values you have to clear them in some other trigger
Maro9595 105 on at

Like (0)

Report

RE: How to remove Repeated values

Thank you Nitin,

for these three fields it works but including all fields and the formula of balance total column duplications starts
Suggested answer

Nitin Verma 21,569 Moderator on at

Like (0)

Report

RE: How to remove Repeated values

[View:/cfs-file/__key/communityserver-discussions-components-files/758/7144.CustomerBalance.rdlc:320:240]Hi,

Please find the attach RDLC layout after putting customer No. grouping.

Thanks.
Suggested answer

Nitin Verma 21,569 Moderator on at

Like (0)

Report

RE: How to remove Repeated values

Also please check your have to put the grouping on Document No. not customer No., and delete the Details Row from Table from report design layout.

Add all the fields into Group row not in details row.

See the image.[View:/cfs-file/__key/communityserver-discussions-components-files/758/CustomerBalance.rdlc:320:240]
Maro9595 105 on at

Like (0)

Report

RE: How to remove Repeated values

I have done so, but it pulling out all customers despite applying filters on the request page while I need the information of only one customer
Suggested answer

Nitin Verma 21,569 Moderator on at

Like (0)

Report

RE: How to remove Repeated values

Hi,

Can you please replace your report coding with below?

report 50100 CustLedgerEntry

{

UsageCategory = ReportsAndAnalysis;

ApplicationArea = all;

Caption = 'Customer vs Balance';

DefaultLayout = RDLC;

RDLCLayout = 'CustomerBalance.rdl';

dataset

{

dataitem(Customer; Customer)

{

column(No_; "No.") { }

column(Balance__LCY_; "Balance (LCY)") { }

column(Debit_Amount__LCY_; "Debit Amount (LCY)") { }

column(Credit_Amount__LCY_; "Credit Amount (LCY)") { }

column(BalanceTotal; BalanceTotal)

{ }

dataitem("Cust. Ledger Entry"; "Cust. Ledger Entry")

{

DataItemLinkReference = customer;

DataItemLink = "Customer No." = field("No.");

RequestFilterFields = "Customer No.", "Posting Date";

column(Posting_Date; "Posting Date") { }

column(Document_Type; "Document Type") { }

column(Document_No_; "Document No.") { }

column(Description; Description) { }

}

trigger OnAfterGetRecord()

begin

BalanceTotal := "Balance (LCY)" + ("Debit Amount (LCY)" - "Credit Amount (LCY)");

end;

}

}

var

Cust: Record Customer;

BalanceTotal: Decimal;

}
Maro9595 105 on at

Like (0)

Report

RE: How to remove Repeated values

Hi, Nitin once more,

I have deleted the layout completely and designed it again still the duplicates are there, then I tried to group by the customer no. (this shows only one record only first row but the rest are not showing)