SQLUNINTERRUPTED

I am just a medium, SQL Server the Goal

Tag Archives: Report Server

Render and Email a Reporting Services Report using .Net

2 Comments Posted by on March 19, 2012

In an earlier post, I had described a customer scenario, where they would want to execute and render a report from a .Net application, and then send the rendered report as an email attachment.

In the previous post, I had talked about how to generate a Tree View of the Report Server Items. In this post, I would be talking mainly about the how to execute a report and render it in a required format. And then send the Report as an email attachment.

The Post, assumes the following

  1. The report which needs to be executed is selected on the Tree View and the report path is passed to the function.
  2. The Report runs with default parameters.
  3. The SMTP mail server is preconfigured.

In order to execute a Report, we need to use the ReportExecutionService() Class, defined in the ReportExecution2005 endpoint for Reporting Services.

I created a new class ExecuteAndRender for this functionality.

The ExecuteAndRender Class, defines and object for the ReportExecutionService() class, in order to execute the methods exposed by Reporting Services.

   1: public ExecuteAndRender()

   2:         {

   3:              rs = new ReportExecutionService();

   4:              rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

   5:              rs.Url = "http://localhost/reportserver/ReportExecution2005.asmx";

   6:         }

Next I created a function RenderReport(string ReportName), this function performance the following tasks

  1. Define the Report Rendering and execution settings.
  2. Calls the ReportExecutionService.LoadReport() to load the report definition.
  3. Defines the report parameters. In this case, since all default values would be used, we do not explicitly define the parameters.
  4. Calls the ReportExecutionService.Render() method to render the report in the desired format.
  5. One the report have been rendered, we create a PDF file for the report.
  6. Then call the AttachAndSendEmail() to send the email. 
   1: public void RenderReport (string ReportName)

   2:         {

   3:             byte[] result = null;

   4:             //string reportPath = "/ReportFolder2";

   5:             string format = "PDF";

   6:             string historyID = null;

   7:             string devInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>";

   8:  

   9:             //DataSourceCredentials[] credentials = null;

  10:             //string showHideToggle = null;

  11:             string encoding;

  12:             string mimeType;

  13:             string extension;

  14:             Warning[] warnings = null;

  15:             

  16:             //ParameterValue[] reportHistoryParameters = null;

  17:             string[] streamIDs = null;

  18:  

  19:             ExecutionInfo execInfo = new ExecutionInfo();

  20:             ExecutionHeader execHeader = new ExecutionHeader();

  21:  

  22:             rs.ExecutionHeaderValue = execHeader;

  23:             execInfo = rs.LoadReport(ReportName, historyID);

  24:  

  25:             //ReportParameter[] param = execInfo.Parameters;

  26:  

  27:             // Prepare report parameter. Currently the report executes with Default paramerts.

  28:             // Here we retrieve the 

  29:             // But if required the Report Paramaeters can be added.

  30:             

  31:             ParameterValue[] parameters = new ParameterValue[execInfo.Parameters.Length];

  32:  

  33:             rs.SetExecutionParameters(parameters,"en-us");

  34:             String SessionId = rs.ExecutionHeaderValue.ExecutionID;

  35:  

  36:             try

  37:             {

  38:                 result = rs.Render(format, devInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);

  39:                 execInfo = rs.GetExecutionInfo();

  40:             }

  41:             catch (SoapException e)

  42:             {

  43:                 throw e;

  44:             }

  45:             // Write the contents of the report to an PDF file.

  46:             try

  47:             {

  48:                 FileStream stream = File.Create("report.pdf",result.Length);

  49:                 stream.Write(result, 0, result.Length);

  50:                 stream.Close();

  51:                 ///Now Send the email

  52:                 AttachAndSendEmail("report.pdf");

  53:             }

  54:             catch (Exception e)

  55:             {

  56:                 throw e;

  57:             }

  58:         }

Next, we have the AttachAndSendEmail() function to connect to the SMPT server and send the email. This function achieves the following tasks

  1. Create an SMTP client to connect to the SMTP server.
  2. Create the From and To Addresses for the email.
  3. Creates the MailMessage with the attachment.
  4. And Sends the email. 
   1: public void AttachAndSendEmail(string ReportFileName)

   2:         {

   3:  

   4:             SmtpClient client = new SmtpClient("Address of the SMTP Server", 25);

   5:             client.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;

   6:  

   7:             MailAddress from = new MailAddress("<from Address here>","<DisplayNamehere>",

   8:                             System.Text.Encoding.UTF8);

   9:  

  10:             MailAddress to = new MailAddress("<To Address here>","<DisplayNamehere>",

  11:                 System.Text.Encoding.UTF8);

  12:  

  13:  

  14:             MailMessage msg = new MailMessage(from,to);

  15:             msg.Subject = "Report Attached " + DateTime.Now.ToString();

  16:             msg.Body = "Report From Report Server";

  17:             msg.BodyEncoding = System.Text.Encoding.UTF8;

  18:             msg.Attachments.Add(new Attachment(ReportFileName));

  19:  

  20:             try

  21:             {

  22:                 client.Send(msg);

  23:             }

  24:             catch (System.Net.Mail.SmtpException e)

  25:             { throw e; }

  26:         }

 

