Microsoft SharePoint Team Blog

How We Did It – Allowing Connections to Multiple SSRS Servers with Report Viewer and Explorer Web Parts

Overview

Today’s guest blog post is from ThreeWill, a Microsoft Managed Gold Partner located in Alpharetta, Georgia that focuses on SharePoint development and integration. You might remember them from a previous article about the SharePoint Connector for Confluence. They worked with Microsoft on a recent project to extend some of the features of SSRS Web Parts to meet the needs of the enterprise.

In this project, ThreeWill helped a large telecommunications organization address their concern of being bound to one SSRS Reporting Server with the web parts. This concern was primarily because they did not have the luxury of combining all reports into one scaled SSRS environment. Of course, like so many other projects, the client needed a solution today, not months from now… this article has the details behind what they did on a week by week basis. Over to ThreeWill.

Pej Javaheri, SharePoint Product Manager.

First, a little bit more background. The core requirements of the solution were to enable SSRS web parts in SharePoint Server 2007 to point to multiple SSRS environments, a capability available in SSRS web parts for SharePoint 2003, with parameterization capability with the filter framework in SharePoint Server 2007. In essence, combine the best qualities of SSRS web parts from 2003 with 2007, as well ensuring usability was easy for end-users with minimal or no training required.  Here’s how we did it and our approach.

Week 1

Getting Started

The obvious starting point for the project was to evaluate existing SSRS Web Parts available from Microsoft. We took a look at the v2 and v3 SSRS Web Parts available for SharePoint. We had a technology spike to determine whether to extend or rewrite either version of the Web Parts. After discussing the options with the SQL Server Product Team, we decided to implement two new Web Parts that use a similar design pattern used by the v2 Web Parts. From that foundation, we added additional features like managing Reporting Parameters either through the Web Part Editor or through Web Part Connections. Below is a comparison of the existing SSRS Web Parts compared to what was custom built.

The objective of the project was to build the following Web Parts:

• Report Explorer (Top Web Part) - allows you to browse existing reports on a SSRS Server and eventually select a report that is passed to the Report Viewer
• Report Viewer (Bottom Web Part) - allows you to display a preconfigured report (or a report determined at runtime through Web Part Connections).

Requirements Baseline

Due to budget and timeline, we had to make a judgment of what was appropriate to reuse from the architecture of SSRS and the existing SSRS Web Parts. We found the architecture of the v2 Web Parts were the best place to start to allow for code reuse and connection to multiple servers. The v2 Web Parts leverage the use of IFrames to an ASPX page hosted on the SSRS Server. This allowed the end user’s security credentials to pass through from the browser to the Report Server vs. a double hop through custom code in the Web Part. This also saved time with not having to recreate the UI elements and behaviors needed for a Report Explorer and Viewer. We extended the v2 capabilities in the Report Viewer by giving support for report parameters through Web Part Properties. The properties for the parameters leveraged the Web Services interface of the Report Server to, at runtime, render labels and controls for each available parameter on the report. By the end of the first week we finalized the product backlog – see below table for a sampling the product backlog items for each of the Feature Groups.

Week 2-3

Implementing the Report Explorer Web Part

To start the second week, we hit the ground running on building the new Web Parts that included a new Report Viewer and Report Explorer. In the interest of time, we did our best to leverage patterns and assets used by the v2 and v3 SSRS Web Parts. Note that SSRS Server natively hosts an ASPX page that contains the Report Explorer UI that is found in the v2 SSRS Web Part and leveraged by the SSRS built-in Report Manager. The Custom Report Explorer Web Part uses an IFrame to host this ASPX page. There are two parameters that are used to configure the Web Part – the “Reporting Server URL” and the “Starting Path” (screen shot of the Web Part Properties can be seen below). Note that the UI of the Report Explorer Web Part renders a view much like Windows Explorer (as shown earlier in this post). The “Report Server URL” points to the location of the SSRS Reporting Server. The “Starting Path” describes where to start in the view in case you wish to start in a subfolder. For example, you can configure the Web Part to start in the “HR Reports” folder when configuring for a HR SharePoint Site.

Adding Web Part Connections

