harbar.net component based software & platform hygiene

In a nutshell: Reporting Services Addin for SharePoint 2007

Print | posted on Saturday, February 24, 2007 2:59 PM

There's been a lot of interest in the enhanced integration between SQL Server 2005 Reporting Services and SharePoint 2007 delivered by SQL Server 2005 Service Pack 2. This truly is some awesome functionality and useful to anyone looking at Report Center in MOSS, or indeed those who currently have a bespoke Web UI for their reports and are looking for something a bit more robust and feature rich.

This integration is yet another example of the seamless integration opportunities available in SharePoint 2007, and it's place within the enterprise as a platform providing super rich frameworks on which to base composite applications.

From a (cynical) commercial perspective this is quite interesting and somewhat amusing as it could be surmised that Microsoft are now using SQL Server to upsell SharePoint - an enormous opportunity. This could be considered the reverse of the more traditional using cool apps (like SharePoint) to upsell the SQL business.

The SharePoint addin has been in CTP form for some time, but now it's here for real as SP2 finally got done! As all you Vista heads out there know, about time too! :) It works for both WSS and MOSS, obviously some things require MOSS.

Here's a "in a nutshell" guide to deployment...

 

On the Reporting Services box...
Firstly SQL Server 2005 SP2 adds a SharePoint Integration node to the Report Server Configuration Manager. All you need here is to install SP2. Now report server has a couple of modes. Native - the same as before SP2 - the default configuration and SharePoint Integration - this is what we are interested in.

But wait a second - if you are installing in a farm - you must install a SharePoint Web Front End on your Report Server, as this machine must be part of the farm(!) and have access to the SharePoint OM. If you are messing about on a single machine then obviously this is no problem, but it certainly encourages separate machines for databases and features of SQL Server such as Reporting. This should be a consideration in your overall farm design.

Following on from this, all your Report Server instances must be in SharePoint mode, you cannot mix and match in the same deployment.

Most SharePoint Farm/Topology design material relegates SQL Server to a simple 'backend' and there is little focus on the SQL topology itself. Don't fall in this trap and slap everything in the SQL product box all on the same box (as it were) - separate storage and config from application services as per the common model. Think of a "SharePoint Farm" as a superset of SharePoint Topologies and SQL Server Topologies.

Having said that there is no reason you cannot install Report Server on your existing WFEs in say a traditional medium SharePoint server farm and indeed this is often an entirely suitable deployment. However ensure you weigh the additional resource requirements against the cost/number of boxes argument and consider the more scalable, many WFEs option.

If you previously installed SQL 2005 Reporting Services, your current Reporting Services database will be in Native mode, so a new database is required. So you can either change the default, or more simply add a new one.

You lose a bunch of Native features, but gain the SharePoint coolness. Examples of the coolness include being able to use the SharePoint native list functionality with your reports. You know you want some. However, you can't have both Native and SharePoint together on the same instance.

On the SharePoint box...
There is an addin (with a ridiculously long official name) which I will call the RS Addin for SharePoint. You install this bad boy on your WSS or MOSS box(es) and it adds a new feature, which is managed via SharePoint Central Admin. You then configure the feature with the Report Server Web Service and Database locations, and some other reporting specific settings. Now there are new links and functionality in SharePoint Report Center, providing integrated report storage and rendering via your SharePoint site.

Does it go wrong?
Well the basic idea is to avoid IIS Web Site port conflicts, which was the most common problem prior to RTM and emphasized in the documentation. If you are doing a headbutt the keyboard install this is what will likely give you grief. Of course the way to completely nullify the issue is to implement Windows DNS in your organization.

I've also seen the Addin Install take a very long time on a Virtual machine - it thrashes during the last step with the progress bar at full. If you are in a hurry, disable your guest anti-virus before the install.

