Run Financial/MR Report using code

Financial reporting allows financial and business professionals to create, maintain, deploy, and view financial statements. These reports are very crucial to them especially during month end or year end close process.

However sometimes some financial reports, for example custom trail balance built as per finance need does not match with  result produced by out of the box trial balance report present in the Dynamics 365 for finance and operations. The reason for it might be nay issue while syncing records to data mart.

Instead of users from finance department come to know  about these discrepancies at the very last moment we decided to be proactive and do the reconciliation between output produced by custom trial balance report and out of the box trial balance report.

Below piece of code you can help you if you want to run financial report using X++ code . GenerateAndExportReport  method from class FinancialReportingIReportExport gives stream as output which can be converted to XML to get the values of columns present in the report.

public static void main(Args _args)
    {  
        FinancialReportVersion          FinancialReportVersion;
        FinancialReports                financialReports;
        FiscalCalendarPeriod            FiscalCalendarPeriod;
        Str                             AmountValue;
        boolean                         Isnegative;
        Amount                          Amount;
        str                             ReportName;

        ReportName="Trial Balance Val report";
      
        select firstonly FinancialReportVersion 
            order by recid desc
               join financialReports where financialReports.DesignId==FinancialReportVersion.DesignId
                    && financialReports.ReportName == ReportName;
            
        FinancialReportingIReportExport reportExporter      = new FinancialReportingReportExport();
       
        System.Text.Encoding encodingGB18030                = System.Text.Encoding::GetEncoding("utf-8");

        if (!financialReports.DesignId)
            throw error(strfmt("Design not found for Report %1",ReportName));
          
        System.IO.Stream stream = reportExporter.GenerateAndExportReport(
                                        financialReports.DesignId,
                                        curExt(),
                                        FiscalCalendarPeriod.enddate,
                                        FinancialReportingReportExportFormat::Xml,
                                        encodingGB18030);
        if (stream == null)
        {
            throw error(strfmt("Stream is empty for Report %1 period %2",ReportName,FiscalCalendarPeriod.enddate));
        }
        //Convert stream to  XML
        XmlDocument xmlDocument = XMLDocument::newFromStream(stream);

        XmlNode reportIdNode =XmlDocument.SelectSingleNode('//Reports/Report');
            XmlNode reportIdAttr = reportIdNode.attributes().getNamedItem('Id');
               
        XmlNode reportNode = xmlDocument.SelectSingleNode('//Report/Unit[@Index=0]');

            if (!reportNode)
                 throw error("Report Id node not found");

        XmlNode reportNameAttr = reportNode.attributes().getNamedItem('Name');

        if (!reportNameAttr)
                    throw error("Report Name node not found");
        XmlNodeList rowList = reportNode.selectNodes('./Row');

        //Read  all rows and needed columns 
        ttsbegin;
        if (!rowList)
                      throw error(strfmt("Rows are  empty for Report %1 period %2",ReportName,FiscalCalendarPeriod.enddate));
            
        for (int i = 0; i < rowList.length(); i ++)
        {
           
            XmlNode row = rowList.item(i);
            str rowDescription =    row.attributes().getNamedItem('Description').value();
                   
            // Skip the placeholder row in MR report.
            if (rowDescription == '')
                            continue;
            
            XmlNode AccountCodeNode = row.selectSingleNode("./Column[@Type='AccountCode']");
            
            XmlNodeList XmlNodeList= row.selectNodes("./Column[@Type='Amount']");
            XmlNodeListIterator iterator= new XmlNodeListIterator(XmlNodeList);
            int counter=0;
                    
            While (iterator.moreValues())
            {
                //Parse if you have more values
            }
        }
                         
            
    }