TDS Register Report Development and Format in BC:
(1) In this blog we will do the development of the TDS Register report in export to excel format.
(2) For developing TDS Report: Use Dataitem: TDS Entry as shown.
Header Part:
Body Part:
Excel creation Part:
Source Code:
report 50012 “TDS Register” { UsageCategory = Administration; ApplicationArea = All; ProcessingOnly = True; dataset { dataitem(TDS_Entry; “TDS Entry”) { DataItemTableView = SORTING(“Document No.”); RequestFilterFields = “Posting Date”; trigger OnAfterGetRecord() begin VendorInv := ”; IF GetVendor.GET(“Party Code”) THEN; IF PurchInvHeader.GET(“Document No.”) THEN VendorInv := PurchInvHeader.”Vendor Invoice No.”; IF AssesseeCode.GET(“Assessee Code”) THEN; Narration := ”; PurchCommentLine.RESET; PurchCommentLine.SETRANGE(“No.”, “Document No.”); IF PurchCommentLine.FINDSET THEN BEGIN REPEAT Narration += PurchCommentLine.Comment; UNTIL PurchCommentLine.NEXT = 0; END; IF Narration = ” THEN BEGIN PostedNarration.RESET; PostedNarration.SETRANGE(“Document No.”, “Document No.”); IF PostedNarration.FINDSET THEN BEGIN REPEAT Narration += PostedNarration.Narration; UNTIL PostedNarration.NEXT = 0; END; END; MakeBody(); end; } } trigger OnPreReport() begin TempExcelBuffer.Reset(); TempExcelBuffer.DELETEALL(); MakeHeader; //Create Header Part end; trigger OnPostReport() begin CreateExcelBook; // Create Excel Book end; var TempExcelBuffer: Record “Excel Buffer” temporary; VendorInv: Code[80]; GetVendor: Record Vendor; PurchInvHeader: Record “Purch. Inv. Header”; AssesseeCode: Record “Assessee Code”; PurchCommentLine: Record “Purch. Comment Line”; Narration: Text; PostedNarration: Record “Gen. Journal Narration”; local procedure MakeHeader() var myInt: Integer; begin TempExcelBuffer.NewRow; TempExcelBuffer.AddColumn(‘TDSNatureof Deduction TDSEntry’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text); TempExcelBuffer.AddColumn(‘Posting Date’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text); TempExcelBuffer.AddColumn(‘Vendor Invoice No.’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text); TempExcelBuffer.AddColumn(‘Document No.’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text); TempExcelBuffer.AddColumn(‘Vendor No.’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text); TempExcelBuffer.AddColumn(‘Vendor Name’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text); TempExcelBuffer.AddColumn(‘Vendor Address’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text); TempExcelBuffer.AddColumn(‘Vendor State’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text); TempExcelBuffer.AddColumn(‘PAN No.’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text); TempExcelBuffer.AddColumn(‘Deductees Code’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text); TempExcelBuffer.AddColumn(‘Type of Deductees’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text); TempExcelBuffer.AddColumn(‘TDS Section TDSEntr’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text); TempExcelBuffer.AddColumn(‘TDS Base Amount’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text); TempExcelBuffer.AddColumn(‘TDS %’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text); TempExcelBuffer.AddColumn(‘TDS Amount’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text); TempExcelBuffer.AddColumn(‘Invoice Amount’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text); TempExcelBuffer.AddColumn(‘Narration’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text); end; local procedure MakeBody() var myInt: Integer; begin TempExcelBuffer.NewRow; TempExcelBuffer.AddColumn(TDS_Entry.”Nature of Remittance”, FALSE, ”, FALSE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text); TempExcelBuffer.AddColumn(TDS_Entry.”Posting Date”, FALSE, ”, FALSE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Number); TempExcelBuffer.AddColumn(VendorInv, FALSE, ”, FALSE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text); TempExcelBuffer.AddColumn(TDS_Entry.”Document No.”, FALSE, ”, FALSE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text); TempExcelBuffer.AddColumn(GetVendor.”No.”, FALSE, ”, FALSE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text); TempExcelBuffer.AddColumn(GetVendor.”Name”, FALSE, ”, FALSE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text); TempExcelBuffer.AddColumn(GetVendor.”Address”, FALSE, ”, FALSE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text); TempExcelBuffer.AddColumn(GetVendor.”State Code”, FALSE, ”, FALSE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text); TempExcelBuffer.AddColumn(GetVendor.”P.A.N. No.”, FALSE, ”, FALSE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text); TempExcelBuffer.AddColumn(TDS_Entry.”Assessee Code”, FALSE, ”, FALSE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text); TempExcelBuffer.AddColumn(AssesseeCode.Description, FALSE, ”, FALSE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text); TempExcelBuffer.AddColumn(TDS_Entry.Section, FALSE, ”, FALSE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text); TempExcelBuffer.AddColumn(TDS_Entry.”TDS Base Amount”, FALSE, ”, FALSE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Number); TempExcelBuffer.AddColumn(TDS_Entry.”TDS %”, FALSE, ”, FALSE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Number); TempExcelBuffer.AddColumn(TDS_Entry.”TDS Amount”, FALSE, ”, FALSE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Number); TempExcelBuffer.AddColumn(TDS_Entry.”Invoice Amount”, FALSE, ”, FALSE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Number); TempExcelBuffer.AddColumn(Narration, FALSE, ”, FALSE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text); end; Local procedure CreateExcelBook(); begin TempExcelBuffer.CreateNewBook(‘TDSRegister’); TempExcelBuffer.WriteSheet(‘TDSRegister’, CompanyName, UserId); TempExcelBuffer.CloseBook(); TempExcelBuffer.SetFriendlyFilename(‘TDSRegister’); TempExcelBuffer.OpenExcel(); end; } |