Using Reporting Services in SharePoint Integrated Mode with PPS 2010 with SQL Server “Denali” CTP3


The SQL Server team has made a small change to the location of the web service that PPS calls in order to render SQL Server Reporting Services reports when running in SharePoint Integrated mode.  In this blog post, I’m going to go over how to work around that problem and get your reports rendering again.

The changes in Denali now have SSRS running inside the SharePoint Shared Service architecture, which in the long term will make management of this service a lot easier for the people responsible for making sure the service is up and running.  As a consequence of this change, the web service that PPS relies on (ReportExecution2005.asmx) changed location.  It is now located in the _vti_bin folder along with the other SharePoint web services.  Accordingly, we need to point the Report Server URL parameter in Dashboard Designer to this folder.

Using SQL 2008 you would give this:

http://servername/ReportServer

With Denali, you now have to give this:

http://servername/_vti_bin/ReportServer

And if you’re doing anything fancy with non root site collections, you’ll also need to put that in the path, as follows:

http://servername/FancySiteCollectionName/_vti_bin/ReportServer

Here’s a screenshot showing what it should look like in Dashboard Designer:

image

Once you make this change in Dashboard Designer and save the SSRS object, everything should work.  No need to republish any Dashboards.

If you’re having trouble getting Reporting Services to work in SharePoint integrated mode in the first place, here are a few resources that helped get me started:


Comments (2)

  1. John B says:

    There seems to be a bug when using SQL Server 2008 R2 and PP 2010.. When you use a report that is deployed to a site collection, the designer adds the site collection path in twice as below, which fails the report creation. Any insight?

    I enter

    http://<sharepointserver>/Sites/Bi/reports/<ReportSubDirectory>/<ReportFileName&gt;.rdl

    designer attempts to access

    http://<sharepointserver>/Sites/Bi/Sites/Bi/reports/<ReportSubDirectory>/<ReportFileName&gt;.rdl

    more info here

    social.msdn.microsoft.com/…/1fdbcc68-12f0-4b08-814f-0ecf6800b811

    Sorry for the off topic comment, but this seems to be the best place I can find to report the issue (if there is somewhere else, I'd be glad to go there!)

  2. Prasad says:

    I got an error as "Access Denied" for the logged windows user, while trying to preview the report. Do you have any idea about it?