How to send an email with an SSRS report as an attachment in Microsoft Dynamics 365 for Finance and Operations

How to send an email with an SSRS report as an attachment in Microsoft Dynamics 365 for Finance and Operations

The following div provides an X++ class that demonstrates how to send an email with an SSRS report as an attachment in Microsoft Dynamics 365 for Finance and Operations. This class, named

EmailCustAccountStmnt

, generates a customer account statement report, converts it to a PDF, and then emails it to the customer.

x++
public class EmailCustAccountStmnt
{
    public void run(CustTable _custTable)
    {
        SysOperationQueryDataContractInfo sysOperationQueryDataContractInfo;
        SrsReportRunController reportRunController;
        CustTransListContract custTransListContract;
        SRSReportExecutionInfo reportExecutionInfo;
        SRSPrintDestinationSettings printDestinationSettings;
        SRSReportRunService srsReportRunService;
        SRSProxy srsProxy;
        QueryBuildRange qbrCustAccount;
        QueryBuildDataSource queryBuildDataSource;
        Object dataContractInfoObject;
        Map reportParametersMap;
        Map mapCustAccount;
        MapEnumerator mapEnumerator;
        Array arrayFiles;
        System.Byte[] reportBytes;
        Filename fileName;
        Args args;
        System.IO.MemoryStream memoryStream;
        System.IO.MemoryStream fileStream;
        CustParameters custParameters;
        Email toEmail;

        Map templateTokens;
        str emailSenderName;
        str emailSenderAddr;
        str emailSubject;
        str emailBody;

        Microsoft.Dynamics.AX.Framework.Reporting.Shared.ReportingService.ParameterValue[] parameterValueArray;

        #define.Subject("Subject")
        #define.CustAccount("CustAccount")
        #define.EmailDate("Date");

        custParameters = CustParameters::find();

        reportRunController = new SrsReportRunController();
        custTransListContract = new CustTransListContract();
        reportExecutionInfo = new SRSReportExecutionInfo();
        srsReportRunService = new SrsReportRunService();
        reportBytes = new System.Byte[0]();
        args = new Args();
        templateTokens = new Map(Types::String, Types::String);
        var messageBuilder = new SysMailerMessageBuilder();

        custTransListContract.parmNewPage(NoYes::Yes);

        fileName = strFmt("CustomerAccountStatement_%1.pdf", _custTable.AccountNum);

        reportRunController.parmArgs(args);
        reportRunController.parmReportName(ssrsReportStr(CustTransList, Report));
        reportRunController.parmShowDialog(false);
        reportRunController.parmLoadFromSysLastValue(false);
        reportRunController.parmReportContract().parmRdpContract(custTransListContract);

        // Modify query
        mapCustAccount = reportRunController.getDataContractInfoObjects();
        mapEnumerator = mapCustAccount.getEnumerator();

        while (mapEnumerator.moveNext())
        {
            dataContractInfoObject = mapEnumerator.currentValue();

            if (dataContractInfoObject is SysOperationQueryDataContractInfo)
            {
                sysOperationQueryDataContractInfo = dataContractInfoObject;

                queryBuildDataSource = SysQuery::findOrCreateDataSource(sysOperationQueryDataContractInfo.parmQuery(), tableNum(CustTable));
                qbrCustAccount = SysQuery::findOrCreateRange(queryBuildDataSource, fieldNum(CustTable, AccountNum));
                qbrCustAccount.value(_custTable.AccountNum);
            }
        }

        printDestinationSettings = reportRunController.parmReportContract().parmPrintSettings();
        printDestinationSettings.printMediumType(SRSPrintMediumType::File);
        printDestinationSettings.fileName(fileName);
        printDestinationSettings.fileFormat(SRSReportFileFormat::PDF);

        reportRunController.parmReportContract().parmReportServerConfig(SRSConfiguration::getDefaultServerConfiguration());
        reportRunController.parmReportContract().parmReportExecutionInfo(reportExecutionInfo);

        srsReportRunService.getReportDataContract(reportRunController.parmReportContract().parmReportName());
        srsReportRunService.preRunReport(reportRunController.parmReportContract());

        reportParametersMap = srsReportRunService.createParamMapFromContract(reportRunController.parmReportContract());
        parameterValueArray = SrsReportRunUtil::getParameterValueArray(reportParametersMap);

        srsProxy = SRSProxy::constructWithConfiguration(reportRunController.parmReportContract().parmReportServerConfig());
        reportBytes = srsProxy.renderReportToByteArray(reportRunController.parmReportContract().parmReportPath(), parameterValueArray, printDestinationSettings.fileFormat(), printDestinationSettings.deviceInfo());

        memoryStream = new System.IO.MemoryStream(reportBytes);
        memoryStream.Position = 0;

        fileStream = memoryStream;
        toEmail = this.getCustEmail(_custTable.AccountNum);

        if (custParameters.EmailId && toEmail)
        {
            templateTokens.insert(#CustAccount, _custTable.Name);
            templateTokens.insert(#EmailDate, date2StrXpp(systemDateGet()));

            [emailSubject, emailBody, emailSenderAddr, emailSenderName] = EmailCustAccountStmnt::getEmailTemplate(custParameters.EmailId, _custTable.languageId());

            messageBuilder.addTo(toEmail)
                          .setSubject(strFmt("Customer account statement for %1", _custTable.AccountNum))
                          .setBody(SysEmailMessage::stringExpand(emailBody, SysEmailTable::htmlEndivParameters(templateTokens)))
                          .addCC("");

            messageBuilder.setFrom(emailSenderAddr, emailSenderName);
            messageBuilder.addAttachment(fileStream, fileName);

            SysMailerFactory::sendNonInteractive(messageBuilder.getMessage());

            info(strFmt("Email sent successfully to the customer account %1", _custTable.AccountNum));
        }
        else
        {
            info(strFmt("There is no email id mapping for this customer %1 or check the Email template setup.", _custTable.AccountNum));
        }
    }

    protected static container getEmailTemplate(SysEmailId _emailId, LanguageId _languageId)
    {
        var messageTable = SysEmailMessageTable::find(_emailId, _languageId);
        var emailTable = SysEmailTable::find(_emailId);

        if (!messageTable && emailTable)
        {
            // Try to find the email message using the default language from the email parameters
            messageTable = SysEmailMessageTable::find(_emailId, emailTable.DefaultLanguage);
        }

        if (messageTable)
        {
            return [messageTable.Subject, messageTable.Mail, emailTable.SenderAddr, emailTable.SenderName];
        }
        else
        {
            warning("@SYS135886"); // Let the user know we didn't find a template
            return ['', '', emailTable.SenderAddr, emailTable.SenderName];
        }
    }

    public Email getCustEmail(CustAccount _custAccount)
    {
        CustTable custTable;
        DirPartyLocation dirPartyLocation;
        LogisticsLocation logisticsLocation;
        LogisticsElectronicAddress logisticsElectronicAddress;

        custTable = CustTable::find(_custAccount);

        select firstonly Location, Party from dirPartyLocation
            where dirPartyLocation.Party == custTable.Party
                join RecId from logisticsLocation
                    where logisticsLocation.RecId == dirPartyLocation.Location
                join Locator from logisticsElectronicAddress
                    where logisticsElectronicAddress.Location == logisticsLocation.RecId
                        && logisticsElectronicAddress.Type == LogisticsElectronicAddressMethodType::Email
                        && logisticsElectronicAddress.IsPrimary == NoYes::Yes;

        return logisticsElectronicAddress.Locator;
    }
}

Explanation

1. Report Generation:

   • The
     run
     method generates a customer account statement using the
     CustTransList
     report.
   • It sets up the report parameters and executes the report to obtain the report data as a byte array.

2. Email Setup:

   • The email setup includes the sender's details, subject, and body.
   • The email content can be customized using tokens for the customer account and date.

3. Attachment:

   • The report is saved as a PDF file in memory, which is then attached to the email.

4. Email Sending:

   • The email is sent to the customer's email address using the
     SysMailerFactory::sendNonInteractive
     method.

Functions

• getEmailTemplate: Fetches the email template based on the provided email ID and language.
• getCustEmail: Retrieves the primary email address for the given customer account.

This example demonstrates how to generate and send an SSRS report as an email attachment in D365F&O using X++.