This code sends the messages in an synchronous manner. In case if required, SMTPClient.SendAsync method can also be used.

Advertisement
Reporting Services, SQL Server , , ,

Generating a Tree view of Report Server Items using .Net

1 Comment Posted by on March 15, 2012

A couple of weeks back, while taking a SSRS workshop for a customer, I was presented with an interesting problem. Customer wanted a functionality wherein, Report Users would be allowed to view the list of the reports existing on the Report Server using an application (and not Report Manager), and they could only execute the selected reports using a button on the .Net/Web application and can only receive he rendered report using Email delivery.

One important aspect which really helped in this scenario was that the reports were not dependent on User parameters. All the reports had to be executed with Default parameters.

We tried to break down the requirements into steps

  1. Forms based/Web Application to list all the available reports on the Report Server. Obviously, only the reports on which the user has access would be visible.
  2. Provide the User with a Text Box for entering their email address.
  3. Provide a button, upon clicking which the reports would be executed and send to the User as an email.

While creating the list of all the available reports was pretty simple since ReportService2010 has ListChildren method which can be used. Executing the report and emailing it to the user is where the major challenge is.

We pondered over multiple ways of doing it, for example

  • We could create a run time subscription for the Report and send the results to the user using Email Delivery. Once the email is sent, the subscription can be deleted.
  • We could execute the reports with the required parameters (parameters are default) and then use the Email delivery method to send the reports to the users.
  • We could create a on Demand subscription for the report and execute it when the user clicks on the button.

In the customer environment, we ended up implementing the second option, since the other two options had issues with concurrency. If multiple users were accessing the Web Application, we would need to create multiple subscription run time, which was not a very scalable approach. If we used a pre-created subscription, we would not be able to run it for multiple users.

In this post, I will be talking mainly about how to generate a tree view of the Report Server Items. In the next parts I will be talking about the other options which we tried to implement at the customer end.

In order to generate a Tree View of the Report Server Catalog, the first thing I did was bind the Report Server to a Report Server endpoint(ReportService2010 in this case). I created a class ListRSItems, which implements three functions.

  • Constructor : To initialize the ReportService2010 Proxy class and dynamically bind the Report Server instance to it.
  • ReturnRSChildList(string path) : To return a list of Report Server Catalog Items, for a given report server path. This function only returns the immediate children of the Report Server item, specified as input to the function.
  • GetReportServerName(): This function is used to return the name of the Report Server instance. If it’s a default instance of Reporting Services, it would return the Machine name, otherwise it would return the report server name in the format <machinename/instancename>.

Included below is the code for the Class.

   1: public class ListRSITems

   2:     {

   3:         private ReportingService2010 rs;

   4:  

   5:         public ListRSITems()

   6:         {

   7:             try

   8:             {

   9:                 // Connect to Reporting Services

  10:                 rs = new ReportingService2010();

  11:                 rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

  12:                 rs.Url = "http://localhost/reportserver/ReportService2010.asmx";

  13:             }

  14:             catch (System.Exception ex)

  15:             {

  16:                 Console.WriteLine(ex.Message);

  17:             }

  18:         }

  19:         public CatalogItem[] ReturnRSChildList(string path)

  20:         {

  21:              return this.rs.ListChildren(path, false);                    

  22:         }

  23:         public string GetReportServerName()

  24:         {

  25:             string RSName = "";

  26:             //RSName = this.rs.GetReportServerConfigInfo(false);

  27:             System.Xml.XmlDocument doc = new System.Xml.XmlDocument();

  28:             doc.LoadXml(this.rs.GetReportServerConfigInfo(false));

  29:  

  30:             RSName = doc.SelectSingleNode("ServerConfigInfo/Server/MachineName").InnerText;

  31:             string temp = doc.SelectSingleNode("ServerConfigInfo/Server/InstanceName").InnerText;

  32:             if ( temp == "MSSQLSERVER")

  33:                 return RSName;

  34:             else

  35:                 return RSName + "/" + temp;            

  36:         }

  37:     }

Next, we need to Add a TreeView control to the windows Forms (I am using a Forms Application, but this can be used in a Web Application also) and bind the Report Server Items as Tree Nodes. For this I added two more functions to the Form class.

  • GenerateTreeView() : This is the parent function, where we create the Root Node for the Tree. The Root node name is derived from the function ListRSItems.GetReportServerName(). After the root node has been initialized, we call the ListRSItems.ReturnRSChildList, to return all first level (Child Items for the root level). For all the child items returned we call a function AddTreeNodes(), to add the specified item and all its child items to the TreeView.
  • AddTreeNodes(TreeNode parent, CatalogItem c) : In this function we add all child nodes and subsequent child nodes to the TreeView. This function calls itself recursively to find all the sub child items and add them to the TreeNode.

