Export ssrs report as excel file and attach in mail using X++ code

Jeevanandham · April 30, 2024, 4:38pm

Hi All,

Process: Running the batch class which will generate the report and send the output as Excel file in mail attachment.

Issue: Unable to open the excel file from the mail attachment, getting the below error.

When the file format is PDF / CSV its working fine. Only facing issue when the file format is Excel facing the issue.

settings.fileFormat(SRSReportFileFormat::Excel);

In excel properties found few property changes

Looking for your valuable solutions, Thanks in advance.

public void sendMail()
    {
        Filename                fileName = 'Test.xlsx';
        Str                     messageText;
        SrsReportRunController  controller = new SrsReportRunController();
        SrsReportDataContract   contract = new SrsReportDataContract();
        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();

        System.Byte[]           binData1;
        System.IO.Stream        stream1;
        
        controller.parmReportName(ssrsReportStr(DashboardReport, Report));
        //controller.parmExecutionMode(SysOperationExecutionMode::Synchronous);
        controller.parmShowDialog(false);
        controller.parmLoadFromSysLastValue(false);
        controller.parmReportContract().parmRdpContract(contract);
        
        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());

        container               binData;
        Binary                  binaryData;
        System.IO.MemoryStream  mstream = new System.IO.MemoryStream(reportBytes);
        
        binaryData = Binary::constructFromMemoryStream(mstream);
        
        if (binaryData)
        {
            binData = binaryData.getContainer();
        }

        for (int i = 0; i < conLen(binData); i++)
        {
            binData1 = conPeek(binData,i+1);
            stream1 = new System.IO.MemoryStream(binData1);
        }

        //File::SendFileToUser(stream1,filename);

        var mail = SysMailerFactory::getNonInteractiveMailer();

        var messageBuilder = new SysMailerMessageBuilder();
        
        messageText = "Dear Sir/Madam,\nTesting mail with attachment.\nThanks.";

        messageBuilder.reset()
        .setFrom('From mail address')
        .addTo("To mail address")
        .setSubject('Test mail')
        .setBody(messageText);

        if (stream1 != null)
        {
            messageBuilder.addAttachment(stream1,filename);
        }

        mail.sendNonInteractive(messageBuilder.getMessage());
    }

MartinDrab · May 1, 2024, 5:01am

Before trying to find a bug in your code, can’t we simply throw away most of it and let the reporting framework to do the job? Like this:

SrsReportRunController controller = new SrsReportRunController();
 
controller.parmReportName(ssrsReportStr(DashboardReport, Report));
controller.parmShowDialog(false);
 
SRSPrintDestinationSettings settings = controller.parmReportContract().parmPrintSettings();
settings.printMediumType(SRSPrintMediumType::Email);
settings.fileFormat(SRSReportFileFormat::Excel);
settings.fileName('Test.xlsx');

SrsReportEMailDataContract emailContract = new SrsReportEMailDataContract();
emailContract.parmTo('To mail address');
emailContract.parmSubject('Test mail');

str messageText = "Dear Sir/Madam,\nTesting mail with attachment.\nThanks.";
emailContract.parmBody(messageText);
 
settings.parmEMailContract(emailContract);
 
controller.startOperation();

Jeevanandham · May 1, 2024, 2:46pm

Thanks, Martin will try out this and update back.

I’m trying to send multiple (different) report output attachment in single mail as a batch job. If this is not possible then I will send each attachment in separate mail.
When I’m trying to send the report output as PDF attachment in mail, its working fine on my code getting issue in Excel only.

Thanks a lot for your valuable time.

Jeevanandham · May 2, 2024, 7:24am

Hi Martin,

when we using the code in batch job class, we are getting the file attachment as CSV instead Excel.

While execute the report manually from menuitem by changing the destination as Excel, at that time the attachment coming as Excel.

It seems weird, on my first code also faced the same issue for Excel.

MartinDrab · May 2, 2024, 8:00am

What happens if you execute the code (instead of doing it manually) but not as a batch?

Jeevanandham · May 2, 2024, 10:39am

If I execute the class directly by “Set as startup object”, then the parameter screen for report is opening

Destination is set to Email but mail id is empty, and file format is HTML 4.0

controller.parmShowDialog(false); → This line is not working
emailContract.parmTo(‘To mail address’); → This line is not working,
Subject and Body reflecting properly

After updated the mail id and file format as Excel, I have executed the class and in mail we got CSV format only

MartinDrab · May 2, 2024, 10:57am

Isn’t the file name (Test.csv) defined by yourself? Note that the extension doesn’t necessarily have to match the file content. It’s just a file name, nothing else.

Please show us your current code. And ideally use a standard report (as I showed in Printing reports from code in D365FO, for example), so others can run and test your code.

The problem with parmShowDialog() may be caused by usage data. Clear it and try again.

Jeevanandham · May 2, 2024, 11:33am

After cleared the usage data the report parameter screen doesn’t appeared, now I got the mail with Test.csv file only.

Issue is the report having group by properties and some colours, which will not come when the file is in CSV format, the whole design is getting collapsed.

Current code

SrsReportRunController controller = new SrsReportRunController();
 
controller.parmReportName(ssrsReportStr(DashboardReport, Report));
controller.parmShowDialog(false);
 
SRSPrintDestinationSettings settings = controller.parmReportContract().parmPrintSettings();
settings.printMediumType(SRSPrintMediumType::Email);
settings.fileFormat(SRSReportFileFormat::Excel);
settings.fileName('Test');

SrsReportEMailDataContract emailContract = new SrsReportEMailDataContract();
emailContract.parmTo('To mail Id');
emailContract.parmSubject('Test mail');

str messageText = "Dear Sir/Madam,\nTesting mail with attachment.\nThanks.";
emailContract.parmBody(messageText);
 
settings.parmEMailContract(emailContract);
 
controller.startOperation();

https://dev.goshoom.net/2018/10/printing-reports-from-code-in-d365fo/
I have tried this solution, after the report executed the report excel file is open in new window as expected. When I tried to send in mail I’m receiving the file is corrupted or file format/extension is not valid.

As finally I’m scheduling batch job from report itself by setting recurrence, without no code.

Krupa · May 6, 2024, 8:58am

I saw in your code you have file name set to “Test”. Have you tried changing the file name to “Test.xlsx”?

Jeevanandham · May 6, 2024, 1:40pm

Hi Krupa,

if I put the file name in code as “Test.xlsx”, then in my mail I got as “Test.xlsx.xlsx” at the same time getting the extension related issue.

CSV,PDF does not have any issue, only on EXCEL facing the issue.

Now using the base report mail option with batch processing.