Hello Expert,
one of my customer is running Custoemr detail trial balance report (104). They want to see customer name in the report header when we select customer no in request page. If no customer no selected in request page then customer name should be blank. Can you please help.
appreciated your help.
Amy
Thank you. It worked in cloud version
I’m doing it in RDLC report ( BC on prem). Did already tried the same but it didn’t work
The data item for the ledger entries should already be sorted by posting date, according to the data item table view.
Put if you want them report sorted on posting data after you have grouped it on document type i think you need to group it on both document type and posting date.
dataitem("Cust. Ledger Entry"; "Cust. Ledger Entry")
{
DataItemLink = "Customer No." = FIELD("No."), "Posting Date" = FIELD("Date Filter"), "Global Dimension 2 Code" = FIELD("Global Dimension 2 Filter"), "Global Dimension 1 Code" = FIELD("Global Dimension 1 Filter"), "Date Filter" = FIELD("Date Filter");
DataItemTableView = SORTING("Customer No.", "Posting Date");
Are you doing the grouping in a rdlc layout or where do you do the grouping?
I would group on posting data and then document type as the next grouping level.
I verified. One last thing , I have grouped on document type and wanted to do sorting on posting date but seems sorting is not happening properly. It would be great if you can help with that.
Hi Amy!
I am happy to hear that it solved your case.
Please help me and the community by marking the question as answered if you felt that my help was solving your case!
Good luck with the rest of your implementation.
It worked. Thank you so much. Much appreciated your help.
And i think this code should work even for the cloud version.
report 50104 "Customer - Detail Trial Bal.2" { DefaultLayout = RDLC; RDLCLayout = './CustomerDetailTrialBal.rdlc'; ApplicationArea = Basic, Suite; Caption = 'Customer - Detail Trial Bal.'; UsageCategory = ReportsAndAnalysis; dataset { dataitem(Customer; Customer) { DataItemTableView = SORTING("No."); PrintOnlyIfDetail = true; RequestFilterFields = "No.", "Search Name", "Customer Posting Group", "Date Filter"; column(TodayFormatted; Format(Today)) { } column(PeriodCustDatetFilter; StrSubstNo(Text000, CustDateFilter)) { } column(CompanyName; COMPANYPROPERTY.DisplayName) { } column(PrintAmountsInLCY; PrintAmountsInLCY) { } column(ExcludeBalanceOnly; ExcludeBalanceOnly) { } column(PrintDebitCredit; PrintDebitCredit) { } column(CustFilterCaption; TableCaption ': ' CustFilter) { } column(CustFilter; CustFilter) { } column(AmountCaption; AmountCaption) { } column(DebitAmountCaption; DebitLbl) { } column(CreditAmountCaption; CreditLbl) { } column(RemainingAmtCaption; RemainingAmtCaption) { } column(No_Cust; "No.") { } column(Name_Cust; Name) { } column(PageGroupNo; PageGroupNo) { } column(StartBalanceLCY; StartBalanceLCY) { AutoFormatType = 1; } column(CustBalanceLCY; CustBalanceLCY) { AutoFormatType = 1; } column(CustLedgerEntryAmtLCY; "Cust. Ledger Entry"."Amount (LCY)") { AutoFormatType = 1; } column(CustDetailTrialBalCaption; CustDetailTrialBalCaptionLbl) { } column(PageNoCaption; PageNoCaptionLbl) { } column(AllAmtsLCYCaption; AllAmtsLCYCaptionLbl) { } column(RepInclCustsBalCptn; RepInclCustsBalCptnLbl) { } column(PostingDateCaption; PostingDateCaptionLbl) { } column(DueDateCaption; DueDateCaptionLbl) { } column(BalanceLCYCaption; BalanceLCYCaptionLbl) { } column(AdjOpeningBalCaption; AdjOpeningBalCaptionLbl) { } column(BeforePeriodCaption; BeforePeriodCaptionLbl) { } column(TotalCaption; TotalCaptionLbl) { } column(OpeningBalCaption; OpeningBalCaptionLbl) { } column(ExternalDocNoCaption; ExternalDocNoCaptionLbl) { } //add new column column(CustNameForReportHeader; CustNameForReportHeader) { } dataitem("Cust. Ledger Entry"; "Cust. Ledger Entry") { DataItemLink = "Customer No." = FIELD("No."), "Posting Date" = FIELD("Date Filter"), "Global Dimension 2 Code" = FIELD("Global Dimension 2 Filter"), "Global Dimension 1 Code" = FIELD("Global Dimension 1 Filter"), "Date Filter" = FIELD("Date Filter"); DataItemTableView = SORTING("Customer No.", "Posting Date"); column(PostDate_CustLedgEntry; Format("Posting Date")) { } column(DocType_CustLedgEntry; "Document Type") { IncludeCaption = true; } column(DocNo_CustLedgEntry; "Document No.") { IncludeCaption = true; } column(ExtDocNo_CustLedgEntry; "External Document No.") { } column(Desc_CustLedgEntry; Description) { IncludeCaption = true; } column(CustAmount; CustAmount) { AutoFormatExpression = CustCurrencyCode; AutoFormatType = 1; } column(CustDebitAmount; CustDebitAmount) { AutoFormatExpression = CustCurrencyCode; AutoFormatType = 1; } column(CustCreditAmount; CustCreditAmount) { AutoFormatExpression = CustCurrencyCode; AutoFormatType = 1; } column(CustRemainAmount; CustRemainAmount) { AutoFormatExpression = CustCurrencyCode; AutoFormatType = 1; } column(CustEntryDueDate; Format(CustEntryDueDate)) { } column(EntryNo_CustLedgEntry; "Entry No.") { IncludeCaption = true; } column(CustCurrencyCode; CustCurrencyCode) { } column(CustBalanceLCY1; CustBalanceLCY) { AutoFormatType = 1; } trigger OnAfterGetRecord() begin CustLedgEntryExists := true; if PrintAmountsInLCY then begin CustAmount := "Amount (LCY)"; CustRemainAmount := "Remaining Amt. (LCY)"; CustCurrencyCode := ''; end else begin CustAmount := Amount; CustRemainAmount := "Remaining Amount"; CustCurrencyCode := "Currency Code"; end; CustDebitAmount := 0; CustCreditAmount := 0; if CustAmount > 0 then CustDebitAmount := CustAmount else CustCreditAmount := -CustAmount; CustTotalDebitAmount = CustDebitAmount; CustTotalCreditAmount = CustCreditAmount; CustBalanceLCY := CustBalanceLCY "Amount (LCY)"; if ("Document Type" = "Document Type"::Payment) or ("Document Type" = "Document Type"::Refund) then CustEntryDueDate := 0D else CustEntryDueDate := "Due Date"; end; trigger OnPreDataItem() begin CustLedgEntryExists := false; CustTotalDebitAmount := 0; CustTotalCreditAmount := 0; CustAmount := 0; CustDebitAmount := 0; CustCreditAmount := 0; SetAutoCalcFields(Amount, "Remaining Amount", "Amount (LCY)", "Remaining Amt. (LCY)"); end; } dataitem("Integer"; "Integer") { DataItemTableView = SORTING(Number) WHERE(Number = CONST(1)); column(Name1_Cust; Customer.Name) { } column(CustBalanceLCY4; CustBalanceLCY) { AutoFormatType = 1; } column(StartBalanceLCY2; StartBalanceLCY) { } column(CustTotalDebitAmount; CustTotalDebitAmount) { } column(CustTotalCreditAmount; CustTotalCreditAmount) { } trigger OnAfterGetRecord() begin if not CustLedgEntryExists and ((StartBalanceLCY = 0) or ExcludeBalanceOnly) then begin StartBalanceLCY := 0; CurrReport.Skip(); end; end; } trigger OnAfterGetRecord() begin if PrintOnlyOnePerPage then PageGroupNo := PageGroupNo 1; StartBalanceLCY := 0; if CustDateFilter <> '' then begin if GetRangeMin("Date Filter") <> 0D then begin SetRange("Date Filter", 0D, GetRangeMin("Date Filter") - 1); CalcFields("Net Change (LCY)"); StartBalanceLCY := "Net Change (LCY)"; end; SetFilter("Date Filter", CustDateFilter); end; //workAroundNeeded if in cloud //CurrReport.PrintOnlyIfDetail := ExcludeBalanceOnly or (StartBalanceLCY = 0); if ExcludeBalanceOnly or (StartBalanceLCY = 0) then CurrReport.Skip(); CustBalanceLCY := StartBalanceLCY; //FetchCustomerName if SingleCustomer then CustNameForReportHeader := Name; end; trigger OnPreDataItem() var begin PageGroupNo := 1; Clear(StartBalanceLCY); if count = 1 then SingleCustomer := true; end; } } requestpage { SaveValues = true; layout { area(content) { group(Options) { Caption = 'Options'; field(ShowAmountsInLCY; PrintAmountsInLCY) { ApplicationArea = Basic, Suite; Caption = 'Show Amounts in LCY'; ToolTip = 'Specifies if the reported amounts are shown in the local currency.'; } field(NewPageperCustomer; PrintOnlyOnePerPage) { ApplicationArea = Basic, Suite; Caption = 'New Page per Customer'; ToolTip = 'Specifies if each customer''s information is printed on a new page if you have chosen two or more customers to be included in the report.'; } field(ExcludeCustHaveaBalanceOnly; ExcludeBalanceOnly) { ApplicationArea = Basic, Suite; Caption = 'Exclude Customers That Have a Balance Only'; MultiLine = true; ToolTip = 'Specifies if you do not want the report to include entries for customers that have a balance but do not have a net change during the selected time period.'; } } } } actions { } } labels { } trigger OnPreReport() var FormatDocument: Codeunit "Format Document"; begin GeneralLedgerSetup.Get(); PrintDebitCredit := GeneralLedgerSetup."Show Amounts" = GeneralLedgerSetup."Show Amounts"::"Debit/Credit Only"; CustFilter := FormatDocument.GetRecordFiltersWithCaptions(Customer); CustDateFilter := Customer.GetFilter("Date Filter"); with "Cust. Ledger Entry" do if PrintAmountsInLCY then begin AmountCaption := FieldCaption("Amount (LCY)"); RemainingAmtCaption := FieldCaption("Remaining Amt. (LCY)"); end else begin AmountCaption := FieldCaption(Amount); RemainingAmtCaption := FieldCaption("Remaining Amount"); end; end; var Text000: Label 'Period: %1'; GeneralLedgerSetup: Record "General Ledger Setup"; PrintDebitCredit: Boolean; PrintAmountsInLCY: Boolean; PrintOnlyOnePerPage: Boolean; ExcludeBalanceOnly: Boolean; CustFilter: Text; CustDateFilter: Text; AmountCaption: Text[80]; RemainingAmtCaption: Text[30]; CustAmount: Decimal; CustDebitAmount: Decimal; CustCreditAmount: Decimal; CustTotalDebitAmount: Decimal; CustTotalCreditAmount: Decimal; CustRemainAmount: Decimal; CustBalanceLCY: Decimal; CustCurrencyCode: Code[10]; CustEntryDueDate: Date; StartBalanceLCY: Decimal; CustLedgEntryExists: Boolean; PageGroupNo: Integer; CustDetailTrialBalCaptionLbl: Label 'Customer - Detail Trial Bal.'; PageNoCaptionLbl: Label 'Page'; AllAmtsLCYCaptionLbl: Label 'All amounts are in LCY'; RepInclCustsBalCptnLbl: Label 'This report also includes customers that only have balances.'; PostingDateCaptionLbl: Label 'Posting Date'; DueDateCaptionLbl: Label 'Due Date'; BalanceLCYCaptionLbl: Label 'Balance (LCY)'; AdjOpeningBalCaptionLbl: Label 'Adj. of Opening Balance'; BeforePeriodCaptionLbl: Label 'Total (LCY) Before Period'; TotalCaptionLbl: Label 'Total (LCY)'; OpeningBalCaptionLbl: Label 'Total Adj. of Opening Balance'; DebitLbl: Label 'Debit Amount'; CreditLbl: Label 'Credit Amount'; ExternalDocNoCaptionLbl: Label 'External Doc. No.'; //New variables SingleCustomer: Boolean; CustNameForReportHeader: Text[50]; procedure InitializeRequest(ShowAmountInLCY: Boolean; SetPrintOnlyOnePerPage: Boolean; SetExcludeBalanceOnly: Boolean) begin PrintOnlyOnePerPage := SetPrintOnlyOnePerPage; PrintAmountsInLCY := ShowAmountInLCY; ExcludeBalanceOnly := SetExcludeBalanceOnly; end; }
I created a copy of the original report.
report 50104 "Customer - Detail Trial Bal.2" { DefaultLayout = RDLC; RDLCLayout = './CustomerDetailTrialBal.rdlc'; ApplicationArea = Basic, Suite; Caption = 'Customer - Detail Trial Bal.'; UsageCategory = ReportsAndAnalysis; dataset { dataitem(Customer; Customer) { DataItemTableView = SORTING("No."); PrintOnlyIfDetail = true; RequestFilterFields = "No.", "Search Name", "Customer Posting Group", "Date Filter"; column(TodayFormatted; Format(Today)) { } column(PeriodCustDatetFilter; StrSubstNo(Text000, CustDateFilter)) { } column(CompanyName; COMPANYPROPERTY.DisplayName) { } column(PrintAmountsInLCY; PrintAmountsInLCY) { } column(ExcludeBalanceOnly; ExcludeBalanceOnly) { } column(PrintDebitCredit; PrintDebitCredit) { } column(CustFilterCaption; TableCaption ': ' CustFilter) { } column(CustFilter; CustFilter) { } column(AmountCaption; AmountCaption) { } column(DebitAmountCaption; DebitLbl) { } column(CreditAmountCaption; CreditLbl) { } column(RemainingAmtCaption; RemainingAmtCaption) { } column(No_Cust; "No.") { } column(Name_Cust; Name) { } column(PageGroupNo; PageGroupNo) { } column(StartBalanceLCY; StartBalanceLCY) { AutoFormatType = 1; } column(CustBalanceLCY; CustBalanceLCY) { AutoFormatType = 1; } column(CustLedgerEntryAmtLCY; "Cust. Ledger Entry"."Amount (LCY)") { AutoFormatType = 1; } column(CustDetailTrialBalCaption; CustDetailTrialBalCaptionLbl) { } column(PageNoCaption; PageNoCaptionLbl) { } column(AllAmtsLCYCaption; AllAmtsLCYCaptionLbl) { } column(RepInclCustsBalCptn; RepInclCustsBalCptnLbl) { } column(PostingDateCaption; PostingDateCaptionLbl) { } column(DueDateCaption; DueDateCaptionLbl) { } column(BalanceLCYCaption; BalanceLCYCaptionLbl) { } column(AdjOpeningBalCaption; AdjOpeningBalCaptionLbl) { } column(BeforePeriodCaption; BeforePeriodCaptionLbl) { } column(TotalCaption; TotalCaptionLbl) { } column(OpeningBalCaption; OpeningBalCaptionLbl) { } column(ExternalDocNoCaption; ExternalDocNoCaptionLbl) { } //add new column column(CustNameForReportHeader; CustNameForReportHeader) { } dataitem("Cust. Ledger Entry"; "Cust. Ledger Entry") { DataItemLink = "Customer No." = FIELD("No."), "Posting Date" = FIELD("Date Filter"), "Global Dimension 2 Code" = FIELD("Global Dimension 2 Filter"), "Global Dimension 1 Code" = FIELD("Global Dimension 1 Filter"), "Date Filter" = FIELD("Date Filter"); DataItemTableView = SORTING("Customer No.", "Posting Date"); column(PostDate_CustLedgEntry; Format("Posting Date")) { } column(DocType_CustLedgEntry; "Document Type") { IncludeCaption = true; } column(DocNo_CustLedgEntry; "Document No.") { IncludeCaption = true; } column(ExtDocNo_CustLedgEntry; "External Document No.") { } column(Desc_CustLedgEntry; Description) { IncludeCaption = true; } column(CustAmount; CustAmount) { AutoFormatExpression = CustCurrencyCode; AutoFormatType = 1; } column(CustDebitAmount; CustDebitAmount) { AutoFormatExpression = CustCurrencyCode; AutoFormatType = 1; } column(CustCreditAmount; CustCreditAmount) { AutoFormatExpression = CustCurrencyCode; AutoFormatType = 1; } column(CustRemainAmount; CustRemainAmount) { AutoFormatExpression = CustCurrencyCode; AutoFormatType = 1; } column(CustEntryDueDate; Format(CustEntryDueDate)) { } column(EntryNo_CustLedgEntry; "Entry No.") { IncludeCaption = true; } column(CustCurrencyCode; CustCurrencyCode) { } column(CustBalanceLCY1; CustBalanceLCY) { AutoFormatType = 1; } trigger OnAfterGetRecord() begin CustLedgEntryExists := true; if PrintAmountsInLCY then begin CustAmount := "Amount (LCY)"; CustRemainAmount := "Remaining Amt. (LCY)"; CustCurrencyCode := ''; end else begin CustAmount := Amount; CustRemainAmount := "Remaining Amount"; CustCurrencyCode := "Currency Code"; end; CustDebitAmount := 0; CustCreditAmount := 0; if CustAmount > 0 then CustDebitAmount := CustAmount else CustCreditAmount := -CustAmount; CustTotalDebitAmount = CustDebitAmount; CustTotalCreditAmount = CustCreditAmount; CustBalanceLCY := CustBalanceLCY "Amount (LCY)"; if ("Document Type" = "Document Type"::Payment) or ("Document Type" = "Document Type"::Refund) then CustEntryDueDate := 0D else CustEntryDueDate := "Due Date"; end; trigger OnPreDataItem() begin CustLedgEntryExists := false; CustTotalDebitAmount := 0; CustTotalCreditAmount := 0; CustAmount := 0; CustDebitAmount := 0; CustCreditAmount := 0; SetAutoCalcFields(Amount, "Remaining Amount", "Amount (LCY)", "Remaining Amt. (LCY)"); end; } dataitem("Integer"; "Integer") { DataItemTableView = SORTING(Number) WHERE(Number = CONST(1)); column(Name1_Cust; Customer.Name) { } column(CustBalanceLCY4; CustBalanceLCY) { AutoFormatType = 1; } column(StartBalanceLCY2; StartBalanceLCY) { } column(CustTotalDebitAmount; CustTotalDebitAmount) { } column(CustTotalCreditAmount; CustTotalCreditAmount) { } trigger OnAfterGetRecord() begin if not CustLedgEntryExists and ((StartBalanceLCY = 0) or ExcludeBalanceOnly) then begin StartBalanceLCY := 0; CurrReport.Skip(); end; end; } trigger OnAfterGetRecord() begin if PrintOnlyOnePerPage then PageGroupNo := PageGroupNo 1; StartBalanceLCY := 0; if CustDateFilter <> '' then begin if GetRangeMin("Date Filter") <> 0D then begin SetRange("Date Filter", 0D, GetRangeMin("Date Filter") - 1); CalcFields("Net Change (LCY)"); StartBalanceLCY := "Net Change (LCY)"; end; SetFilter("Date Filter", CustDateFilter); end; //workAroundNeeded if in cloud CurrReport.PrintOnlyIfDetail := ExcludeBalanceOnly or (StartBalanceLCY = 0); CustBalanceLCY := StartBalanceLCY; //FetchCustomerName if SingleCustomer then CustNameForReportHeader := Name; end; trigger OnPreDataItem() var begin PageGroupNo := 1; Clear(StartBalanceLCY); if count = 1 then SingleCustomer := true; end; } } requestpage { SaveValues = true; layout { area(content) { group(Options) { Caption = 'Options'; field(ShowAmountsInLCY; PrintAmountsInLCY) { ApplicationArea = Basic, Suite; Caption = 'Show Amounts in LCY'; ToolTip = 'Specifies if the reported amounts are shown in the local currency.'; } field(NewPageperCustomer; PrintOnlyOnePerPage) { ApplicationArea = Basic, Suite; Caption = 'New Page per Customer'; ToolTip = 'Specifies if each customer''s information is printed on a new page if you have chosen two or more customers to be included in the report.'; } field(ExcludeCustHaveaBalanceOnly; ExcludeBalanceOnly) { ApplicationArea = Basic, Suite; Caption = 'Exclude Customers That Have a Balance Only'; MultiLine = true; ToolTip = 'Specifies if you do not want the report to include entries for customers that have a balance but do not have a net change during the selected time period.'; } } } } actions { } } labels { } trigger OnPreReport() var FormatDocument: Codeunit "Format Document"; begin GeneralLedgerSetup.Get(); PrintDebitCredit := GeneralLedgerSetup."Show Amounts" = GeneralLedgerSetup."Show Amounts"::"Debit/Credit Only"; CustFilter := FormatDocument.GetRecordFiltersWithCaptions(Customer); CustDateFilter := Customer.GetFilter("Date Filter"); with "Cust. Ledger Entry" do if PrintAmountsInLCY then begin AmountCaption := FieldCaption("Amount (LCY)"); RemainingAmtCaption := FieldCaption("Remaining Amt. (LCY)"); end else begin AmountCaption := FieldCaption(Amount); RemainingAmtCaption := FieldCaption("Remaining Amount"); end; end; var Text000: Label 'Period: %1'; GeneralLedgerSetup: Record "General Ledger Setup"; PrintDebitCredit: Boolean; PrintAmountsInLCY: Boolean; PrintOnlyOnePerPage: Boolean; ExcludeBalanceOnly: Boolean; CustFilter: Text; CustDateFilter: Text; AmountCaption: Text[80]; RemainingAmtCaption: Text[30]; CustAmount: Decimal; CustDebitAmount: Decimal; CustCreditAmount: Decimal; CustTotalDebitAmount: Decimal; CustTotalCreditAmount: Decimal; CustRemainAmount: Decimal; CustBalanceLCY: Decimal; CustCurrencyCode: Code[10]; CustEntryDueDate: Date; StartBalanceLCY: Decimal; CustLedgEntryExists: Boolean; PageGroupNo: Integer; CustDetailTrialBalCaptionLbl: Label 'Customer - Detail Trial Bal.'; PageNoCaptionLbl: Label 'Page'; AllAmtsLCYCaptionLbl: Label 'All amounts are in LCY'; RepInclCustsBalCptnLbl: Label 'This report also includes customers that only have balances.'; PostingDateCaptionLbl: Label 'Posting Date'; DueDateCaptionLbl: Label 'Due Date'; BalanceLCYCaptionLbl: Label 'Balance (LCY)'; AdjOpeningBalCaptionLbl: Label 'Adj. of Opening Balance'; BeforePeriodCaptionLbl: Label 'Total (LCY) Before Period'; TotalCaptionLbl: Label 'Total (LCY)'; OpeningBalCaptionLbl: Label 'Total Adj. of Opening Balance'; DebitLbl: Label 'Debit Amount'; CreditLbl: Label 'Credit Amount'; ExternalDocNoCaptionLbl: Label 'External Doc. No.'; //New variables SingleCustomer: Boolean; CustNameForReportHeader: Text[50]; procedure InitializeRequest(ShowAmountInLCY: Boolean; SetPrintOnlyOnePerPage: Boolean; SetExcludeBalanceOnly: Boolean) begin PrintOnlyOnePerPage := SetPrintOnlyOnePerPage; PrintAmountsInLCY := ShowAmountInLCY; ExcludeBalanceOnly := SetExcludeBalanceOnly; end; }
This code will create a new text variable CustNameForReportHeader that you can put in the report header layout.
If you are onprem the code should work as it is. If you are in the cloud you need to do a work around for the PrintOnlyIfDetail property because that is not supported in the cloud yet.
You are right. But they just want to see the name when running for a single customer. However could you please help with that. I tried few but didn’t work
To fix that you will have to do a customization for the report. But i do not really understand the need for it because the name of the customer is printed before the listing of the transactions starts.
So if it was my customer i think i would go back to them and ask again if this is really necessary and show them where the customer name is printed.
But if it is really needed it is a pretty small customization to make, but i do not see the value it gives.
Sohail Ahmed
2,655
Mansi Soni
1,574
YUN ZHU
1,453
Super User 2025 Season 1