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

Posted by: Mark Kendall , on 11/14/2012, in Category ASP.NET
Views: 105416
Abstract: Sometimes you need to get the results of a report from SSRS and send an e-mail to your users. In this article, we will look at how this is done in ASP.NET by calling the SSRS Execution Web service

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:

ASP.NET SRRS Integration

Here is the e-mail that is sent:

image

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.

Absolutely Awesome Book on C# and .NET

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!

What Others Are Reading!
Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+

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


Page copy protected against web site content infringement 	by Copyscape




Feedback - Leave us some adulation, criticism and everything in between!
Comment posted by Shiyjt on Wednesday, November 28, 2012 3:54 AM
Thanks. Very useful information for my ASP.NEt project
Comment posted by sachin on Friday, November 30, 2012 4:10 AM
Really good way for generating Reports
Comment posted by Balaji on Saturday, December 1, 2012 3:05 AM
Excellent article. I would rate this as a+100 if I can. Thank you very much as my search ends here on this topic.
Comment posted by Utkarsh on Monday, December 10, 2012 4:28 AM
Thanks. Good Article. But my question is this is for server side reporting.
But if I want to email client side reporting then what should be done for that? Thanks in advance!!!
Comment posted by Michael Normand on Wednesday, January 9, 2013 12:58 AM
Really good article! Is the code able to process an embedded image from the report and include them in the output? The issue I'm facing is that SSRS generates encrypted IMG SRC links which requires user authentication on SSRS.
Comment posted by d. Siva on Monday, March 4, 2013 7:37 AM
could you please answer my question ?
http://stackoverflow.com/questions/15068583/sending-parameters-from-mvc-4-action-to-web-form-for-the-ssrs-report

Thanks in Advance
Comment posted by Yogendra Sharma on Saturday, August 23, 2014 4:32 AM
thanks

Categories

JOIN OUR COMMUNITY

POPULAR ARTICLES

C# .NET BOOK

C# Book for Building Concepts and Interviews

Tags

JQUERY COOKBOOK

jQuery CookBook