web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / X++ code : Extract Tri...
Finance | Project Operations, Human Resources, ...
Active Discussion

X++ code : Extract Trial Balance from D365 F&O to .CSV file

(0) ShareShare
ReportReport
Posted on by Deepak 22

Extract Trial balance in CSV file using Sys-operation Framework
1. Create Contract class


[
DataContractAttribute,
SysOperationContractProcessingAttribute(classstr(TrialBalanceUIBuilder)),
SysOperationGroupAttribute('Focuses', “”, '1'),
SysOperationGroupAttribute('Date', "@SYS7402", '2'),
SysOperationGroupAttribute('Frequency', “”, '3'),
SysOperationGroupAttribute('Manual', “”, '4')
]

class TrialBalanceContract implements SysOperationValidatable
{
TransDate fromDate;
TransDate toDate;
DimensionsLedgerTransSpecDim specification;
int frequencyDays;
NoYesId manualRun;
Name primaryDimensionFocus;


[
DataMemberAttribute('ManualRun'),
SysOperationLabelAttribute(literalstr("@")),
SysOperationHelpTextAttribute(literalstr("@")),
SysOperationGroupMemberAttribute('Manual'),
SysOperationDisplayOrderAttribute('4')
]
public NoYesId parmManualRun(NoYesId _manualRun = manualRun)
{
manualRun = _manualRun;
return manualRun;
}

[
DataMemberAttribute('FromDate'),
SysOperationLabelAttribute(literalstr("@SYS5209")),
SysOperationHelpTextAttribute(literalstr("@SYS67")),
SysOperationGroupMemberAttribute('Date'),
SysOperationDisplayOrderAttribute('1')
]
public TransDate parmFromDate(TransDate _fromDate = fromDate)
{
fromDate = _fromDate;
return fromDate;
}

[
DataMemberAttribute('PrimaryDimensionFocus'),
SysOperationLabelAttribute(literalstr("@")),
SysOperationGroupMemberAttribute('Focuses'),
SysOperationDisplayOrderAttribute('3')
]
public Name parmPrimaryDimensionFocus(Name _primaryDimensionFocus = primaryDimensionFocus)
{
primaryDimensionFocus = _primaryDimensionFocus;
return primaryDimensionFocus;
}


[
DataMemberAttribute('ToDate'),
SysOperationLabelAttribute(literalstr("@SYS14656")),
SysOperationHelpTextAttribute(literalstr("@SYS67")),
SysOperationGroupMemberAttribute('Date'),
SysOperationDisplayOrderAttribute('2')
]
public TransDate parmToDate(TransDate _toDate = toDate)
{
toDate = _toDate;
return toDate;
}

public boolean validate()
{
boolean isValid = true;
FiscalCalendarRecId calendarRecId;
TransDate toDatePeriodStart;
TransDate fromDatePeriodStart;
recId primaryFocusRecId;
recId secondaryFocusRecId;

calendarRecId = Ledger::fiscalCalendar(CompanyInfo::find().RecId);

if (fromDate && toDate)
{
if (fromDate > toDate)
{
isValid = checkFailed(strfmt("@SYS55013", date2StrUsr(fromDate, DateFlags::FormatAll), date2StrUsr(toDate, DateFlags::FormatAll)));
}

fromDatePeriodStart = LedgerFiscalCalendar::findOpeningStartDateByDate(calendarRecId, fromDate);
toDatePeriodStart = LedgerFiscalCalendar::findOpeningStartDateByDate(calendarRecId, toDate);

if (fromDatePeriodStart != toDatePeriodStart)
{
isValid = checkFailed(strfmt("@SYS28745", year(fromDatePeriodStart), year(toDatePeriodStart)));
}
}

primaryFocusRecId = DimensionHierarchy::findByTypeAndName(DimensionHierarchyType::Focus, this.parmPrimaryDimensionFocus()).RecId;

if (!primaryFocusRecId)
{
isValid = checkFailed("@");
}

return isValid;
}

}

2. Create controller class
[SysOperationJournaledParametersAttribute(true)]
class USMFTrialBalanceController extends SysOperationServiceController
{

public static void main(Args _args)
{
USMFTrialBalanceController controller =
USMFTrialBalanceController::construct();
controller.parmArgs(_args);
controller.startOperation();
}

public static USMFTrialBalanceController construct(
SysOperationExecutionMode _executionMode =
SysOperationExecutionMode::Asynchronous)
{
USMFTrialBalanceController controller =
new USMFTrialBalanceController();
controller.parmExecutionMode(_executionMode);
return controller;
}

public void new()
{
super(classStr(USMFTrialBalanceService),
methodStr(USMFTrialBalanceService,
processData),
SysOperationExecutionMode::Asynchronous);
}

}

