Send ssrs report to email with excel attachment in D365FO

Purpose:

The purpose of this document is to demonstrate how we can send ssrs report to mail through attachment Excel X++ based on a business logic.

Product:

Dynamics 365 for Finance and Operations, Platform Update 9.

Development approach:

Customization through code, to send report in excel format to email

Development:

Create class, and get control setting and SMTP config details, to users get email of report as attachment .

Please find the method definition as follows.

class testTransReportService

{

    /// <summary>

    /// Send email with excel attachment

    /// </summary>

    [SysEntryPointAttribute(false)]

    public void processRecords()

    {

        date integationDate;

        SysEmailParameters sysEmailParameters;

        integationDate = DateTimeUtil::getToday(DateTimeUtil::getUserPreferredTimeZone());

        Filename fileName = "test transaction report"+ any2Str(integationDate)+ ".xlsx";

             testTransController controller = new testTransController();

               SRSPrintDestinationSettings settings;

        Array arrayFiles;

        System.Byte[] reportBytes = new System.Byte[0]();

        SRSProxy srsProxy;

        SRSReportRunService srsReportRunService = new SrsReportRunService();

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

        Map reportParametersMap;

        SRSReportExecutionInfo executionInfo = new SRSReportExecutionInfo();

       Args args = new Args();

       controller.parmArgs(args);

        controller.parmReportName(ssrsReportStr(testTransReport, voucher));

        controller.parmShowDialog(false);

        controller.parmLoadFromSysLastValue(false);

        settings = controller.parmReportContract().parmPrintSettings();

        settings.printMediumType(SRSPrintMediumType::File);

        settings.fileName(fileName);

        settings.fileFormat(SRSReportFileFormat::Excel);

        controller.parmReportContract().parmReportServerConfig(SRSConfiguration::getDefaultServerConfiguration());

        controller.parmReportContract().parmReportExecutionInfo(executionInfo);

        srsReportRunService.getReportDataContract(controller.parmreportcontract().parmReportName());

        srsReportRunService.preRunReport(controller.parmreportcontract());

        reportParametersMap = srsReportRunService.createParamMapFromContract(controller.parmReportContract());

        parameterValueArray = SrsReportRunUtil::getParameterValueArray(reportParametersMap);

        srsProxy = SRSProxy::constructWithConfiguration(controller.parmReportContract().parmReportServerConfig());

        reportBytes = srsproxy.renderReportToByteArray(controller.parmreportcontract().parmreportpath(),                                              parameterValueArray,                                              settings.fileFormat(),                                              settings.deviceinfo());

        if (reportBytes)

        {

            System.IO.MemoryStream stream = new System.IO.MemoryStream(reportBytes);

            File::SendFileToUser(stream, settings.parmFileName());

            stream.Position = 0;

            str fileContentType = System.Web.MimeMapping::GetMimeMapping(fileName);

            var messageBuilder = new SysMailerMessageBuilder();

            //select firstonly integrationMailSetup

            //    where integrationMailSetup.IntegrationName == 'Purchase Integration';

            select firstonly SMTPUserName from sysEmailParameters;        

            str sendFromEmail = sysEmailParameters.SMTPUserName;

            str sendToEmail = "nivas@test.com";          

            //str sendCCEmail = integrationMailSetup.CCAddress;

            str body = "Hi Team,<o:p></o:p></p><p></p><p>Please find the attached report. Kindly review and take necessary actions. <o:p></o:p></p><p></p><p>Regards,<o:p></o:p></p><p></p><p><span style='font-size:12.0pt'>AX Team<o:p></o:p></span></p>";

            messageBuilder.addTo(sendToEmail)

                    .setSubject("transaction report")

                    .setBody(Body);

                   // .addCC(sendCCEmail);      

            if (sendFromEmail)

            {

                messageBuilder.setFrom(sendFromEmail);

            }      

            if (stream != null)

            {

                messageBuilder.addAttachment(

                stream,

               fileName);

            }

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

        }

    }

}

after this service class.... create menuitem and add this class