Integrating ASP.NET and SSRS: Calling Reports Programmatically and Sending results in HTML

I had a requirement to call a report on our SSRS server and send the results as e-mail to our clients outside our network, and additionally to have e-mail look good on a Smart Phone. After some research, I found that by calling the web service execution engine on my server, that I could do just that. ReportExecution2005.asmx

By generating a proxy class to this service, (Code in the attached Project) I was able to get the results I was looking for in no time! The attached project is a working example of this in action.

You will need to change the web config and give it the correct credentials and the path to your servers:-mail included

<add key="SSRSSite2005" value="http:// sqlrs01v:80/ReportServer/ReportExecution2005.asmx" />
        <add key="SSRSSite2010" value="http:// sqlrs01v:80/ReportServer/ReportService2010.asmx" />

<add key="SMTP_Postoffice" value="postoffice.yourdomain.com"/>
<add key="AdminEmailAddress" value="youremail@.com"/>
<add key="SMTP_Password" value="yourpassword"/>

NOTE: Search the code "yourname", “yourdomain” and replace these values with your values.

After that It’s easy to call and report and have the parameters of the report automatically added to the call:

//Call Report with parameter Values
        Dictionary<string, string> dictParam =
     new Dictionary<string, string>();
        dictParam.Add("pRig", "Kendall 21");
        dictParam.Add("pStartDate", "3/14/2012");
        dictParam.Add("pEndDate", "11/30/2012");
      
        GenerateReport(dictParam, true, false, "Open Action Items By Rig");
        msgText.Text = "Message Sent!";

The method to call the report is pretty simple as well. A dictionary object is sent into the GenerateReport method as well as the report name- in this case “Open Action Items By Rig” and you are on your way. The parameters for the report are then set.

You will need to set the SSRSLibrary in your web config as well. The keys to success are setting the string mimeType = "html/text" and string format = "HTML4.0". You are able to assign different types and you can read the readme file in the project

This is the call to the web service that makes it all happen:

result = rs1.Render2(format, devInfo, PageCountMode.Estimate, out extension, out mimeType, out encoding, out warnings, out streamIDs);
            execInfo = rs1.GetExecutionInfo2();

//Code start here
using SSRS; //The proxy class

private string GenerateReport(Dictionary<string, string> dictParam, bool bGetImagePath, bool preservecomments, string Report_Name)
    {
        string reportPath = string.Empty;
        string sReportFileName = string.Empty;
        FileInfo fi = null;

        reportPath = SSRSLibrary + "/" + Report_Name;
      
        SSRS.ReportExecutionService rs1 = new SSRS.ReportExecutionService();
        rs1.Credentials = new System.Net.NetworkCredential(ClientConfiguration.WebServiceUserName, ClientConfiguration.WebServicePWD, ClientConfiguration.WebServiceUserDomain);

        ExecutionInfo2 execInfo = new ExecutionInfo2();
        execInfo = rs1.LoadReport2(reportPath, null);
        Int32 counter = 0;

        SSRS.ParameterValue[] parameters = new SSRS.ParameterValue[dictParam.Count];
        foreach (KeyValuePair<string, string> kvp in dictParam)
        {
            Console.WriteLine("Key = {0}, Value = {1}",
                kvp.Key, kvp.Value);

            // Prepare report parameters
          
            parameters[counter] = new SSRS.ParameterValue();
            parameters[counter].Name = kvp.Key;
            parameters[counter].Value = kvp.Value;
            counter = counter + 1;
        }
        rs1.SetExecutionParameters2(parameters, null);

        //See Readme for different Types   

        // Render arguments
        string encoding;
        string mimeType = "html/text";
        string extension;
        SSRS.Warning[] warnings = null;
        string[] streamIDs = null;
        string format = "HTML4.0";
        string devInfo = null;
        byte[] result = null;

        try
        {
           
            result = rs1.Render2(format, devInfo, PageCountMode.Estimate, out extension, out mimeType, out encoding, out warnings, out streamIDs);
            execInfo = rs1.GetExecutionInfo2();

            UTF8Encoding enc = new UTF8Encoding();
            string str = enc.GetString(result);
            //Hide Logo or parse string for anything
            str = str.Replace("<a", "<a style='display:none' ");
            string s = System.Text.ASCIIEncoding.ASCII.GetString(result);
            SendEmail(str, "youremail.com");

 
        }
        catch (SoapException ex)
        {
            Console.WriteLine(ex.Detail.OuterXml);
          
            return string.Empty;
        }

        sReportFileName =Report_Name;

        String ReportName = graphicspath + "\\" + sReportFileName + ".html";
        // Write the contents of the report to an MHTML file.
        fi = new FileInfo(ReportName);
        if (fi.Exists)
        {
            fi.Delete();
        }
        using (FileStream stream = File.Create(ReportName, result.Length))
        {
            stream.Write(result, 0, result.Length);
            stream.Close();
        }
     
           return string.Empty;

    }

