Reporting Services and SharePoint

A couple of weeks back I’ve done some research about SQL Server Reporting services combined with SharePoint 2010. There are a couple of options to choose from to show a report on a SharePoint site. Next I will discuss three possible ways with their pros and cons.

Microsoft Report Viewer Web Part

The Microsoft SQL team has developed a reporting services plug-in for SharePoint, this plug-in is free and can be downloaded from: http://www.microsoft.com/downloads/details.aspx?familyid=B3BEBF9D-D86D-48CD-94E2-0639A846BE80&displaylang=en. This add-in includes the Report Viewer Web Part which is a Web Part that can be configured to show a report that is saved in a SharePoint library. For this Web Part to work Reporting Services has to be installed in SharePoint integrated mode.

To place a report on a site take the next steps:

1.       Go to the site.
2.       Put the site in edit mode.
3.       Click on Insert -> Web Part
4.       Choose in the category Miscellaneous the SQL Server Reporting Services Report Viewer Web Part.

Now the webpart is on the page the only thing to do now is to configure the Web Part.

1.       Click on “Click here to open the tool pane”.

2.        Select the location of the report you want to show.
3.       Click on Parameters and Load Parameters. This are the input parameters for the selected report.
4.       Insert the right values for the Report parameters.
5.       Click on Ok.

Refresh the page and there it is, the report you selected with the right parameters already filled in.

Pros:

1.       The biggest pro of this solution is that it is really simple to show a report on a SharePoint page, all the configuration is straight forward.

Cons:

There are some cons to this solution.

1.       The biggest drawback of this solution is that it is not possible to fill a report parameter using the query string.
2.       SQL Server Reporting services has to be installed in SharePoint Integrated mode.

HTML Form Viewer Web Part with JavaScript 

Out of the box Reporting services contains a reporting services web page. This page can be used to show reports by providing the location of the report as a query string parameter. Report parameters can also be filled using the query string.

 The second solution I found was to use the standard HTML Form Viewer Web Part and insert an IFrame in it. Add some JavaScript to the Web Part and there it is the Report in SharePoint.

 To use this solution take the next steps:

1.       Go to the site to place the report on.
2.       Add a HTML Form Viewer Web Part (Category Content Rollup) to the site.
3.       Click on Edit Web Part.

4.       Open the source editor.
5.       Add an IFrame to the source.

&nbsp;<iframe id=”rapport” name=”FRAME” src=”” width=”820″ height=”600″ frameborder=”1″ ></iframe>

6.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Add the JavaScript to the source.

This script invokes the SetIFrameURL when the site is loaded. The SetIFrameURL function reads the project parameters from the query string and combines these parameters with the address of the report server and the report itself. Finally it sets the url of the IFrame.

<script type=”text/javascript”>

&nbsp;&nbsp;&nbsp; _spBodyOnLoadFunctionNames.push(“SetIFrameURL”);

&nbsp;

&nbsp;&nbsp;&nbsp; function SetIFrameURL() {

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; var reportServer = “http://win-c3jvpsatr6s:8088/ReportServer_REPORTING/Pages/ReportViewer.aspx”;

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; var reportUrl = “http%3a%2f%2fwin-c3jvpsatr6s%2fsites%2fManagementRapportage%2fSharedDocuments%2fReport1.rdl”;

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; var reportParameters = “&Projectcode=” + getQueryVariable(“Projectcode”) + “&Link1=” + getQueryVariable(“Link1”);

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; var url = reportServer + “?” + reportUrl + reportParameters;

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; document.getElementById(“rapport”).src = url;

&nbsp;&nbsp;&nbsp; }

&nbsp;

&nbsp;&nbsp;&nbsp; function getQueryVariable(variable) {

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; var query = window.location.search.substring(1);

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; var vars = query.split(“&”);

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; for (var position = 0; position < vars.length; position++) {

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; var pair = vars[position].split(“=”);

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if (pair[0] == variable) {

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return pair[1];

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }

&nbsp;&nbsp;&nbsp; }

</script>

&nbsp;7.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Save the configuration and refresh the page. Now the report has to show up.

Pros:

1.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; There is no need to install reporting services in SharePoint integrated mode.
2.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Report parameters can be filled using the query string.
3.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; No custom made Web Part is needed.

&nbsp;

Cons:

1.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; The configuration is hard. JavaScript is needed to do the configuration what makes the site less maintainable and intuitive to maintain.
2.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Hard if not impossible to use Web Part connections, not yet tried it.&nbsp;

Self made Web Part

When the possibility to feed parameters to the report using the connection string is needed and it isn&rsquo;t an issue to deploy a custom Web Part, the best solution is to use a custom Web Part.

The JavaScript that is needed in the second example can be encapsulated in the Web Part and the Web Part settings can be extended with the configuration properties that are needed. So this solution combines the pros of the previous solutions, the only drawback is that a custom Web Part has to be deployed.

Please contact me for the source of this Web Part.

Conclusion

There is no one solution that fits all the problems. If it isn&rsquo;t an issue to deploy a custom Web Part the third solution is probably the best. Else the decision has to be made if query string parameters are really needed, most of the time the problem can be solved with Web Part connections. Web Part connections are supported by the Report Viewer Web Part.

Share this