Dynamically Creating Crystal Report for distribution using Outlook

In one of our assignments, we needed to develop a solution to generate reports for a given set of dynamic criteria, and subsequently distribute the reports out via e-mail to the appropriate user group.  To leverage the client’s existing skillset and infrastructure, we decided to proceed forward with Crystal Report for the reporting piece, and Exchange Server/MS Outlook for the e-mail delivery system.

The report generation and distribution process got triggered after the CSR’s have completed a set of data entry steps using a Windows form-based application developed in .NET, which was automatically deployed/pushed to the CSR’s machines.  One additional constraint to our approach was the fact that the CSR’s machines don’t have Crystal Report DLL installed in it, and that the client management has decided not to include Crystal Report DLL as part of the application deployment.  To get around this constraint, we decided to develop a web service that would serve the form’s request to generate and distribute the resulting report.  This way, the form would only need to pass the reports parameter to the web service, which in turn would dynamically create and distribute the Crystal Report for the given set of parameter.

When we first attempted to develop the web service, we had thought of initially creating a temporary report file with a unique name by appending unique timestamp to the file’s name.  The original plan was to associate the file to the Attachment class, and subsequently use MailMessage and SmtpClient classes to send out the e-mail.  After the e-mail got sent out, the web service would then go back and delete the temporary report file.

We expected that the approach outlined above would work out just fine.  In practice, however, it didn’t work very well as the web service was actually unable to clean-up (i.e., delete) the temporary report file.  When the web service issued a file delete command, it received an error message indicating that the file was currently being used by another process.   To get around this issue, we modified our web service to perform the following, instead:

  1. Create report document and populate it with the data contained in DataSet
  2. Create MemoryStream object to capture the report document
  3. Attach the MemoryStream object to a MailMessage object by utilizing .NET Attachment class
  4. Send the MailMessage object utilizing SmtpClient class

The following is sample code representation for the solution that we implemented in our web service (note: assume that m_DsCsfReport is a DataSet that has its content populated elsewhere):

string m_RptName = "";

// Load the Report Document
ReportDocument m_csfReport = new ReportDocument();
MemoryStream m_RptStream = new MemoryStream();

// E-mail
MailMessage m_EmailMsg = new MailMessage();
SmtpClient m_SmtpClient = new SmtpClient("EMAILXX");

try
{
m_csfReport.Load(@"C:InetpubwwwrootWebSecurityWScsfReportFromDS.rpt");

//Set dataset for the report
m_csfReport.SetDataSource(m_DsCsfReport);

m_RptStream = (MemoryStream)m_csfReport.ExportToStream(ExportFormatType.WordForWindows);

m_EmailMsg.Subject = "E-mail w/ attachment";
m_EmailMsg.Body = "";
m_EmailMsg.To.Add("h.lukito@practicalsolutions.com");
m_EmailMsg.From = new MailAddress("h.lukito@practicalsolutions.com ");

m_RptName = "TestReport_" + inUserId.Trim().ToUpper() + "_" +
DateTime.Now.Hour.ToString().Trim() +
DateTime.Now.Minute.ToString().Trim() +
DateTime.Now.Second.ToString().Trim() +
".doc";

m_EmailMsg.Attachments.Add(new Attachment(m_RptStream, m_RptName, "application/msword"));
m_SmtpClient.Send(m_EmailMsg);