SQL Server Reporting Services interop

SQL Server Reporting Services is nearly ubiquitous as SQL Server itself, it is in nearly all versions of SQL Server from Express (with Advanced services) to DataCenter edition and will soon be available in SQL Azure.   It’s been around for 8 years and if Microsoft used traditional version numbers it would now be on V5.  This wide spread availability and long history does cause some confusion when trying to work out what works with what.

So here are the FAQs I commonly see and get asked about :

FAQ 1. Can Reporting Services report on data stored in [ insert relevant database platform here]?

Although Reporting Services is part of SQL Server it can consume data from pretty well any structured data source e.g Excel, xml, and anything you can get an ADO.Net,OLEDB or ODBC connector for. To design your reports you’ll need these database connectors (drivers) on the machine you’re designing the reports on and on the server they’ll be run from.  

Also don’t forget that if your source data is in a different version of SQL Server that doesn’t matter either, providing you again use the right connector/drivers.

FAQ 2. Can I design a report in the SQL Server 2XXX and then run it on Server 2YYY?

A report can generally be run on a later version of SQL server than it was designed for (unless you have custom code or rare authentication or security setup). Once you save it to the newer version of reporting services it will be automatically updated. However you can never do this the other way around i.e. run a report on an older version an the one it was designed in.   This is because the report definition language (RDL) gracefully changes in each release, so that older reports can run.

Other resources on upgrading to SQL Server 2008 R2 can be found here

FAQ 3 Can I use and older version of SQL server to host the reporting server databases

**

Reporting services uses 2 SQL Server databases, one to hold the metadata about the reports and the other as a temporary workspace. You can use a different version of SQL Server to store these databases and the interop matrix looks like this

SQL Server database Reporting Services version Compatibility
2000 2005 OK
2005 2005 OK
2005 2008 OK
2005 2008 R2 OK
2008 2008 OK
2008 2008 R2 OK

 

FAQ 4. Which versions of Reporting Services work with which versions of SharePoint?

SharePoint version Reporting Services version Compatibility
2007 2005 OK
2007 2008 OK
2007 2008 R2 OK
2010 2005 Not possible
2010 2008 OK, but you actually use the 2008 R2 reporting services add-in for SharePoint (actually this part of the SharePoint installation) and 2008 needs to be at Service pack1 cumulative update 8
2010 2008 R2 OK

 

FAQ 5. Which versions of Visual Studio (VS) work with which version of Reporting Services?

When SQL Server 2005 came out it introduced the BI development studio (BIDS) which is essentially a cut down version of Visual Studio in that case 2005. When SQL Server 20087 came out BIDS was built on VS2008. However (BIDS) in SQL Server 2008 R2 is also still built on VS 2008 , but it’s simple to have another version alongside e.g. VS 2010.

FAQ 5. Which versions of Visual Studio do I use to embed my reports into my applications?  

An ancillary question to this relates to the report viewer control that developers can use to embed reports in their projects.  

  • For SQL Server 2005 there is a report viewer tool, described here, that works in Visual Studio 2005
  • If you followed that link you’ll notice there is an other version option that tales you to similar details about the report viewer control that works in VS 2008 but still against SQL Server 2005.
  • The report viewer in VS2010 the only works against SQL Server 2008 and SQL Server 2008 R2 as detailed here.

in summary the allowed permutations are:

Visual Studio version Reporting Services version Compatibility
2005 2005 OK
2008 2005 OK
2010 2008 OK
2010 2008 R2 OK

FAQ 6 How is Reporting Services licensed?

Probably a post in it’s own right, but to summarise: If you install a server component of SQL Server (so reporting services, analysis services the database engine etc.) on a server then you must license that server for SQL Server, either by CPU or user CAL just as you would the DB engine itself.

This covered in the SQL Server licensing quick reference guide on the SQL Server 2008 R2 licensing page    

This is a classic example of why I  blog,  I  will be asked about what this post relates to again and again and I’ll forget where the answer is unless I page it to a post!