Subtitle: Using the XML Data Source to Query a SharePoint Web Service from within SSRS
Get the URL of the current site from within an SSRS report. So, given a report that is being run from:
The goal is to return:
Of course, the report could be deployed to any site/subsite/folder, so a simple string manipulation won’t cut it.
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:
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:
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:
The above works, but I’d prefer to try to get the value without having to deploy additional assemblies.
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.
- 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
- Create a new data source in the report, give it a good name, and and specify a connection type of “XML”
For the connection string, specify the following:
- On the credentials tab, specify “use Current user” (or hard code user credentials, just don’t leave it on “do not use credentials”)
- Click ok
Create the dataset
- R-click on the data source, and select “Add Dataset”
- Give it a good name
- 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>
- Note that the above XML contains a parameter, but it has no value.
- Click on the Parameters “tab” (ignore the warning about not being able to generate a list of fields)
Click add to add a parameter
- Parameter Name: pageUrl (yes, it’s case sensitive)
- Click the fx button
- Type in “=Globals!ReportFolder” (without the quotes)
- Click ok
- Click back on the query tab, and click “Refresh Fields”
- A box will come up that prompts you for the pageUrl value. Paste in a valid sample URL
- Click OK
That’s it! You should now have a dataset that contains two fields, one of which is WebUrlFromPageUrlResult!