Get the Site Url for a Report running in SharePoint Integrated Mode

Subtitle: Using the XML Data Source to Query a SharePoint Web Service from within SSRS

The Challenge

Get the URL of the current site from within an SSRS report. So, given a report that is being run from:

http://server/sites/site/subsite/DocLibrary/folder1/report1.rdl

The goal is to return:

http://server/sites/site/subsite

Of course, the report could be deployed to any site/subsite/folder, so a simple string manipulation won’t cut it.

The Problem

SSRS provides a global field called ReportServerUrl, that normally returns, well, the report server url. In SharePoint integrated mode, this still returns the URL of the Report Server, which is likely to be something like this:

http://servername:port/ReportServer

Not what I’m after. Luckily, there’s another Global called ReportFolder, which will return the path down to the document library / folder. In this example, this Global would have a value of:

http://server/sites/site/subsite/DocLibrary/folder1

Close, but I still needed to get the URL of the site. The following forum post recommended using a .net assembly to get the correct value:

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/f6fde96f-2966-4e1d-a338-9e5de8764b45

The above works, but I’d prefer to try to get the value without having to deploy additional assemblies.

Solution

Webs.asmx is a web service that ships with SharePoint. One of its methods is: “GetWebUrlFromPageUrl”, and it does exactly what I need: it returns a site url given a file or directory url.

Steps:

  • Create an xml data source that points to the webs.asmx service
  • Create a DataSet that calls the GetWebUlFromPageUrl method

Create the XML Data Source

  1. Create a new data source in the report, give it a good name, and and specify a connection type of “XML”
  2. For the connection string, specify the following:
    1. http://server/_vti_bin/webs.asmx
  3. On the credentials tab, specify “use Current user” (or hard code user credentials, just don’t leave it on “do not use credentials”)
  4. Click ok

Create the dataset

  1. R-click on the data source, and select “Add Dataset”
  2. Give it a good name
  3. Paste the following into the query textbox:
<Query><SoapAction>http://schemas.microsoft.com/sharepoint/soap/WebUrlFromPageUrl</SoapAction><Method Name="WebUrlFromPageUrl" Namespace="http://schemas.microsoft.com/sharepoint/soap/"><Parameters><Parameter Name="pageUrl"></Parameter></Parameters></Method><ElementPath IgnoreNamespaces="true">*</ElementPath></Query>

  1. Note that the above XML contains a parameter, but it has no value.
  2. Click on the Parameters “tab” (ignore the warning about not being able to generate a list of fields)
  3. Click add to add a parameter
    1. Parameter Name: pageUrl (yes, it’s case sensitive)
    2. Parameter Value:
      1. Click the fx button
      2. Type in “=Globals!ReportFolder” (without the quotes)
      3. Click ok

  1. Click back on the query tab, and click “Refresh Fields”
  2. A box will come up that prompts you for the pageUrl value. Paste in a valid sample URL
  3. Click OK

That’s it! You should now have a dataset that contains two fields, one of which is WebUrlFromPageUrlResult!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s