SQLUninterrupted

I am just a medium, SQL Server the Goal

Archive for the ‘Reporting Services’ Category

Generating a Tree view of Report Server Items using .Net

Posted by Sourabh Agarwal 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.

Posted in Reporting Services, SQL Server | Tagged: , , | 1 Comment »

Uploading multiple Reports to the Report Server using .Net

Posted by Sourabh Agarwal on March 8, 2012

Continuing the series on Programming for Reporting Services, we will see how to upload multiple reports (*.rdl) files present in a folder, to the Report Server. While this can be done using Report Manager (uploading the reports one-by-one), this obviously is not an efficient way of doing it. In this post, we will see how we can automate the task using .Net.

The code, here assumes that all the *.rdl present in a file system folder would be uploaded to the same Report Server folder. if you require to add reports to multiple folder, then I suggest you create a folder hierarchy, to replicate your Report Server folder hierarchy.

For the purpose of this post, I have created the below mentioned folder hierarchy. Each folder contains some report, which needs to be uploaded to the report server. The reports would be uploaded to a similar folder hierarchy on the Report Server. If the folders do not exists on the Report Server, it would be first created and then the report uploaded.

image

Each of this folders contain some RDL file, which needs to be uploaded to the Report Server. I have created a Console Application for this purpose.

The First step would be create a method to iterate through the folder structure and read the RDL files.  Then we would need a method read the RDL files and get the report definition. This can be done using one of the IO stream classes in .Net. In my console Application, I have added a C# class named ReadReportDefinition.cs. Code included below.

   1: public static class ReadReportDefinition

   2:     {

   3:         public static void IterateFolderHierarchy(string folderpath)

   4:         {

   5:             ConfigureReports cr = new ConfigureReports();

   6:             string connectionString = "data source=." + ";Initial Catalog=PowerSaverLogs";

   7:             cr.UploadDataSource("PowerManagementDataSource", "SQL", connectionString);

   8:             try

   9:             {

  10:                 DirectoryInfo dir = new DirectoryInfo(folderpath);

  11:                 try

  12:                     { 

  13:                         DirectoryInfo[] subdirectories = 

  14:                                     dir.GetDirectories("*", SearchOption.TopDirectoryOnly);

  15:                         byte[] ReportDefinition = null;

  16:                         foreach (DirectoryInfo Dir in subdirectories)

  17:                         {

  18:                             cr.CheckReportServerFolder(Dir.Name);

  19:                             FileInfo[] ReportFiles = Dir.GetFiles("*.rdl");

  20:                             foreach (FileInfo file in ReportFiles)

  21:                             {

  22:                                 FileStream stream = File.OpenRead(file.FullName);

  23:                                 ReportDefinition = new Byte[stream.Length];

  24:                                 stream.Read(ReportDefinition, 0, (int)stream.Length);

  25:                                 stream.Close();

  26:                                 cr.UploadReports(Dir.Name,ReportDefinition, file.Name.Substring(0, (file.Name.Length - 4)));

  27:                                 ReportDefinition = null;

  28:                             }

  29:  

  30:                         }

  31:                 }

  32:                 catch(System.IO.IOException E)

  33:                     {

  34:                         Console.WriteLine("System Exception Message" + E.Message);

  35:                     }

  36:                 

  37:             }

  38:             catch(System.IO.DirectoryNotFoundException e)

  39:             {

  40:                 Console.WriteLine("System Exception Message" + e.Message);

  41:             }

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

The next step, would be to add a reference to one of the Reporting Services endpoints, and then dynamically binding it to the Report Server, where the reports needs to be uploaded. For information about, how to add a reporting service endpoint and dynamically bind it to a Report Server, click here.

The next step, is to create a class or methods to call the ReportingService2010 endpoint (this is what I am using in my code) and perform the required actions. In this case, I created a new Class “Configure Reports” to perform the desired operations.

This class has 3 methods, and the constructor. In the constructor, I dynamically bind the endpoint to my Report Server instance.

  • CheckReportServerFolder: Checks if a particular folder exists on the report server on not. This uses the ReportingService2010 Listchildren method.
  • UploadReports : Uploads reports to the Report Server. This function calls the ReportingService2010 CreateReport method.
  • UploadDataSource: To upload the datasource to the report server. Uses the ReportingService2010 CreateDataSource function.
   1: using System;
   3: using System.Linq;

   2: using System.Collections.Generic;

   4: using System.Text;

   5: using System.Web.Services;

   6:  

   7: namespace UploadMultipleReports

   8: {

   9:     public class ConfigureReports

  10:     {

  11:         private const string Root = "/";

  12:         private ReportingService2010 rs;

  13:  

  14:         public ConfigureReports()

  15:         {

  16:             try

  17:             {

  18:                 // Connect to Reporting Services

  19:                 rs = new ReportingService2010();

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

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

  22:             }

  23:             catch (System.Exception ex)

  24:             {

  25:                 Console.WriteLine(ex.Message);

  26:             }

  27:  

  28:         }

  29:         public void CheckReportServerFolder(string foldername)

  30:         {

  31:             int count = 0;

  32:             int datasourcecount =0;

  33:             try

  34:             {

  35:                 CatalogItem[] list = rs.ListChildren("/", false);

  36:                 foreach (CatalogItem c in list)

  37:                 {

  38:                     if ((c.TypeName == "Folder") & (c.Name == foldername))

  39:                         count += 1;

  40:                     if((c.TypeName=="Data Source")& (c.Name == "PowerManagementDataSource"))

  41:                         datasourcecount+=1;

  42:                 }

  43:                 if (count == 0)

  44:                     rs.CreateFolder(foldername, "/", null);

  45:             }

  46:             catch (Exception e)

  47:             {

  48:                 Console.WriteLine(e.Message);

  49:             }

  50:         }

  51:         public void UploadReports(string ParentFolder,byte[] definition,string ReportName)

  52:         {

  53:             try

  54:             {

  55:                 string parent = "/" + ParentFolder;

  56:                 /// Create the Reports

  57:                 ///

  58:                 Warning[] warnings = null;

  59:                 rs.CreateCatalogItem("Report", ReportName, parent, true, definition, null, out warnings);

  60:                 if (warnings != null)

  61:                 {

  62:                     Console.WriteLine("Report {0} could not be uploaded to folder {1}", ReportName, ParentFolder);

  63:                     foreach(Warning war in warnings)                    

  64:                         Console.WriteLine("Warning Generated {0}", war.Message);

  65:                 }

  66:             }

  67:             catch (Exception e)

  68:             {

  69:                 Console.WriteLine(e.Message);

  70:             }

  71:  

  72:         }

  73:         public void UploadDataSource(string name, string extension, string connstring)

  74:         {

  75:             const string Parentpath = "/";

  76:             DataSourceDefinition definition = new DataSourceDefinition();

  77:             definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated;

  78:             definition.ConnectString = connstring;

  79:             definition.Extension = extension;

  80:             definition.Enabled = true;

  81:             definition.EnabledSpecified = true;

  82:             definition.ImpersonateUserSpecified = true;

  83:             // Use the default prompt string.

  84:             definition.Prompt = null;

  85:             definition.WindowsCredentials = false;

  86:             try

  87:             {

  88:                 rs.CreateDataSource(name, Parentpath, false, definition, null);

  89:             }

  90:             catch (Exception e)

  91:             {

  92:                 Console.WriteLine(e.Message);

  93:             }

  94:         }

  95:     }

  96: }

Once the classes are in place, all we need to do is call them in the application main function.

   1: class Program

   2: {

   3:     static void Main(string[] args)

   4:     {

   5:         Console.WriteLine("Enter Report File Root Directory");

   6:         string folderpath = Console.ReadLine();

   7:         ReadReportDefinition.IterateFolderHierarchy(folderpath);

   8:         Console.ReadLine();

   9:     }

  10: }

Posted in Reporting Services | Tagged: , , , , | Leave a Comment »

Dynamically binding a Web Service reference to a Report Server Instance

Posted by Sourabh Agarwal 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.

Posted in Reporting Services | Tagged: , , , , , | 3 Comments »

Adding a Reporting Services Web Reference to .Net application

Posted by Sourabh Agarwal on March 4, 2012

In an earlier post, I had talked about adding a Reporting Services Proxy class using WSDL.exe. In this post, I would talking about adding a Reporting Services Web Service reference to your .Net Application.

As mentioned in the earlier post, SSRS web service provides interface for communication with the Report Server. In order to use the methods exposed by the Web Service, a reference to the Web Service needs to be added to the Visual Studio project. Depending on the requirement, a reference can be added for any of the reporting services endpoints.

  1. ReportService2005
  2. ReportService2006
  3. ReportExecution2005
  4. ReportService2010 (to be used with SSRS 2008 R2).

In order to add, a web service reference to your project, follow the steps provided below.

In solution explorer, right click on the project node and select “Add Service Reference”

image

On the Add Service Reference Window, click on Advanced. This would open the “Service Reference Setting” window. On the Service Reference Setting window, click on “Add Web Reference”.  On the Add Reference window, in the URL section, provide the complete asmx URL for the Reporting Service endpoint which needs to be used. For example, I would be using the ReportService2010 endpoint. Click on Go.

image

When you click on go, the web service reference would be resolved, and the methods available in the web service would be displayed.

In the Web Service Reference name, you can provide any name of your choice, and click on add reference. This would add the desired endpoint reference to your project.

At this post, we have added a web reference to the project. In a next post, I will talk about how to dynamically bind the reference or the proxy class to a Report Server.

Posted in Reporting Services | Tagged: | 4 Comments »

Generating a Reporting Services proxy class using WSDL.exe

Posted by Sourabh Agarwal on February 27, 2012

Reporting Services Web Service provides a communication interface for Report Server and the client applications. It uses SOAP over HTTP and provides two endpoints (one for report server execution and one for management). The web services provides multiple methods and types which can be used to play around with Reporting Services.

SSRS 2005 and SSRS 2008 expose three different endpoints, which are defined in different WSDL(Web Service Description Language) files.

  • ReportService2005: This is used to manage and work with Report Server which is running in the Native Mode. The WSDL for this endpoint is typically defined as

http://<servername>/<reportserver>/ReportService2005.asmx?wsdl

Where <servername> is the name of the Machine where SSRS is installed and <ReportServer> is the virtual directory name for Reporting Services web service.

  • ReportService2006: This is used to manage and work with Report servers which are running in the SharePoint Integration Mode. The WDSL for this endpoint is.

http://<servername>/<reportserver>/ReportService2006.asmx?wsdl

  • ReportExecution2005: This endpoint is used to control Report Server Execution. for example, to stop a running subscription.The WDSL for this endpoint is.

http://<servername>/<reportserver>/ReportExecution2005.asmx?wsdl

With SSRS 2008 R2, the functionalities of (ReportService2005 and ReportService2006, now depricated) endpoints were integrated to a single WSDL, ReportService2010.

In order to use these WSDL’s in your application, a web service reference should be added. In case, if we do not want to add a web service reference, we can use the Visual Studio utility WSDL.exe to generate a proxy class for these endpoints and add it to the application.

In this post, I will be talking about how to use WSDL.exe to generate a proxy class for Reporting Services. On my Laptop running Windows7, WSDL.exe is located in the following folder.

“C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin”

Open a Command Prompt window and browse to the above mentioned folder. From the command prompt launch the WSDL.exe. For generating the proxy class, the following Parameters should be enough.

<url or path> A url or path to a WSDL contract, an XSD schema or .discomap document.
/language:<language> The language to use for the generated proxy class.  Choose from ‘CS’,’VB’, ‘JS’, ‘VJS’, ‘CPP’ or provide a fully-qualified name for a class implementing System.CodeDom.Compiler.CodeDomProvider.  The default language is ‘CS’ (CSharp).  Short form is ‘/l:’.
/out:<fileName|directoryPath> The filename or directory path for the generated proxy code. The default filename is derived from the service name. Short form is ‘/o:’.

Let’s say, we need to create the proxy class for the ReportService2005 endpoint, the WSDL.exe command would be

wsdl.exe "http://<ReportServerHostName>/reportserver/ReportService2005.asmx" /l:CS /out:"D:\RS2005.cs"
On successful completion, you should see the following information in the Command Prompt.
Microsoft (R) Web Services Description Language Utility
[Microsoft (R) .NET Framework, Version 2.0.50727.1432]
Copyright (C) Microsoft Corporation. All rights reserved.
Writing file 'D:\Test.cs’.

Once the File is generated, it can be added to you project as a class file. Using the following steps.
  • In your Visual studio project, open Solution Explorer and Right Click on your Project and click on Add, Existing item.

image

  • In the Add Existing Item dialogue box, locate the RS2005.cs file saved above and add it.

image

  • Once the RS2005.cs, file has been added, we can use the classes/methods just like any C# class.

Details about the methods and functionalities provided by these endpoints can be found at

ReportService2005

ReportService2006

ReportExecution2005

ReportService2010

In the next post, I will talk about how to add a Reporting Services Web Service Reference to your project.

Posted in Reporting Services | Tagged: , , | 2 Comments »

 
Follow

Get every new post delivered to your Inbox.

Join 82 other followers