Update: Send SSRS Report as Email (with meaningful PDF File name)

A while ago I’ve described how to add a “Send E-Mail Button” to the Report Viewer in Dynamics AX 2012: https://erpcoder.wordpress.com/2013/01/07/send-ssrs-report-as-email/. In this initial script all reports were generated as Report.PDF file. However, a visitor recently asked how to generate a PDF file, named according to the documents ID, e.g. To create an Invoice PDF named as the Invoice Number in AX.

This can be achieved by reading the SSRS Report Parameters and create a meaningful document name based on this information.  For example, the SalesInvoice report holds a parameter RecordId, which is the RecId for the corresponding CustInvoiceJour record in Dynamics Ax.

Import the ReportViewer.WinForms DLL

The DLL Microsoft.ReportViewer.WinForms is required. The DLL can be found at C:\Program Files (x86)\Microsoft Visual Studio 12.0\ReportViewer . Add the DLL as Reference in the AOT.

Parameter Table

Create a table that contains 3 fields and stores the combination of Report Name, Report Design and Report Parameter. Create an index for the combination of Report Name and Report Design.

Report Mailing Parameter
A sample configuration for SalesInvoice looks like this: The Report Name is SalesInvoice, the Report Design is Report and the Parameter used to generate the document name is RecordId.
Report Mailing Parameter for SalesInvoice

find() and exist()

Add a find and exist method to the table.

public static ERPReportMailParameter find(SrsReportName _name,
SrsReportDesignName _design,
boolean _forupdate = false)
{
ERPReportMailParameter parm;
parm.selectForUpdate(_forupdate);
select firstOnly parm
where parm.ReportName == _name &&
parm.ReportDesignName == _design;
return parm;
}

 

public static boolean exist(SrsReportName _name,
SrsReportDesignName _design)
{
return ERPReportMailParameter::find(_name,_design).RecId > 0;
}

Business Logic

Create the following documentName* methods to generate the name of the PDF file. These methods are called by the SrsReportViewer Form to get a meaningful name for the PDF file in the mail.

public static str documentName(SRSReportName _name,
SRSReportDesignName _design,
Microsoft.Reporting.WinForms.LocalReport _report)
{
Microsoft.Reporting.WinForms.ReportParameterInfoCollection parms;
Microsoft.Reporting.WinForms.ReportParameterInfo reportParm;
System.Collections.IList list;
System.Object value;
str documentName = „Report“;    // default value
str parameterName = „“;
str parameterValue = „“;

   // Is a parameter configured
if(ERPReportMailParameter::exist(_name,_design))
{
parameterName = ERPReportMailParameter::find(_name,_design)
.ReportParameter;

      parms = _report.GetParameters();
reportParm = parms.get_Item(parameterName);
list = reportParm.get_Values();
value = list.get_Item(0);
parameterValue = value.ToString();

      // Call document specific logic to determine the document name
switch(_name)
{
case „SalesInvoice“:
documentName = ERPReportMailParameter::documentNameSalesInvoice
(parameterValue);
break;

         default:
break;
}
}
return documentName;
}

public static str documentNameSalesInvoice(str _parameterValue)
{
RecId recId = str2int64(_parameterValue);
return CustInvoiceJour::findRecId(recId).InvoiceId;
}

SrsReportViewer Form

At the SrsReportViewer Form, modify the init() method so the report parameters are always visible.

public void init()
{
boolean showReportViewerParameters;
    super();
    if(this.controller())
{
        // showReportViewerParameters = this.controller()
.parmShowReportViewerParameters();
        this.setFormCaption();
        // set form help context.
this.setFormHelpContext();
}
else
{
//showReportViewerParameters = false;
    }
    showReportViewerParameters = true;

Add a send button to the button group
image

Modify the clicked() method

void clicked()
{
Microsoft.Dynamics.AX.Frameworks.Controls.ReportViewer.AxReportViewer
axviewer;
Microsoft.Reporting.WinForms.ReportViewer nviewer;
Microsoft.Reporting.WinForms.LocalReport report;
ERPReportMailParameter mailParameter;
str parameterName;
str reportName;
str reportPath;
int dot;
int slash;
str documentName = „Report“;
SRSReportName srsReportName;
SRSReportDesignName srsReportDesignName;
System.Exception ex;
System.Array bytear;
System.Object no;
System.IO.File file;
str tmpPath;
System.IO.FileStream fs;
SmmOutlookEmail smmOutlookEmail = new SmmOutlookEmail();

   super();

   axviewer = AxReportViewer.control();
nviewer = axviewer.get_ReportViewerControl();

   try
{
report = nviewer.get_ServerReport();
reportPath = report.get_ReportPath();

 

      // cut the report server path
slash = strFind(reportPath,“/“,strLen(reportPath),
strLen(reportPath)*-1);
reportName = subStr(reportPath,slash+1,strLen(reportPath)-slash);

      // split name and design
dot = strFind(reportName,“.“,strLen(reportName),
strLen(reportName)*-1);
srsReportName = subStr(reportName,1,dot-1);
srsReportDesignName = subStr(reportName,dot+1,
strLen(reportName)-dot);
documentName = ERPReportMailParameter::documentName
(srsReportName,srsReportDesignName,report);

      //render as PDF
bytear = report.Render(„PDF“);
no = bytear;

      //path to temp. files
tmpPath = System.IO.Path::GetTempPath();
tmpPath = tmpPath + documentName + „.pdf“;

      //save to file
fs = System.IO.File::Create(tmpPath);
fs.Write(no,0,bytear.get_Length());
fs.Flush();
fs.Close(); 

      //open outlook email
if (smmOutlookEmail.createMailItem())
{
smmOutlookEmail.isHTML(true);
smmOutlookEmail.addFileAsAttachment(tmpPath);
smmOutlookEmail.sendEMail(smmSaveCopyofEmail::No);
}
}
catch(Exception::CLRError)
{
ex = CLRInterop::getLastException();
info(ex.ToString());
}
}

Test it

Compile your code in X++ and IL and test it.

Send SSRS Report per Email

Advertisements

Über erpcoder
Software Engineer and Dynamics AX enthusiast working in Research & Development for InsideAx

One Response to Update: Send SSRS Report as Email (with meaningful PDF File name)

  1. Tarro says:

    Hi Thanks for the great post. Unfortunately when the PDF is created it does not retrieve the invoice id, any ideas where I might be going wrong?

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s

%d Bloggern gefällt das: