General ledger statement by main account | Dynamics 365

This article explains how to generate general ledger statements by main account in Microsoft Excel format. Skiptomaincontent Thisbrowserisnolongersupported. UpgradetoMicrosoftEdgetotakeadvantageofthelatestfeatures,securityupdates,andtechnicalsupport. DownloadMicrosoftEdge MoreinfoaboutInternetExplorerandMicrosoftEdge Tableofcontents Exitfocusmode ReadinEnglish Save Tableofcontents ReadinEnglish Save Feedback Edit Print Twitter LinkedIn Facebook Email Tableofcontents Generalledgerstatementbymainaccount Article 08/12/2022 8minutestoread 4contributors Inthisarticle ThisarticleexplainshowtouseElectronicReporting(ER)togeneratetheGeneralledgerstatementbymainaccountreportinMicrosoftExcelformat. TheGeneralledgerstatementbymainaccountreportinExcelformatisavailableinDynamics365Financeasofversion10.0.27. Inadditiontotheledgerreports,youcanusetheGeneralledgerstatementbymainaccountreportinExcelformattoviewgeneralledgertransactions.Foreachtransaction,thereportshowsthefollowingdetails. Columnname Datasourcedescription Transactionpostingdate Thepostingdateofthegeneraljournalentry:GeneralJournalEntry.AccountingDate. Transactionnumber Thevouchernumberofthegeneraljournalentry:GeneralJournalEntry.SubledgerVoucher. Mainaccount Themainaccountofthegeneraljournalaccountentry:GeneralJournalAccountEntry.MainAccount. Mainaccountname Thetranslationofthemainaccountnameintotheuser'spreferredlanguageforthemainaccountfromthegeneraljournalaccountentry:MainAccountTranslation.NamerelatedtoMainAccount.Name.Ifnotranslationisspecifiedfortheuser'spreferredlanguage,MainAccount.Nameisreported. Counterpartynumber WhenthepostingtypeofthegeneraljournalaccountentryisCustomerbalance,Vendorbalance,Customercollectionletterfee,orPennydifferenceinaccountingcurrency,thiscolumnshowsthecustomerorvendoraccountIDfromtherelatedcustomerorvendortransactionthatisfoundbasedonthevouchernumberandtransactiondatefromthegeneraljournalentry:CustTrans.AccountNumorVendTrans.AccountNumfoundbyGeneralJournalEntry.SubledgerVoucherandGeneralJournalEntry.AccountingDate. Counterpartyname ThenameofthecustomerorvendorfromtheGlobaladdressbookthatisrelatedtothecustomerorvendoraccountIDthatisreportedintheCounterpartynumbercolumn:DirPartyTable.Name. Ledgeraccount Theledgeraccount,includingdimensionsfromthegeneraljournalaccountentry:GeneralJournalAccountEntry.LedgerAccount. Description Thedescriptionfromthegeneraljournalaccountentry:GeneralJournalAccountEntry.Text. Transactioncurrency Thecurrencyofthegeneraljournalaccountentry:GeneralJournalAccountEntry.TransactionCurrencyCode. Debitintransactioncurrency TheamountinthesourcedocumentcurrencyfromthegeneraljournalaccountentrywheretheIsCreditpropertyisfalse:GeneralJournalAccountEntry.TransactionCurrencyAmountwhenIsCredit=false. Creditintransactioncurrency TheamountinthesourcedocumentcurrencyfromthegeneraljournalaccountentrywheretheIsCreditpropertyistrue:GeneralJournalAccountEntry.TransactionCurrencyAmountwhenIsCredit=true. Debit TheamountintheaccountingorreportingcurrencyfromthegeneraljournalaccountentrywheretheIsCreditpropertyisfalse:GeneralJournalAccountEntry.AccountingCurrencyAmountorGeneralJournalAccountEntry.ReportingCurrencyAmountwhenIsCredit=false. Credit TheamountintheaccountingorreportingcurrencyfromthegeneraljournalaccountentrywheretheIsCreditpropertyistrue:GeneralJournalAccountEntry.AccountingCurrencyAmountorGeneralJournalAccountEntry.ReportingCurrencyAmountwhenIsCredit=true. Accumulatedbalance Theaccumulatedbalanceiscalculatedbasedontheopeningbalanceatthebeginningofthereportingperiodandtheturnoverbymainaccountfromthebeginningofthereportingperioduntilthecurrentgeneralledgerentry. Salestaxcode IfageneraljournalaccountentryhasarelatedProfitandlosssalestaxentry,thiscolumnrepresentsthesalestaxcodesfromtherelatedsalestaxentries:TaxTrans.TaxCodefoundbyTaxTransGeneralJournalAccountEntry.TaxTransandTaxTransGeneralJournalAccountEntry.SubledgerJournaalAccountEntrywhereTaxTransGeneralJournalAccountEntry.TaxTransRelationship=TaxTransRelationshipType.TransactionLineAccount.Ifmultiplesalestaxentriesarerelatedtothegeneraljournalaccountentry,acomma(,)isusedasthedelimiterbetweensalestaxcodes. Salestaxamount IfageneraljournalaccountentryhasarelatedProfitandlosssalestaxentry,thiscolumnrepresentstheaggregatedsalestaxamountfromtherelatedsalestaxentries:TaxTrans.TaxAmountorTaxTrans.TaxAmountRepfoundbyTaxTransGeneralJournalAccountEntry.TaxTransandTaxTransGeneralJournalAccountEntry.SubledgerJournaalAccountEntrywhereTaxTransGeneralJournalAccountEntry.TaxTransRelationship=TaxTransRelationshipType.TransactionLineAccount. Generaljournalaccountentriesonthereportarepresentedingroupsbymainaccount.Foreachmainaccount,anopeningbalanceisreportedthatrepresentstheamountineithertheaccountingcurrencyorthereportingcurrencyatthebeginningofthereportingperiod.Eachmainaccountgroupofgeneraljournalaccountentriesisfinalizedbyusingalinethatpresentsthetotalamountofdebitandcreditturnoverattheendofreportingperiod,andalsotheaccumulatedbalancebymainaccountattheendofthereportingperiod. Theformatofthereportusesthelanguagethathasbeenselectedastheuser'spreferredlanguageinFinanceuseroptions. Note UseoftheOnevoucherfunctionalityintroducesareportinglimitationofthecounterpartnumberandnameforsomescenarios.WerecommendthatyousettheAllowmultipletransactionswithinonevoucheroptiontoNoontheGeneralledgerparameterspageinyourlegalentitytoensurethatthecounterpartnumberandnamearecorrectlydefinedontheGeneralledgerstatementbymainaccountreport. PrepareyourenvironmenttogenerateaGeneralledgerstatementbymainaccountreport TostarttoworkwiththeGeneralledgerstatementbymainaccountreport,completethefollowingtasks: ImportElectronicreporting(ER)configurations. SelecttheERconfigurationinGeneralledgerparameters. EnablefeaturesinFeaturemanagement. ImportERconfigurations BeforeyoucangenerateaGeneralledgerstatementbymainaccountreport,youmustimportthelatestversionsofthefollowingERconfigurations. ERconfigurationname Type Description StandardAuditFile(SAF-T) Model Thecommondatamodelfordifferentauditreports. SAF-TGeneralmodelmapping Modelmapping Themodelmappingthatprovidesgeneraldatasourcemapping. Generalledgerstatementbymainaccount(Excel) Format ThegeneralledgerstatementbymainaccountinExcelformat. FormoreinformationabouthowtodownloadERconfigurations,seeDownloadERconfigurationsfromtheGlobalrepository. Importthemostrecentversionsoftheconfigurations.TheversiondescriptionusuallyincludesthenumberoftheMicrosoftKnowledgeBase(KB)articlethatexplainsthechangesthatwereintroducedintheconfigurationversion.UsetheIssuesearchsectionoftheLCSportaltofindandreadinformationaboutspecificversionofERconfiguration. Important AfteralltheERconfigurationsareimported,settheDefaultformodelmappingoptiontoYesfortheSAF-TGeneralmodelmappingconfiguration. SelecttheERconfigurationinGeneralledgerparameters GotoGeneralledger>Setup>Generalledgerparameters. OntheLedgertab,ontheElectronicreportingFastTab,intheGeneralledgerstatementbymainaccountfield,selecttheGeneralledgerstatementbymainaccount(Excel)format. EnablefeaturesinFeaturemanagement Tooptimizethetimingandmemoryconsumptionofreportgeneration,werecommendthatyouenablesomefeaturesintheFeaturemanagementworkspace. OpentheFeaturemanagementworkspace. OntheAlltab,inthefeaturelist,findandselectthefollowingfeatures: OptimizationofquerydatasourcecreationtimeduringexecutionofERreports OptimizedatasetsmemoryconsumptionatERreportsruntime SelectEnablenow. GenerateaGeneralledgerstatementbymainaccountreport TogenerateaGeneralledgerstatementbymainaccountreport,followthesesteps. GotoGeneralledger>Inquiriesandreports>Ledgerreports>Generalledgerstatementbymainaccount. Inthereportdialogbox,setthefollowingfields. Fieldname Description Fromdate,Todate Selectdatestospecifythedateintervalforthereport.Youcanselectdateswithinonefiscalyear. Currency SelectAccountingcurrencytoreportamountsintheDebit,Credit,andAccumulatedbalancecolumnsofthereportintheaccountingcurrency.SelectReportingcurrencytoreportthoseamountsinthereportingcurrency. Mainfinancialdimensionset Selectthestandardfinancialdimensionset,includingthemainaccountthatthereportusestocalculatetheopeningbalancebymainaccountatthebeginningofthereportingperiod.Formoreinformationaboutfinancialdimensionsets,seeFinancialdimensionsets. Groupbymainaccount Selectthischeckboxtogroupgeneralledgeraccountentriesbymainaccountonthereport.Whenthischeckboxisselected,theLedgeraccountcolumnishidden,andtheamountsofthegeneralledgeraccountentriesthatarereportedforeachmainaccountarerepresentedasaggregatedamountsintheTransactionpostingdateandTransactionnumbercolumns. Includereversed Selectthischeckboxifreversedtransactionmustbereported. Postinglayer(s) Selectoneormorepostinglayertransactionstoincludeonthereport.Ifyouleavethisfieldblank,allthepostinglayersarereported. Salestaxspecification Selectthischeckboxtoincludeinformationaboutthesalestaxcodesandamountsforeachgeneralledgeraccountentry.ThisoptionisavailableonlyiftheGroupbymainaccountcheckboxiscleared. Settlementperiod Selectasettlementperiodtofiltersalestaxtransactionsonthereport.Ifyouleavethisfieldblank,salestaxtransactionsfromallsettlementperiodsareincludedonthereport. Splitfilebymainaccounts Selectthischeckboxtosplitthereportdataintomultiplefilesbymainaccount. YoucanusetheRecordstoincludeoptiontofilterthedataonthereportbyoneormoremainaccounts. OntheRuninthebackgroundFastTab,youcanspecifyparametersofthebatchjobandrunthereportinbatchmode.Whenanelectronicreportisgeneratedinbatchmode,youcanfindrelatedbatchinformationandthegeneratedoutputfileasanattachmentbygoingtoOrganizationadministration>Electronicreporting>Electronicreportingjobs.FormoreinformationabouthowtoconfigureadestinationforeachERformatconfigurationanditsoutputcomponent,seeElectronicreporting(ER)destinations. GenerateaGeneralledgerstatementsbymainaccountreportforalargevolumeofdata ThemaximumreportingperiodfortheGeneralledgerstatementbymainaccountreportisonefiscalyear. WhenyougeneratetheGeneralledgerstatementbymainaccountreportforareportingperiodthathasmorethanonemilliongeneraljournalaccountentries,werecommendthatyouselecttheSplitfilebymainaccountsreportoption.Then,ifthenumberofgeneraljournalaccountentriesforamainaccountexceedsonemillioninthereportingperiod,thefirstmilliongeneraljournalaccountentriesarereportedonthefirstsheetoftheExceloutputfilewhenthereportisgenerated.ThenextmilliongeneraljournalaccountentriesarethenreportedonthesecondsheetofthesameExceloutputfile,andsoon. IftheSplitfilebymainaccountsoptionisn'tselected,andthenumberofgeneraljournalaccountentriesinthereportingperiodexceedsonemillion,nooutputfileisgenerated,andyoureceivethefollowingmessage: Filecannotbegenerated.NumberoflinesinthereportdatasetexceedsmaxnumberoflinesononesheetinExcelfile.Useoptionsofthereporttoreducethedatainthereportor"Splitfilebymainaccounts"parameter. Helpusunderstand WewanttolearnmoreabouthowpeopleuseMicrosoft'scustomHelptoolkit.Takethesurvey(inEnglish)andhelpusunderstand:https://forms.office.com/r/A4cUJgjkD1. Feedback Submitandviewfeedbackfor Thisproduct Thispage Viewallpagefeedback Inthisarticle