To make Web Parts more useful, you can develop a Web Part to accept connections from other Web Parts on the same page. By creating connectable Web Parts, you have the ability to create new and more meaningful ways to view data and interact with a Web Part Page. In the case of the Report Explorer and Report Viewer, we want to pass the id of the SSRS Report Viewer Web Part back to the Report Explorer Web Part. The SSRS Report Explorer uses this id to specify which Report Viewer Web Part instance should be the “target” when a report link in the Report Explorer Web Part is clicked. In the screen shot below, we are setting the Web Part connection on the Report Explorer Web Part to connect to the Report Viewer Web Part.

The IWebPartField, shown below, is one of several standard interfaces used to create connectable Web Parts. As the name implies, IWebPartField is intended to pass a “field” of data while IWebPartRow and IWebPartTable are meant to pass a row and table respectively. Connections are enabled through the use of ConnectionProvider and ConnectionConsumer attributes.

Report Explorer Implementation

[ConnectionProvider("Report Explorer")]

public IWebPartField GetConnectionInterface()

{

_isWebPartConnected = true;

return this;

}

Report Viewer Implementation

//Get the connection

[ConnectionConsumer("Report Viewer", "ReportViewer",AllowsMultipleConnections=false)]

public void SetConnectionInterface(IWebPartField fieldProvider)

{

CustomReportExplorer reportExplorer = fieldProvider as

CustomReportExplorer;

if (reportExplorer != null)

{

reportExplorer.ConsumerId = this.ID;

_isWebPartConnected = true;

}

}

To learn more about Web Part Connections, see documentation on the SPWebPartConnection Class.

Implementing the Report Viewer Web Part

Like the Report Explorer Web Part, the Report Viewer Web Part renders content provided by the SSRS server through use of an IFrame for the ASPX page. Key features implemented to manage the interaction with the ASPX page are: a) a connection to the Report Explorer Web Part to allow a report to be passed to the Report Viewer, b) support for MOSS Filter Web Parts to be able to pass in reporting parameters through Web Part Connections, and c) ability for report parameters to be specified in the Web Part properties.

The Report Viewer Web Part uses the GetReportParameters method of the ReportServices2005 Web Service to retrieve report parameters. The Report Parameters from this method call are used for two purposes.

1. The parameter prompt (i.e. Display Name) is used in the IFIlterValues Interface so that the connecting MOSS Filter Web Part can choose which report parameter is receiving data.

2. The parameter display name and default values are used to build the list of parameters in the Web Part editor.

Note that if parameters are not supplied to the Report Viewer Web Part the ASPX page provided by SSRS Server renders input boxes at the top of the report as seen below.

Our client required that the reporting parameters also be provided through the Web Part Properties Editor and through Web Part Connections. Below is a view of supporting the parameters through the Web Part Properties Editor. This required dynamically discovering and rendering the label and input controls for the report parameters.

From a programmatic standpoint, the first step is to retrieve and store the report parameters so that we can use these for Web Part Connections and Web Part Properties. A CustomReportParameter class is used to store report parameter name/value pairs that are retrieved by an SSRS Web Services call.

ReportingService2005 svc = new ReportingService2005();

parameters = svc.GetReportParameters(ReportPath, historyID, forRendering, values, credentials);

if (parameters != null)

