Mauro Cardarelli

SharePoint and Reporting Services - Installation Resources

As posted in several blogs recently, Microsoft has made available a collection of Reporting Services reports based on SharePoint Portal Server data (”Microsoft SQL Server Report Pack for Microsoft Office SharePoint Portal Server 2003”).  The integration story between SharePoint and Reporting Services is actually quite good... but there are a few installation issues.  Here are some tidbits...

Microsoft recommends that you do not install Reporting Services on your SharePoint server.  However, it is possible with a few manual tweaks.  After installing Reporting Services (and Service Pack 2), do the following (these steps come from MSDN - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSinstall/htm/gs_installingrs_v1_9fdy.asp):

1. While installing Reporting Services, you may experience activation failures. Ignore any activation errors that occur.
2. Add the Reporting Services virtual directories to the Windows SharePoint Services list of exclusions. If you installed Reporting Services using the default virtual directories, run the following at the command prompt:

   STSADM.EXE -o addpath -url http://localhost/ReportServer -type exclusion

   and

   STSADM.EXE -o addpath -url http://localhost/Reports -type exclusion

3. Add the following under the HttpModules configuration element of the SharePoint Web.config file if it does not already exist. By default, the SharePoint Web.config file is located at C:\Inetpub\wwwroot.

   < HTTPMODULES>
      < ADD type="System.Web.SessionState.SessionStateModule" name="Session" />

   In addition to adding the session state module, you must also enable session state for the pages element by changing the enableSessionState attribute from false to true. The entry in the configuration files should look like the following:

   < PAGES validateRequest="false" enableViewStateMac="true" enableViewState="true" enableSessionState="true" />

4. From the Internet Information Services (IIS) Manager, ensure that the report server is in an application pool that is separate from the SharePoint server. The report server management user interface, Report Manager, can remain in the same application pool to which it was originally installed. To assign the report server to a separate application pool, you must first create a new application pool. After you have created a new application pool, expand Web Sites, expand Default Web Site, right-click the report server virtual root that you created during setup (the default is ReportServer), and then click Properties. From the Application pool drop-down list, select the newly created application pool. For more information about application pools, see your Internet Information Services documentation.
5. Finally, use the rsactivate utility to activate the local instance of Reporting Services. You need to install Administrative Tools and Utilities as part of Reporting Services setup in order to use the rsactivate utility. By default, rsactivate is located at C:\Program Files\Microsoft SQL Server\80\Tools\Binn. If you installed your report server to the default location, run the following at the command prompt:

   rsactivate -c "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\RSReportServer.config

To install SharePoint Web Parts onto your SharePoint Services installation (these come with Reporting Services SP2), type the following at a command prompt:

stsadm.exe -o addwppack -filename "C:\Program Files\Microsoft SQL Server\80\Tools\Reporting Services\SharePoint\RSWebParts.cab" -url http://sharepointservername

A good resource for understanding how to use the web parts is:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/webrssp2.mspx

To download the sample SharePoint reports, go to:

http://www.microsoft.com/downloads/details.aspx?FamilyId=49159368-544B-4B09-8EED-4844B4E33D3D&displaylang=en

This Report Pack includes the following reports.  Tools are provided to allow you to build custom Report Services reports based on SPS data:

Storage Report
Shows a listing of the virtual servers and the number of collections, sites, areas, lists, files and size. Also shows a size distribution and storage usage chart, and a top 20 sites based on size. 
Storage Trend Report
Shows four charts illustrating the virtual server storage trend, site collection growth trend, area growth trend and list growth trend. 
Site Trend Report
Shows hit counts for virtual servers, collections, areas and lists. Also shows the top 20 sites based on hits. 
Comprehensive Site Collections Report
Shows the list of site collections, who owns the collection, configurable characteristics about the owner and the date the collection was last accessed. 
Detailed Site Collection Report
Shows top 20 pages accessed (based on hit count) for this site collection. 
Detailed Page Report
Shows users who have access to the page, when they last accessed it, any referrer URL and number of hits. Also shows two charts illustrating user distribution and referrer distribution. 
Best Bet Keyword
Shows top 20, top 10, bottom 10, or bottom 20 keywords used for searching. Also shows which keywords have best bets. 
Search Terms
Shows top 20, top 10, bottom 10, or bottom 20 search terms used for searching. Also shows which search terms match a defined keyword.