The project code contains a simple Asp.net site with a button to call the method to create the report and send it:

Here is the e-mail that is sent:

And the code to send the mail:

//********************************************************************************
    public bool SendEmail(string msg, string emailTo)
    {
        //********************************************************************************     
        string sMsg = "";
        string sSuccess = "";

        try
        {
            sMsg = msg;

            string sFrom = ConfigurationSettings.AppSettings["AdminEmailAddress"] + "";
            string sServer = ConfigurationSettings.AppSettings["SMTP_Postoffice"] + "";
            string sSMTP_Password = ConfigurationSettings.AppSettings["SMTP_Password"] + "";
            string Devemail = ConfigurationSettings.AppSettings["devEmail"] + "";

            string sName = "";
            string sEmail = "";

            if (!emailTo.Contains("@"))
            {
                sEmail = Devemail;
            }
            else
            {
                sEmail = emailTo;
            }

            MailMessage Mailer = new MailMessage();

            try
            {
                //Mailer.Bcc = "";
                //Mailer.Cc = "";
            }
            catch
            {
            }

            System.Web.Mail.SmtpMail.SmtpServer = sServer;
            Mailer.BodyFormat = System.Web.Mail.MailFormat.Html;

            Mailer.Fields["http://schemas.microsoft.com/cdo/configuration/smtpauthenticate"] = 1;
            Mailer.Fields["http://schemas.microsoft.com/cdo/configuration/sendusername"] = sFrom;
            Mailer.Fields["http://schemas.microsoft.com/cdo/configuration/sendpassword"] = sSMTP_Password;
            var _with1 = Mailer;
            _with1.From = sFrom;
            _with1.To = sEmail;
            _with1.Subject = "An Action has been assigned to you " + sName + " please see below ";
            _with1.Body = sMsg;
            _with1.BodyFormat = System.Web.Mail.MailFormat.Html;

            SmtpMail.Send(Mailer);
            sSuccess = "Success";

        }
        catch (Exception ex)
        {
            return false;
        }

        return true;
    }
    protected void Button1_Click(object sender, EventArgs e)
    {

        //Call Report with parameter Values
        Dictionary<string, string> dictParam =
     new Dictionary<string, string>();
        dictParam.Add("pRig", "Patterson 21");
        dictParam.Add("pStartDate", "3/14/2012");
        dictParam.Add("pEndDate", "11/30/2012");
      
        GenerateReport(dictParam, true, false, "Open Action Items By Rig");
        msgText.Text = "Message Sent!";
    }

Conclusion: Sometimes it is easier to use the Reporting Services Web Service SSRS to create the reports because they usually look better than the programmer’s output of the same thing- by way of tables and code ETC. In this article and source code, any ASP.net programmer can include this in his bag of Tricks.

Happy Programming.

Download the entire source code (Github)

This article has been editorially reviewed by Suprotim Agarwal.

C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.

We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).

Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.

Click here to Explore the Table of Contents or Download Sample Chapters!

Author

Mark Kendall is a Software Engineer working in the Oil Industry in Dallas,Texas. He has been a programmer/developer for past 15 years specializing in .NET/C# development. Currently, he is a Sharepoint/Silverlight developer specializing in BCS Services. He has published a book on Dotnetnuke, and written several articles on many .net subjects. Apart from his passion for making money writing software to improve business, Mark enjoys ranting-and-raving about current events and taking long runs on the beach. He blogs at kendallsoft and can be reached at Kendallsoft[attherate]hotmail[dot]com