Monday 13 February 2012

SQL Server Reporting Services (SSRS) - Options for Dynamically Setting Report Data Source

I received a call from one of my colleagues in the Oakton Canberra office today who was working at a Federal Government department. He was using a SharePoint-integrated mode of SSRS.
He wanted deployed reports to point to the correct datasource without manually updating the DataSource of the report each time a new site is provisioned. It was not a feasible option for him to be manually changing the report datasources each time a new site in SharePoint was deployed (based on a particular site template that included Reports) - especially when he moved back off site.

I suggested a couple of ways forward - each with their own advantages and disadvantages:
1) Create a custom SQL Reporting Services Reports Extension that can source data dynamically (as outlined here and here
2) Using Expression Based Connection Strings
3) Setting the datasource at Deploy Time instead via Powershell or another scripting technology (or as part of a feature event receiver in SharePoint)

The Deploy-time option was the simplest and cleanest option (though it would have a limitation of breaking if the site locations are moved). A script would need to update the datasources as part of the site migration process. This was deemed to be an acceptable tradeoff.

1 comment:

Brett said...

Geez I wish you posted that script :)