Code Included below: 

   1: public partial class Form1 : Form

   2:     {

   3:         public Form1()

   4:         {

   5:             LRT = new ListRSITems();

   6:             InitializeComponent();

   7:             GenerateTreeView();

   8:         }

   9:  

  10:         public void GenerateTreeView()

  11:         {

  12:             string path = "/";

  13:             CatalogItem[] items = this.LRT.ReturnRSChildList(path);

  14:  

  15:             TreeNode root = new TreeNode();

  16:             root.Text = LRT.GetReportServerName();

  17:             root.ToolTipText = "Report Server Name";

  18:             root.ImageIndex = root.SelectedImageIndex = 0;

  19:             TreeView1.Nodes.Add(root);

  20:             TreeView1.BeginUpdate();

  21:             foreach(CatalogItem c in items)

  22:             {

  23:                 AddTreeNodes(root, c);

  24:             }

  25:             TreeView1.EndUpdate();

  26:         }

  27:         public void AddTreeNodes(TreeNode parent, CatalogItem c)

  28:         {

  29:             TreeNode newnode = new TreeNode();

  30:             if (c.TypeName == "Folder")

  31:             {

  32:                 CatalogItem[] items = this.LRT.ReturnRSChildList(c.Path);

  33:                 newnode.Text = c.Name;

  34:                 newnode.ToolTipText = "Report Server Folder";

  35:                 newnode.ImageIndex = newnode.SelectedImageIndex = 0;

  36:                 parent.Nodes.Add(newnode);

  37:                 foreach (CatalogItem x in items)

  38:                 {

  39:                     AddTreeNodes(newnode, x);

  40:                 }

  41:             }

  42:             if (c.TypeName == "Report")

  43:             {

  44:                 newnode.Text = c.Name;

  45:                 newnode.ToolTipText = "Report Server Report";

  46:                 newnode.ImageIndex = newnode.SelectedImageIndex = 2;

  47:                 parent.Nodes.Add(newnode);

  48:             }

  49:             if (c.TypeName == "DataSource")

  50:             {

  51:                 newnode.Text = c.Name;

  52:                 newnode.ToolTipText = "DataSource";

  53:                 newnode.ImageIndex = newnode.SelectedImageIndex = 1;

  54:                 parent.Nodes.Add(newnode);

  55:             }

  56:             if (c.TypeName == "LinkedReport")

  57:             {

  58:                 newnode.Text = c.Name;

  59:                 newnode.ToolTipText = "Linked Report";

  60:                 newnode.ImageIndex = newnode.SelectedImageIndex = 4;

  61:                 parent.Nodes.Add(newnode);

  62:             }

  63:             if (c.TypeName == "Model")

  64:             {

  65:                 newnode.Text = c.Name;

  66:                 newnode.ToolTipText = "Report Model";

  67:                 newnode.ImageIndex = newnode.SelectedImageIndex = 3;

  68:                 parent.Nodes.Add(newnode);

  69:             }

  70:         }

  71:     }

The Code generates the following TreeView Structure for my Report Server instance.

image

In another post, I will talk about how to programmatically, execute a Report and send the rendered report (in any format) as an email to user.

Reporting Services, SQL Server , ,

Dynamically binding a Web Service reference to a Report Server Instance

3 Comments Posted by on March 4, 2012

One of the most important aspect of SQL Server Reporting Services, which makes it stand out amongst competition is its extensibility. If you know .Net programming, then you can almost anything with Reporting services. In this series of posts I would be talking about some things which can be done with .Net and Reporting Services.

Typically speaking, .Net with Reporting Services can be used to create any of the following.

  • .Net Applications (console, UI bases) to automate SSRS tasks.
  • Reporting Services extensions (data, security, delivery etc.) to be used with Reporting Services.
  • Reference libraries to be used directly with the Reports in Reporting Services.
  • .Net code to (web service, Forms application) to consume Reporting Services Reports.

In this post, I would be talking about how to dynamically bind a report server instance using a web service reference.

Reporting Services Web service provides a SOAP over HTTP interface for communication with the Report Server. It provides two endpoints, one for execution and another for management. The most important thing, when creating an application for SSRS is to the add the Reporting services proxy class to the project to the class. This can be done using one of the two ways.

Once you have added the web service reference, using any of the two methods described above, the most important thing is to dynamically bind the web service to a Report Server instance.

To dynamically bind the web service to any Report Server of your choice.

  • First create an instance of the SSRS web service, which was added to the project. For example, if you added the ReportService2010 endpoint, create an object for the same.
  • Set the URL property of this object to the desired Report Server web service URL.
  • Define appropriate security credentials.
ReportingService2010 rs = new ReportingService2010();

rs.Url = "http://localhost/reportserver/ReportService2010.asmx";

rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

To make sure that the URL property is recognized by the compiler, you would need to add the following reference to your code.

using System.Web.Services;

At this point, we can call any method defined in the Web Service and perform the desired operation.

In the next post, I will talk about, creating an Application, which can upload, all the .RDL files present in a folder to a report server.

Reporting Services , , , , ,