SQL Server 2005 Reporting Services – Reports Management & Execution Web Services

One of the important and interesting features in SQL Server Reporting Services is the web services support. They can be divided into two parts; one for the reports execution and one for the reports management.

The ReportingService2005 class is responsible for the reports management web service and contains all the required methods & properties for the same. Similarly ReportExecutionService class holds the methods & properties for the execution part. For all the member details and what each of them does, one can refer the MSDN docs; nicely documented so won’t reproduce the same stuff again 🙂 For quick reference (http://msdn.microsoft.com/en-us/library/ms155071.aspx)

The example mentioned below is a small POC that I was working on recently and I thought that someone could benefit from what I learned during experimenting with the web services. The example is not very fancy and there are definitely better ways of doing the stuff…no arguments about that. If anyone feels that this code could be of any use to them, s\he is free to go ahead and do so.

Here is the storyline that is used. The reports server has all the reports published and organized in folders based on the sensitivity of the data that the reports holds. The user would only have access to the reports under a particular folders to which he has been granted access. Mostly, the administrator would allow\restrict access to the reports and\or folders using the management studio for reporting services, but over here we are dealing (and had to mimic) with a third party web access manager who decides the access to the reports\folder based on the current user login. Based on the logged in user it would decide what reports he can see and what not.

In other words the account used to access the reports on the reports server is different than the user login account. It has access to all the reports on the server. The "logged in user’s" access is decided by the third party web access software and is "not" dependant on the windows login account.

This is different than the way it would have behaved if the rights access would have been done by the management studio. In that case the windows account and the role to which he belongs is used for deciding the access he has to a particular report.

SSRS Management Web Services

Based on the above mentioned story line, in this example we have two users Tom & Joe. They have access to the "AdventureWorks Reports" folder and "WellsReport" folder respectively. When a user is logged in and runs the app, he is only shown the respective folder\reports to which he has access. This control would be a characteristics of a web access manager and is mimicked by a dummy function (WebAccessManagerBlackBox), which takes a parameter of username. We get the current user by making a call to "System.Security.Principal.WindowsIdentity.GetCurrent().Name" and passing it as a parameter while calling the said function. The trick is that the report’s path is set to the folder to which the current user has access and thus only the reports below in the hierarchy to which the user has access are displayed.

The web service method, ListChildren returns the list of the catalogs (report items) which is then passed to populate the tree control.

This would display only the reports to which the user has access and populate the tree view control on the left. Once the user clicks on one of the report, the adjacent reports viewer control would display the selected report. To add some spice to the story line it is assumed that the user might have to open more than one report or even the same report twice so that he can do some comparitative analysis. This feature is made available by the use of the tabbed controls along with multiple reports. When the user clicks on a report in the tree view the current tab control displays the report.

Also, when the user runs the application, he is shown a dummy report which has nothing but a message asking him to click on one of the report in the tree node.

Here is the code…

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using MServices.ReportingService;

namespace MServices
{
public partial class MServicesMain : Form
{
private static string reportServer = "
/reportserver" ;?=";?">/reportserver" ;?=";?">/reportserver" ;?=";?">/reportserver" ;?>/reportserver";’>http://<machine name>/reportserver";
private static char separator = ‘/’;
private static char[] separators = { separator };
private static string separatorString = new string(separator, 1);
private static string reportPath = "/";

public MServicesMain()
{
InitializeComponent();
}

private void MServicesMain_Load(object sender, EventArgs e)
{
// added this peice to handle the restriction of the reports per user basis.
WebAccessManagerBlackBox(System.Security.Principal.WindowsIdentity.GetCurrent().Name);

ReportingService.ReportingService rService = new ReportingService.ReportingService();
rService.Credentials = System.Net.CredentialCache.DefaultCredentials;

CatalogItem[] catalogItems;
catalogItems = rService.ListChildren(reportPath, true);

PopulateTree(catalogItems);

// added this peice to handle the restriction of the reports per user basis.
if (!reportPath.Equals("/"))
{
ReportTreeView.Nodes[0].Text = reportPath.Substring(1);
}
ReportViewer1.RefreshReport();
}

private void WebAccessManagerBlackBox(string username){
if (username.Equals("ADV-SQL2\\Tom"))
{
reportPath = "/AdventureWorks Reports";
}
if (username.Equals("ADV-SQL2\\Joe"))
{
reportPath = "/WellsReport";
}
}

private void PopulateTree(CatalogItem[] catalogItems)
{
foreach (CatalogItem item in catalogItems)
{
if (item.Type == ItemTypeEnum.Report && item.Name!= "DummyReport")
{
string path = item.Path.Remove(0, reportPath.Length);
string[] tokens = path.Split(separators);
AddNodes(tokens, 0, ReportTreeView.Nodes);
}
}
}

private void AddNodes(string[] tokens, int index, TreeNodeCollection nodes)
{
TreeNode node = null;

for (int i = 0; i < nodes.Count; i++)
{
if (nodes[i].Text == tokens[index])
{
node = nodes[i];
break;
}
}

if (node == null)
{
node = new TreeNode();
node.Text = tokens[index];
nodes.Add(node);

if (tokens.Length – 1 == index)
{
node.Tag = String.Join(separatorString.ToString(), tokens);
node.Text = tokens[tokens.Length – 1];
}
}

index++;
if (tokens.Length > index)
{
AddNodes(tokens, index, node.Nodes);
}
}

private void ReportTreeView_NodeMouseClick(object sender, TreeNodeMouseClickEventArgs e)
{
string tag = String.Empty;

if (e.Node.Tag != null)
{
tag = e.Node.Tag.ToString();

((Microsoft.Reporting.WinForms.ReportViewer)tabControl.SelectedTab.Controls[0])
.ServerReport.ReportServerUrl = new Uri(reportServer);

((Microsoft.Reporting.WinForms.ReportViewer)tabControl.SelectedTab.Controls[0])
.ServerReport.ReportPath = reportPath + tag;

((Microsoft.Reporting.WinForms.ReportViewer)tabControl.SelectedTab.Controls[0]).RefreshReport();

tabControl.SelectedTab.Text = tag.Substring(tag.LastIndexOf("/")+1);
}
}
}
}

The image shows the form in action…

SSRS Execution Web Services

The sample in the online documentation does an excellent job of getting the concept across. Please refer to the same at the following URL for more details.

http://technet.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx

Advertisements
This entry was posted in Reporting Services. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s