SQLUNINTERRUPTED

I am just a medium, SQL Server the Goal

Tag Archives: Catalog

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.

Advertisement
Reporting Services, SQL Server , ,