SharePoint and Reporting Services Integration

I have had the opportunity of the last few days to explore the new integration capabilities of the SharePoint and SQL Reporting Services recently released courtesy of SQL 2005 SP2 with my co-worker Nick Barclay.

As a SharePoint specialist, it's great to see the release of another integration capability that further deepens the relationship between SharePoint and the rest of the MS product family. This is another sign of the convergence into the SharePoint sun that knowledge workers will orbit around in the coming years.

Troubleshooting SQL 2005 SP2 installation

First of all, I had some problems installing SQL 2005 SP2, so if you do have this problem, this is the work around that worked for the VPC image I was testing on.

Error message when you try to install a SQL Server 2005 service pack or a SQL Server 2005 hotfix package: "Error 29528. The setup has encountered an unexpected error while Setting Internal Properties"

http://support.microsoft.com/?kbid=925976

Installation and Configuration

Sources

• SharePoint Team Blog

Microsoft SQL Server 2005 SP2 Reporting Services integration with WSS 3.0 and MOSS 2007

http://blogs.msdn.com/sharepoint/archive/2007/02/19/microsoft-sql-server-2005-sp2-reporting-services-integration-with-wss-3-0-and-moss-2007.aspx

• Liam Cleary's post –

MOSS2007 – Reporting Services Add-in

http://www.sharepointblogs.com/helloitsliam/archive/2007/02/21/19801.aspx

In terms of installation and configuration, I found that going through these 2 articles put me on the right track, so I recommend following these. The one thing I did find a little bit different compared to Liams post in the Application Management > Grant Database access, my SQL server name was already configured (same machine) and when I went into it and was asked to enter credentials, I entered the admin credentials, I got 'Unable to log on with the given username and password'. At first this bugged me but in the end I thought it would be ok and it turned out to be the case.

Working with Samples

To road test the new capabilities I grabbed some of the SQL sample databases from:

SQL Server 2005 Samples and Sample Databases (February 2007)

http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

Grab and install:

• SqlServerSamples.msi -- 25,469 KB
  
• AdventureWorksDBCI.msi -- 29,177 KB (case-insensitive collation)
  
• AdventureWorksBICI.msi --
  

Open All Programs > Microsoft SQL Server 2005 > SQL Server Business Intelligence Development Studio

Open the solution AdventureWorks Sample Reports.sln found at

Go into Properties in AdventureWorks Sample Reports

Deployment paths need to change deploying to a SharePoint Document Library with settings.

In this case, I am deploying to the reports center site I have at http://sharepoint/reports and creating two folders to be the container for these http://sharepoint/reports/data , http://sharepoint/reports/reports

Given that I haven't changed any of these, this should deploy successfully and if you browse into your site you should be able to browse the reports, proving a successful implementation of the Reporting Services Integration.

I can now see Data and Reports libraries added to my sharepoint site:

We can then go and view some of the reports:

Add a Reporting Services Viewer Web Part

The final step in this walk through is to create and add a web part page that displays the Reporting Services reports.

To do so create a page under pages and a web part as below:

Open the tool pane to configure the web part and browse to the folder, in my case, http://sharepoint/reports/reports and select report.