Deliver Reporting Services Reports to SharePoint
Quite a while ago Brian Welker talked about a delivery extension to Reporting Services to directly send reports to SharePoint. Well, it’s not there and it will still take some time (Office 12 will change the situation). However I was at a customer and he asked for exactly this functionality. So I thought about different options…
The easiest solution would be just to use web folders and data driven subscriptions (or just subscriptions). You just send the reports to a network share which is a SharePoint document library. Well, that’s really easy. But unfortunately you need the “Web Client” service for that and this is disabled at the customer’s site. No idea why but somebody told me that this is quite common…
So the other option would be to leverage web services. Too bad, Microsoft “forgot” to include a “document upload” web service. There’s an example for a “workaround” in MSDN (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_sp2003_ta/html/odc_writingcustomwebservicesforsppt.asp describes how to add a document upload web service to SharePoint) but since the customer has quite a lot of SharePoint servers, deployment would be a problem.
Since Reporting Services 2000 SP2 you have that nice WebParts you can use. But the customer didn’t like to do the administration for all the users accessing reports. He just wanted some static reports in SharePoint which are refreshed automatically.
So the standard subscriptions were an option, too. But if you deliver rendered reports the mail data volume increases quickly, so that wasn’t the perfect way, too.
With the help of some colleagues I decided to go with WebDAV. And that was not to hard… WebDAV is quite easy to understand (at least the basics). You simply do a HTTP GET/PUT/DELETE to download, upload or delete documents from an URL. WebDAV doesn’t know about SharePoint’s metadata, that’s the downside. But this didn’t matter in this case. If you enable versioning you also can just post changed documents using the same name and appear as new versions of an existing document.
So you need just some little scripts to get the report from reporting services, delete it from SharePoint (if you don’t like to have a new version) and send it to the document library.
Since I’m an old DTS hacker (and the customer didn’t upgrade to SQL 2005, yet) I used a DTS package as a “container” for my scripts.
This is an example of a report services download script:
Const adTypeBinary = 1
Const adModeReadWrite = 3
Const adSaveCreateOverWrite = 2
set xmlHTTP=createobject("msxml2.xmlhttp")
Set adoStream = CreateObject("ADODB.Stream")
xmlHTTP.Open "GET", “myReportURL”,bGetAsAsync,”myUser”, “my Password”
xmlHTTP.Send
With adoStream
.Type = adTypeBinary
.Mode = adModeReadWrite
.Open
.Write xmlHTTP.responseBody
.SaveToFile “myFile”, adSaveCreateOverwrite
.Close
end with
set xmlHTTP=nothing
set adoStream=nothing
You can easily wrap it in a DTS ActiveX script task or whatever scripting you prefer. Then you have to exchange myURL with the URL of the report you want to export (i.e. as PDF). A good idea is to open the report in report manager and export it. Just before the “save as” dialog appears copy the URL of the IE window starting up. This is the URL you want…
You can also use “myUser” and “myPassword” to pass credentials for the report manager. I didn’t try a direct “download/upload” scenario, so I first save the exported report to a file “myFile”. Then I need another script to upload the file to SharePoint. This might look like that:
set xmlHTTP=createobject("MSXML2.XMLHTTP.3.0")
Set objStream = CreateObject("ADODB.Stream")
objStream.Type = 1 ' adTypeBinary
objStream.Open
objStream.LoadFromFile “myFile”.Value
xmlHTTP.Open "PUT", “myURL”,false,”myUser”,”myPWD”
xmlHTTP.Send objStream.Read
set objStream=nothing
set xmlHTTP=nothing
“myFile” is the temp file, “myURL” is the URL of the file in the SharePoint document library (just open it in SharePoint and look at he URL, you can extract the path from there, then add the filename you want to use), “myUser” and “myPassword” are credentials to log in SharePoint.
If you want to delete the old version of a file before posting a new one, just use this script:
set xmlHTTP=createobject("MSXML2.XMLHTTP.3.0")
xmlHTTP.Open "DELETE", “myURL”,false,”myUser”,”myPassword”
xmlHTTP.send
set xmlHTTP=nothing
You can leave out the credentials in all the scripts if you have a “clean” AD environment and the user running the script has rights to Report Server / SharePoint.
So this does the trick. Nothing fancy, just some lines of code… I tried that with SQL 2000 (also Reporting Services 2000), but that should work with 2005 as well.
I hope this gives you a little “inspiration” how to use Reporting Services and SharePoint together. And perhaps I helped to heal the pain while you’re waiting for Office 12…
Thomas