The skinny...
It's all deeply cool. The basic scenario is if you have a bunch of reports and are looking for the Web UI that should have been in Reporting Services in the first place, as opposed to the lame one that shipped, this is for you. Now this is not a complaint - it actually shows the benefit of waiting for SharePoint 2007 to provide the Web UI, because, well that's what SharePoint is for :)

Again, this feature set is a classic example of basing your enterprise platform on SharePoint, and indeed how that is now truly viable in a way that wasn't with SharePoint 2003. In many respects it offers guidance in terms of architecture and guidance when looking to consolidate the number of web applications with varying user experience (not to mention URLs) in your enterprise to a single delivery platform (SharePoint).

Here is a collection of resources for those interested in implementation:

Feedback

Gravatar

# re: In a nutshell: Reporting Services Addin for SharePoint 2007

This article is excellent and i have some queries. if possible please provide me the solution

I have installed sql server SP2 and Report server add ins, but i am not able to and configured reporting services but the problem is if i go to wss central administration i dont find the Reporting services section in application management

If that will not be available how can i configure it.

Please advice
Srinivas

7/3/2007 6:35 PM | Srinivas
Gravatar

# re: In a nutshell: Reporting Services Addin for SharePoint 2007

Hi,

I have installed MOSS 2007 and then followed by the addin, however, I simply can't get the section "Manage integration settings" to appear in Application Management tab of MOSS Central Admin page.

I have checked that there're no port conflicts. My reporting service is sitting on a different server.

Are there any other possible cause of this issue?

Thanks so much in advance.

G.M.

7/24/2007 8:53 AM | GM
Gravatar

# re: In a nutshell: Reporting Services Addin for SharePoint 2007

You MUST install WSS (or MOSS) on your Report Server box and join it to the Sharepoint Farm in order for things to function correctly.

7/24/2007 12:21 PM | Spence
Gravatar

# re: In a nutshell: Reporting Services Addin for SharePoint 2007

I have not been able to get it working, I already have MOSS and the RS installed. I have the same issue as Srinivas, no Reporting Services section. I also don't have a log entry searching for RS*.log, although the add in appears in my Add/Remove Programs. I need help ASAP as I have a demo for a customer next week!

10/4/2007 10:33 PM | Brad
Gravatar

# re: In a nutshell: Reporting Services Addin for SharePoint 2007

hi there - i had the same problem with the Reporting Services section not appearing in Central Admin. The fix for me was to run setup again using a Farm admin account, selecting the repair option. The first time i installed was with a normal domain admin that didn't have Sharepoint rights. Hope that helps!

10/23/2007 8:38 AM | Raymond Diack
Gravatar

# re: In a nutshell: Reporting Services Addin for SharePoint 2007

works fine until you try it on a windows 2008 server

5/13/2008 2:19 PM | Tom
Gravatar

# re: In a nutshell: Reporting Services Addin for SharePoint 2007

Hi all,

A simple question about the Reporting Services add-in for MOSS 2007: does anyone know for sure if the add-in is only to be used in integrated mode or can it be used in native mode as well?

Thanks.

Sachin

5/22/2008 9:58 AM | Sachin Kainth
Gravatar

# re: In a nutshell: Reporting Services Addin for SharePoint 2007

You must be a SharePoint farm administrator to install the Reporting Services Add-in.

2/2/2010 3:28 PM | Koye
Gravatar

# re: In a nutshell: Reporting Services Addin for SharePoint 2007

I had big issues with this. We have a distributed farm. Two web front ends (WFE), clustered SQL and a search/index box. Managed to get this working by installing SSRS on one of teh SQL boxes in teh cluster. Then installed SSRS integration msi on WFEs.

But to tie it all together I had to install WSS on teh SQL box and put it into the farm. Then install SSRS Integration msi as well. This then tied it all together and now Central Admin on teh front end shows teh SSRS section...not bad for 3 1/2 days work :o(

3/4/2010 2:50 PM | Andy

Post Comment

Title  
Name  
Email
Url
Comment   
Please add 4 and 8 and type the answer here: