Reza Alirezaei’s Blog

Unless you are living under the rock , you’d probably know that since SQL Server 2005 SP2 time frame , you can configure a deployment of SQL Server Reporting Services to work with a deployment of SharePoint – known as SSRS 2008 installation in SharePoint Integrated mode. In the first attempt to combine their Business intelligence and Information Portal technologies together and by releasing SQL Server 2005 SP2 , Microsoft brought two very interesting technologies together and opened up a world of interest to many people including myself . When I first read about SP2 , I was like wait a minute, this is awesome!  Both products that I had been really passionate about for years now are getting much closer! Needless to say that I’ve always appreciated the  efforts Microsoft has put into making the technology , geekyness and weirdness around it transparent to the vast majority of people out there.

When SQL Server 2005 SP2 was released , MS really demonstrated that not only do they listen to their customer feedback, they also care so much about developers and to make their life easier. For example , in the context of SharePoint and SSRS integration , now you can potentially hire a report developer,they can build the reports (in much easier way) and publish them into SharePoint and basically hand them over to the  user community and business users. From here , they can take the wheel, manage and interact with these reports (high level) without having to know what the hell is going on under the hood! Isn’t that amazing that how two completely different technologies can be combined to make things much easier for everyone? Remember, easier something is, more people will use it. More people use it, more popular you’ll become! Microsoft ,for sure, has proved that they’ve learned this very simple rule of life…

The only thing that tipped me over the edge at that time was when I first attempted to bring the the best out of both products in a “real” integration project with a very “difficult-to-get-along” kind of client! (I still have the  nightmare of those two days) .  I really don’t want to talk about those issues here , but what made it difficult for me was no  proper documentation , no active community around both products  and , to an extend,  the immaturity of the integration . Things certainly have changed since then and obviously I’ve learned my “integration” lessons as well! Not that I don’t face any issues these days, but nowadays it’s much easier to find an answer – and yes , I find the answers to the majority of my questions in the blogs of those who are blessed and willing to SHARE their POINTS with the rest of the world!

When SQL Server 2008 and SSRS 2008 was RTMed , I didn’t make the same mistake I had made back in 2005 🙂 . I decided to to gain some home-based lab experiences before I go live with this in real engagements (Didn’t I just tell you that I learnt my integration lessons? 😉  ) Surprisingly, every installations I had at my home-based farms from a single stand-alone installation to a scale-out SSRS  along with a large SharePoint Server farm went really smooth without big stucking points. Documentation around the integration is much better this time around, but I am still not happy by the coverage of SSRS 2008 and SharePoint integration by MS people and community! Let’s hope it gets better soon.

Speaking of SSRS 2008 Add-in for SharePoint, here is one question that I frequently get asked :

I have configured report server in SharePoint integrated mode. I have installed SharePoint Web front-end components on the report server computer.I have downloaded and installed the Reporting Services Add-in for SharePoint Technologies on my other  Web front-end servers (including the one that hosts the Central administation site) , but Reporting Services section doesn’t appear in the Central Administration site;therefore I cannot complete the integration.  Where did it go?

Well , the answer is : You need to activate a site collection-scoped feature called Report Server Integration Feature on the Central Administration site.

This feature has two different behaviors when gets activated on Central administration site than other sites. When activated on the Central administration site , the feature does all of the things it does for other type of sites , plus it adds a section  called Reporting Services under the Application Management. This section must be used to make sure SharePoint is aware of my SSRS instance existence. Here is where the fun part starts 🙂 .

There are three options in this section:

1. Grant Database Access: First you need to specify the server which hosts reporting services database, whether it is on a default or named instances. Essentially what happens here is that the Report Server endpoint and Windows service accounts for that instance (named or default) will be granted required access to the SharePoint databases. During this process, the Report Server service will be restarted.  This is an essential step in integration.
2. Manage integration settings  :  You need to specify Report server URL and the authentication. Pretty straightforward.
3. Set Server Defaults :You set all of your basic defaults. This page contains all of the things you’d normally use Reporting Services Configuration tool to configure them, but they are now managed via SharePoint tier. For example making sure that all data sources use integrated security, so on and so forth. Ad-hoc reporting  is also a powerful feature which can be set and controlled from here.

There is one more action that Reporting Services Add-in for SharePoint Technologies performs on the Central Administration site which is provisioning SSRS integrated help content in the HelpFold  folder:

The add-in also installs some application pages, including pages that you open in Central Administration to set the report server URL and other integration settings in Central Administration and other sites.

In addition to the application pages , the Proxy endpoints are also placed in the 12\ISAPI\ReportServer folder. As you can tell , all of the Reporting Services Proxy endpoints are nicely virtualized and context aware (note wsdl and disco aspx file for each Web service). This means  that no matter how deep you are in each site collection , these endpoints are always accessible via a call to the respective asmx file – for example http://mysite/_vti_bin/ReportServer/ReportServer2006.asmx or  http://mysite/subsite1/…../subsiteN/_vti_bin/ReportServer/ReportServer2006.asmx.

More on SSRS Proxy endpoints in  the Integrated mode can be found here.

A quick list of proxy endpoints here:

1. ReportService2006.asmx : Proxy endpoint to support SharePoint Integrated mode. New functionalities such as Data Driven subscriptions are added to this endpoint.
2. ReportExecution2005.asmx :  Execution endpoint. New functionalities such as On demand load (a.k.a pagination) is added to this endpoint.
3. As you can tell , ReportService.asmx (SSRS 2000 SOAP endpoint) which was deprecated in 2005 , now is removed and no longer supported!
4. ReportService2005.asmx: Proxy endpoint to support Native mode (not in this picture- I hope you know why 🙂 )

All right , let’s just go ahead and see what happens to non-Central admin sites :

First of all , the same feature that we just activated in the Central administration site appears on every site collection meaning that if you want to have the Reporting Services integration , you need to activate this on each site collection:

Once you activate this feature , the following things will be added to your site collection:

Required content types:

Report Viewer Web part:

A section in the Site Settings for managing shared schedules :

Obviously if you want to be able to store your reports in a Report Library and your data sources in Data Connection Library , you need to enable another web-scoped  “Office SharePoint Server Enterprise Site features” feature to get Report Library and Data Connection Library in the create page. This has nothing to do with SSRS Add-in though !

That’s all about it! I hope this blog post can help you verify your SSRS 2008 installation in integration mode.