class USMFTrialBalanceService
{
const str staticalAccCategory = “OTHERCA";
public void processData(USMFTrialBalanceContract _dataContract)
{
GeneralJournalEntry generalJournalEntry,generalJournalEntry_Loc;
GeneralJournalAccountEntry generalJournalAccountEntry;
SubledgerVoucherGeneralJournalEntry voucherGeneralJournalEntry;
FiscalCalendarPeriod fiscalCalPeriod;
Ledger ledger;
CompanyInfo companyInfo;
SubledgerVoucherGeneralJournalEntry SubledgerVoucherGeneralJournalEntry;
TaxTransGeneralJournalAccountEntry taxTransGeneralAccEntry;
TaxTrans taxTrans;
DimensionAttributeValueCombination dimensionAttributeValueCombination;
MainAccount mainAccount;
MainAccountCategory mainAccountCategory;
DimensionHierarchyLevel dimensionHierarchyLevel;
USMFLedgerTransStatementStagingTmp USMFLedgerTransStatementStagingTmp, USMFLedgerTransStatementStagingTmp1;
TransDate fromDate;
TransDate toDate;
NoYesId manualRun;
Name primaryDimensionFocus;
MainAccountNum mainAccountId;
UserConnection con = new UserConnection();
USMFTrialBalanceService trialBalanceService = new USMFTrialBalanceService();
USMFTrialBalanceParameters trialBalanceParameter;
DimensionAttributeValueSetStorage dimStorage;
DimensionValue dimensionValue;
DimensionDefault defaultDimension;
int i;
DimensionAttributeValueCombination dimValueCombi;
;

fromDate = _dataContract.parmFromDate();
toDate = _dataContract.parmToDate();
primaryDimensionFocus = _dataContract.parmPrimaryDimensionFocus();
manualRun = _dataContract.parmManualRun();

delete_from USMFLedgerTransStatementStagingTmp;

if(!manualRun)
{
select trialBalanceParameter;

fromDate = today()-trialBalanceParameter.BackDatedEntriesDays ;
toDate = today()-1;


while select generalJournalAccountEntry
join JournalNumber, PostingLayer,AccountingDate,RecId,Ledger,SubledgerVoucher,FiscalCalendarPeriod
from generalJournalEntry
where generalJournalEntry.RecId == generalJournalAccountEntry.GeneralJournalEntry
&& generalJournalEntry.AccountingDate >= fromDate
&& generalJournalEntry.AccountingDate <= toDate

join Voucher
from SubledgerVoucherGeneralJournalEntry
where SubledgerVoucherGeneralJournalEntry.GeneralJournalEntry == generalJournalEntry.RecId
join Type from fiscalCalPeriod
where fiscalCalPeriod.RecId == generalJournalEntry.FiscalCalendarPeriod
&& fiscalCalPeriod.Type == FiscalPeriodType::Operating
exists join ledger
where ledger.RecId == generalJournalEntry.Ledger
exists join companyInfo
where companyInfo.RecId == ledger.PrimaryForLegalEntity
&& companyInfo.DataArea == curext()
{

if(generalJournalAccountEntry.RecId)
{
ttsbegin;

USMFLedgerTransStatementStagingTmp.clear();
USMFLedgerTransStatementStagingTmp.TransDate = GeneralJournalEntry.AccountingDate;
USMFLedgerTransStatementStagingTmp.GeneralJournalEntry = GeneralJournalEntry.RecId;
USMFLedgerTransStatementStagingTmp.Voucher = SubledgerVoucherGeneralJournalEntry.Voucher;
USMFLedgerTransStatementStagingTmp.MainAccountId = MainAccount::findByLedgerDimension(GeneralJournalAccountEntry.LedgerDimension).MainAccountId;

if(generalJournalAccountEntry.IsCredit)
{
USMFLedgerTransStatementStagingTmp.AmountCredit = GeneralJournalAccountEntry.AccountingCurrencyAmount * -1;
}
else
{
USMFLedgerTransStatementStagingTmp.AmountDebit = GeneralJournalAccountEntry.AccountingCurrencyAmount;
}

USMFLedgerTransStatementStagingTmp.GeneralJournalAccountEntry = GeneralJournalAccountEntry.RecId;
USMFLedgerTransStatementStagingTmp.LedgerDimension = GeneralJournalAccountEntry.LedgerDimension;
USMFLedgerTransStatementStagingTmp.TransTxt = GeneralJournalAccountEntry.Text;
USMFLedgerTransStatementStagingTmp.GeneralJournalEntryDataArea = curExt();
USMFLedgerTransStatementStagingTmp.CurrencyCode = GeneralJournalAccountEntry.TransactionCurrencyCode;
USMFLedgerTransStatementStagingTmp.LedgerAccount = generalJournalAccountEntry.LedgerAccount;
taxTrans.clear();
if (generalJournalAccountEntry.PostingType == LedgerPostingType::Tax)
taxTrans = trialBalanceService.findTaxCode(GeneralJournalAccountEntry);

if(taxTrans.RecId)
{
USMFLedgerTransStatementStagingTmp.TaxCode = taxTrans.TaxCode;
}

USMFLedgerTransStatementStagingTmp.insert();

ttscommit;
}
}
}

if(manualRun)
{

while select generalJournalAccountEntry
join JournalNumber, PostingLayer,AccountingDate,RecId,Ledger,SubledgerVoucher,FiscalCalendarPeriod
from generalJournalEntry
where generalJournalEntry.RecId == generalJournalAccountEntry.GeneralJournalEntry
&& generalJournalEntry.AccountingDate >= fromDate
&& generalJournalEntry.AccountingDate <= toDate
join Voucher
from SubledgerVoucherGeneralJournalEntry
where SubledgerVoucherGeneralJournalEntry.GeneralJournalEntry == generalJournalEntry.RecId
join Type from fiscalCalPeriod
where fiscalCalPeriod.RecId == generalJournalEntry.FiscalCalendarPeriod
&& fiscalCalPeriod.Type == FiscalPeriodType::Operating
exists join ledger
where ledger.RecId == generalJournalEntry.Ledger
exists join companyInfo
where companyInfo.RecId == ledger.PrimaryForLegalEntity
&& companyInfo.DataArea == curext()
{

if(generalJournalAccountEntry.RecId)
{
ttsbegin;

USMFLedgerTransStatementStagingTmp.clear();
USMFLedgerTransStatementStagingTmp.TransDate = GeneralJournalEntry.AccountingDate;
USMFLedgerTransStatementStagingTmp.GeneralJournalEntry = GeneralJournalEntry.RecId;
USMFLedgerTransStatementStagingTmp.Voucher = SubledgerVoucherGeneralJournalEntry.Voucher;
USMFLedgerTransStatementStagingTmp.MainAccountId = MainAccount::findByLedgerDimension(GeneralJournalAccountEntry.LedgerDimension).MainAccountId;

if(generalJournalAccountEntry.IsCredit)
{
USMFLedgerTransStatementStagingTmp.AmountCredit = GeneralJournalAccountEntry.AccountingCurrencyAmount * -1;
}
else
{
USMFLedgerTransStatementStagingTmp.AmountDebit = GeneralJournalAccountEntry.AccountingCurrencyAmount;
}

USMFLedgerTransStatementStagingTmp.GeneralJournalAccountEntry = GeneralJournalAccountEntry.RecId;
USMFLedgerTransStatementStagingTmp.LedgerDimension = GeneralJournalAccountEntry.LedgerDimension;
USMFLedgerTransStatementStagingTmp.TransTxt = GeneralJournalAccountEntry.Text;
USMFLedgerTransStatementStagingTmp.GeneralJournalEntryDataArea = curExt();
USMFLedgerTransStatementStagingTmp.CurrencyCode = GeneralJournalAccountEntry.TransactionCurrencyCode;
USMFLedgerTransStatementStagingTmp.LedgerAccount = generalJournalAccountEntry.LedgerAccount;
taxTrans.clear();
if (generalJournalAccountEntry.PostingType == LedgerPostingType::Tax)
taxTrans = trialBalanceService.findTaxCode(GeneralJournalAccountEntry);

if(taxTrans.RecId)
{
USMFLedgerTransStatementStagingTmp.TaxCode = taxTrans.TaxCode;
}


USMFLedgerTransStatementStagingTmp.insert();

ttscommit;
}
}
}

USMFLedgerTransStatementStagingTmp1.setConnection(con);
trialBalanceService.populateDimensionInformationInStaging(USMFLedgerTransStatementStagingTmp,primaryDimensionFocus);
this.exportDataToLocalFolder(manualRun,primaryDimensionFocus

}

public TaxTrans findTaxCode(GeneralJournalAccountEntry _generalJournalAccountEntry = null)
{
GeneralJournalAccountEntry generalJournalAccountEntry;
TaxTransGeneralJournalAccountEntry taxTransGeneralAccEntry;
TaxTrans taxTrans;

select TaxTrans,GeneralJournalAccountEntry
from taxTransGeneralAccEntry
where taxTransGeneralAccEntry.GeneralJournalAccountEntry == _generalJournalAccountEntry.RecId
join TaxCode
from taxTrans
where taxTrans.recid == taxTransGeneralAccEntry.TaxTrans;

return taxTrans;
}

public void populateDimensionInformationInStaging(
USMFLedgerTransStatementStagingTmp _ledgerTransStatementTmp,
Name _dimensionFocusName )
{
UserConnection con= new UserConnection();
MainAccount mainAccount;
DimensionFocusNameTmp dimNameTmpMainValue;
MainAccountConsolidateAccount mainAccountConsolidateAccount;
DimensionAttributeValueCombination dimValueCombi;
DimensionAttributeLevelValueAllView dimAllview;
DimensionAttribute dimAttr;
DimensionAttributeValue dimAttrValue;
ExtCodeTable extCodeTable;
ExtCodeValueTable extCodeValue;
dimNameTmpMainValue.setConnection(con);
MainAccountCategory mainAccountCategory;

Query focusNameQuery = new Query();
SysQuery::findOrCreateDataSource(focusNameQuery, tableNum(USMFLedgerTransStatementStagingTmp));

QueryBuildDataSource davcDS = SysQuery::findOrCreateDataSource(focusNameQuery, tableNum(DimensionAttributeValueCombination), tableNum(USMFLedgerTransStatementStagingTmp));
davcDS.addLink(fieldNum(USMFLedgerTransStatementStagingTmp, LedgerDimension), fieldNum(DimensionAttributeValueCombination, RecId));

USMFLedgerTransStatementStagingTmp querySource;
querySource.setConnection(con);
select querySource;

QueryRun focusNameQueryRun = new QueryRun(focusNameQuery);
focusNameQueryRun.setRecord(querySource);
focusNameQuery = focusNameQueryRun.query();

DimensionFocusNameTmp::generateFocusNames(dimNameTmpMainValue, focusNameQuery, _dimensionFocusName, con);

update_recordset _ledgerTransStatementTmp setting
MainFocusValue = dimNameTmpMainValue.FocusValue,
MainFocusName = _dimensionFocusName
join dimNameTmpMainValue
where dimNameTmpMainValue.LedgerDimension == _ledgerTransStatementTmp.LedgerDimension;

DimensionFocusNameTmp::generateFocusDescriptions(dimNameTmpMainValue, _dimensionFocusName, con);

update_recordset _ledgerTransStatementTmp setting
MainFocusDescription = dimNameTmpMainValue.FocusValue
join dimNameTmpMainValue
where dimNameTmpMainValue.LedgerDimension == _ledgerTransStatementTmp.LedgerDimension;

update_recordset _ledgerTransStatementTmp setting
BusinessUnit = dimAllview.DisplayValue
join dimAllview
join dimAttr
join dimValueCombi
where dimAttr.RecId == dimAllview.DimensionAttribute
&& dimAttr.Name == "BusinessUnit"
&& dimValueCombi.DisplayValue == _ledgerTransStatementTmp.LedgerAccount
&& dimAllview.ValueCombinationRecId == dimValueCombi.RecId ;


update_recordset _ledgerTransStatementTmp setting
AccountCategory = mainAccountCategory.AccountCategory
join mainAccount
join mainAccountCategory
where mainAccount.MainAccountId == _ledgerTransStatementTmp.MainAccountId
&& mainAccountCategory.AccountCategoryRef == mainAccount.AccountCategoryRef;

}

Description255 getDimensionValue(RecId _recId,Name _DimensionName)
{
DimensionAttributeLevelValueAllView dimAllview;
DimensionAttribute dimAttr;
;
dimAllview.clear();
select firstOnly dimAllview
where dimAllview.ValueCombinationRecId == _recId
join dimAttr
where dimAttr.RecId == dimAllview.DimensionAttribute
&& dimAttr.Name == _DimensionName;
return dimAllview.DisplayValue;
}

public void exportDataToLocalFolder(boolean _manualRun = NoYes::No, Name _primaryDimensionFocus = '')
{
USMFLedgerTransStatementStagingTmp USMFTrialBalanceTemp;
DimensionAttributeValueCombination dimAttributeValueCombination;
FolderName folderName;
Filename fileName,fileNameTime;
date currentDate = DateTimeUtil::getToday(DateTimeUtil::getUserPreferredTimeZone());
DMFParameters dmfParameters;
CommaIo commaIo;
Query query;
QueryBuildDataSource queryBuildDataSource;
QueryRun queryRun;
GeneralJournalEntry generalJournalEntryUpd;
QueryBuildRange USMFTrialBalanceParameters trialBalanceParameters;
boolean stopProcess;
utcdatetime exportedDateTime;


try
{

dmfParameters = DMFParameters::find();
fileName = date2Str(currentDate-1,213,DateDay::Digits2,DateSeparator::Hyphen,DateMonth::Digits2,DateSeparator::Hyphen,DateYear::Digits4);
fileNameTime = strFmt("%1",DateTimeUtil::getTimeNow(DateTimeUtil::getUserPreferredTimeZone()));

folderName = dmfParameters.SharedFolderPath + @"\" + "TrialBalanceExport_"+fileName+"_"+fileNameTime+".csv";

commaIo = new CommaIo(folderName, "W");
commaIo.write("Company code","Posting date","Ledger dimensions","Document ID","Description","Credit","Debit","Currency","GST code","D365 Ledger dimensions");
while select USMFTrialBalanceTemp
where
USMFTrialBalanceTemp.MainFocusName == _primaryDimensionFocus

{
commaIo.write(USMFTrialBalanceTemp.DataAreaId,USMFTrialBalanceTemp.TransDate, ,USMFTrialBalanceTemp.Voucher,USMFTrialBalanceTemp.TransTxt,USMFTrialBalanceTemp.AmountCredit,USMFTrialBalanceTemp.AmountDebit,USMFTrialBalanceTemp.CurrencyCode,USMFTrialBalanceTemp.TaxCode,USMFTrialBalanceTemp.MainFocusValue);
}

if(!_manualRun)
{
ttsbegin;
exportedDateTime = DateTimeUtil::newDateTime(today(), timeNow(),DateTimeUtil::getUserPreferredTimeZone());
update_recordset generalJournalEntryUpd
setting USMFExported = NoYes::Yes,
USMFExportedDateTime = exportedDateTime
join USMFTrialBalanceTemp
where generalJournalEntryUpd.RecId == USMFTrialBalanceTemp.GeneralJournalEntry
&& USMFTrialBalanceTemp.MainFocusName == _primaryDimensionFocus
;


select forupdate trialBalanceParameters;
trialBalanceParameters.LastBatchRunDate = today()-1;
trialBalanceParameters.update();
ttscommit;
}
}
}
catch(Exception::CLRError)
{
System.Exception exception = CLRInterop::getLastException();
if (exception != null)
{
exception = exception.get_InnerException();
if (exception != null)
{
ttsabort;
Error(exception.ToString());
}
}
}


}

  • Martin Dráb Profile Picture
    Martin Dráb 239,029 Most Valuable Professional on at

    Could you explain your question, please?

    Your code doesn't contain indentation, therefore it's very difficult to read. Please use Insert > Insert Code (in the rich formatting view) to paste source code.

    And which version of AX is it about? I see you're using attributes and the SysOperation framework, therefore it must be either AX 2012 or D365FO, but I'm not sure which one.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Congratulations to our 2025 Community Spotlights

Thanks to all of our 2025 Community Spotlight stars!

Congratulations to the February Top 10 Community Leaders

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Giorgio Bonacorsi Profile Picture

Giorgio Bonacorsi 617

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 461 Super User 2026 Season 1

#3
Syed Haris Shah Profile Picture

Syed Haris Shah 298 Super User 2026 Season 1

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP
License usage reporting (in-app and PPAC) with Entra dynamic groups

Product updates

Dynamics 365 release plans