{

//look through each report parameter returned, and add to our list if not

//already in the list

foreach (ReportParameter rp in parameters)

{

if (!_customReportParameters.ContainsKey(rp.Name))

_customReportParameters.Add(rp.Name, new CustomReportParameter(rp));

}

Now that we know the parameters that are available in the report, we can use these parameters for Web Part Connections and Web Part Properties. We implemented the IFilterValues interface to support MOSS Filter Web Parts (Web Parts used to filter other Web Parts see MSDN for more details). To implement the IFilterValues interface, the Report Viewer Web Part must provide the filter value provider (e.g. Choice Filter Web Part) with a list of parameters which can be used to filter the report. Here is a simplified version of filling those parameters:

List<IFilterValues> _providers = new List<IFilterValues>();

[ConnectionConsumer("Parameters", "IFilterValues", AllowsMultipleConnections = true)]

public void SetConnectionInterface(IFilterValues provider)

{

if (provider != null)

{

//add this provider to our collection of providers

_providers.Add(provider);

//Call the ReportServices2005 web service to build a list of parameters

FillParameters();

//create a ConsumerParameter for each of our Report Parameters

List<ConsumerParameter> l = new List<ConsumerParameter>();

//iterate over each ReportParameter and fill the list of

//ConsumerParameters

foreach (CustomReportParameter crp in _customReportParameters.Values)

{

//Note: We are adding parameters by "Prompt" and not by

//parameter name

l.Add(new ConsumerParameter(crp.ReportParameter.Prompt,

ConsumerParameterCapabilities.SupportsAllValue |

ConsumerParameterCapabilities.SupportsMultipleValues |

ConsumerParameterCapabilities.SupportsEmptyValue |

ConsumerParameterCapabilities.SupportsSingleValue));

}

provider.SetConsumerParameters(new

ReadOnlyCollection<ConsumerParameter>(l));

}

}

Below is a Web Part Configuration dialog for the Web Part Connection between a Filter Web Part and the Report Viewer Web Part. Note, the code above provides the “Filtered Parameter” list. This particular report has both “Customer ID” and “Account ID” as available reporting parameters that can participate in a Web Part Connection.

Once the Web Part connection has been established, the Report Viewer Web Part retrieves the filter values from the IFilterValues provider. Below the FillParametersFromWebpartConnection method is called each time the Web Part is rendered to retrieve parameter values from the filter provider in order to build a query string. The query string is used to pass parameter values to the SSRS ASPX page, which is then rendered in the Web Part.

private void FillParametersFromWebpartConnection ()

{

//iterate through each provider

foreach (IFilterValues provider in _providers)

{

//now find our parameter and populate the values

//remember though, we have to search for it by Prompt

foreach (CustomReportParameter crp in _customReportParameters.Values)

{

//match up the provider with the report parameter by prompt

if (crp.ReportParameter.Prompt == provider.ParameterName)

{

if (provider.ParameterValues == null)

{

break;

}

else if (crp.ReportParameter.MultiValue)

{

crp.Value = "";

foreach (string val in provider.ParameterValues)

{

//encode to account for semi-colons in

//mutli-value selects

crp.Value += val.Replace (";",@"\;");

crp.Value += ";";

}

}

else if (provider.ParameterValues.Count > 0)

{

crp.Value = provider.ParameterValues[0];

}

//break out of the foreach loop;

//we have found the parameter

break;

}

}

}

}

Here is an abbreviated sample of building out the query string.

//fill parameters from web part connection, if any

FillParametersFromWebpartConnection();

//loop through each report parameter and build parameters on the query string

foreach (string paramName in _customReportParameters.Keys)

{

if (_customReportParameters[paramName].Value != null)

{

reportUrl += "&" + paramName;

reportUrl += "=";

reportUrl += _customReportParameters[paramName].Value;

}

}

Below are a series of screen shots showing the use of a MOSS Filter Web Part where it is connected to and providing parameters to the Report View Web Part. Below shows a Filter Web Part with preconfigured name/value pairs

Next, we use the Filter Web Part to the select a Customer

And after a customer is selected, you see the filtered report that uses the filter’s value.

Summary

In Summary, this solution leveraged the following to allow a quick turnaround:

• Use of existing Report Explorer and Report Viewer UI provided by SSRS Server through use of IFrames.
• Use of the SharePoint Web Part Framework to leverage Web Part Connections for connecting the Report Explorer to the Report Viewer and for passing in report parameters from Filter Web Parts yielding a richer user experience.
• Use of Web Services within Web Part Properties to dynamically provide configuration of Report Parameters.
• Use of Agile development techniques to build weekly solutions that could be tested and adapted to an enterprise grade solution in 3 weeks.

Key tools that helped in the Web Part Development were:

STS Dev – The STSDev tool is an excellent tool used to quickly create base SharePoint features which can be easily packaged into SharePoint solution files.

SharePoint Solution Installer – The SharePoint Solution Installer is a terrific alternative to stsadm commands. This tool checks for basic SharePoint prerequisites and will deploy or retract your solution using a friendly wizard based interface.

About the Team and Authors

Eric Bowden is a Senior Consultant with ThreeWill who implemented the Report Viewer features.

Tim Coalson is a Senior Consultant with ThreeWill who implemented the Report Explorer features.

Donna Hodges from Microsoft was the technical decision maker that was able to keep the team focused on getting the most bang for the buck.

Audie Wright from Microsoft was the business liaison that defined the business requirements, and ensured that the necessary resources from the client, and Microsoft were provided to effectively solve the business problem.

Tommy Ryan is a co-founder of ThreeWill who helped pull together the article with assistance from the project team and Danny Ryan.