Run, Save and Email Purchase order report through X++ code

Hi Friends,

Sometime customer we face this requirement to send SSRS reports directly on emails of customer/vendor like Purchase order or Sales order report. In this post, I will demonstrate how to do these kinds of stuff in very easy steps, 





To understand each and every logic, we will perform this whole process in three steps. Let’s take an example of Purchase order report. 

1. Run a report and Save as PDF at local drive
2. Email this report
3. Delete from local drive after email.

It would be suggested to have all code into a single class than writing the code here and there on forms or any other standard classes. 

1.      Run a report and Save as PDF at local drive: 
Create a new class and add a new method for run a report and save it at your local/shared path. 

   public str runAndSaveSSRSReport(PurchTable _purchTable)
{
    SrsReportRunController          ssrsController = new SrsReportRunController();
    PurchPurchaseOrderContract      Contract = new PurchPurchaseOrderContract();
    SRSPrintDestinationSettings     printerSettings;
    VendPurchOrderJour              VendPurchOrderJour;
    str                             ReportPath;

    select firstOnly VendPurchOrderJour
    order by VendPurchOrderJour.createdDateTime DESC
    where VendPurchOrderJour.PurchId == _PurchTable.PurchId;

    ReportPath = "C:\\" + _PurchTable.PurchId +".pdf";
    ssrsController.parmReportName(ssrsReportStr(PurchPurchaseOrder, Report));
    ssrsController.parmExecutionMode(SysOperationExecutionMode::Synchronous);
    ssrsController.parmShowDialog(false);
    Contract.parmRecordId(VendPurchOrderJour.RecId);
    ssrsController.parmReportContract().parmRdpContract(Contract);

    //link the printer settings to the controller
    printerSettings = ssrsController.parmReportContract().parmPrintSettings();
    //print to pdf and always overwrite if the file exists
    printerSettings.printMediumType(SRSPrintMediumType::File);
    printerSettings.fileFormat(SRSReportFileFormat::PDF);
    printerSettings.overwriteFile(true);
    printerSettings.fileName(@ReportPath);

    //run & save the report
    ssrsController.runReport();
    return ReportPath;// return the file location where pdf saved. we use this path to email and delete after email.
}

2.      Email this report: 
Now when we have report saved on local drive we can pick this file to attach in email. Add a new method in your class as below,





public void POConfirmationEmail(PurchTable   _PurchTable)
{
    PurchTable                  PurchTable;
    Map                         parameterMap = new Map(Types::String, Types::String);
    Email                       requester;
    SysEmailId                  ApprovalEmailTemplate;
    SysEmailId                  ReopenEmailTemplate;
    int                         itemCount = 1;
    str                         ItemId, ItemQty, ItemDeliveryDate, ItemPrice;
    str                         companyDetails;
    FilenameOpen                attachmentFilename;

    companyDetails = curext();
    ParameterMap.insert('CompanyDetails',companyDetails);
    PurchTable = _PurchTable;
    attachmentFilename = this.runAndSaveSSRSReport(PurchTable);

    ParameterMap.insert('VendorName', VendTable::find(PurchTable.OrderAccount).name());
    ParameterMap.insert('PurchaseID', PurchTable.PurchId);
    requester   = LogisticsElectronicAddress::findRecId(DirPartyTable::findRec(VendTable::find(PurchTable.OrderAccount).Party).PrimaryContactEmail).Locator;

    if(!requester)
    {
        throw error("No Email address is available");
    }
    else
    {
        SysEmailTable::sendMail("PoEmail", companyinfo::languageId(), requester, parameterMap, attachmentFilename); //PoEmail is emial template with two parameter (VendName and PurchId, you can make changes in template as per user requirement. 
        this.deleteReportFile(attachmentFilename);// To delete this file after email.
    }
}

3.      Delete report file from your local drive:
Now after email we have to delete this from memory, lets add one more method in your class to delete this file,

public void deleteReportFile(str _ReportPath)
{
    str         ReportPath = _ReportPath;

    if(!ReportPath)
        warning("No file in local to remove");
    else
        WinAPI::deleteFile(@ReportPath);
}

Now all you need is to call this class during confirmation of PO. Put your Feedback/question/Queries in comments. 





Enjoy......

-Harry


Comments

  1. I want to do the same in AX2009 i.e. For Customer Dunning Letter.
    Actually the requirement is when user run the report,the selected customer should get the email with an attachment of the same report same through Batch also.
    Will you help me with the link that might help me to achieve this.
    Thanks in Advance!

    ReplyDelete
  2. Hi,

    I have the same requirement in AX2009,It should send email to the selected customer email id when the Customer Statement report is run or schedule through batch.
    Could you please help me .
    As In AX2009 the report has been extended to runbase not ssrs controller.
    Please let me know if you need more details ,also correct me as I am a beginner in AX.

    ReplyDelete
    Replies
    1. Hi Binay,

      Thanks for writing. I would suggest to have some specific step where you are stucked or need help. I will help me to understand better.

      Delete
    2. Thanks Deepeak for your response.
      Customer Account statement report should send email to all the selected customer in the report ,taking email id from its customer card.

      I have tried using print job setting ,but it is generating 1 PDF for all the customer account and sending it to the last customer account.
      What I want is to break the PDF and send separately to the customers.

      My Current logic -
      In fetch method-

      element.printJobSettings().Format(printformat::PDF);
      element.setTarget(PrintMedium::Mail);
      element.printJobSettings().preferredTarget(PrintMedium::Mail);
      element.printJobSettings().preferredFileFormat(Printformat::PDF);
      element.printJobSettings().preferredMailFormat(printformat::PDF);
      element.printJobSettings().mailTo(custTable.Email);

      Above code is sending email to the last customer only and combined pdf.
      Could you please suggest .

      Delete
    3. Can you help me to how I can break the PDF pages for the customer account and send email with Attached PDF separately to each selected customer account.
      Please suggest.

      Delete
    4. Hi Binay,

      This whole process must execute setpe by step for each and every customer. In your case your are executing all setps once for all customers thats why you are getting single report.

      Try to write your logic for call all these method for each customer, not once for all.

      Hope it will help.

      Delete

Post a Comment

Thanks

Popular posts from this blog

Connecting to Databases through X++ PART -I

X++ code for document attachment

Multiple Tables In Query