Reports are not my favorite part of an application. But to may an end user the reports are the most valuable parts of the system. They are a well presented and clear overview of hours, even days, of work behind the keyboard. So taking good care of the reports is important. A part of my personal dislike is caused by the tools. Crystal Reports was a sheer nightmare. MS Sql Reporting services (RS) is far better but is still a little pale compared to the rest of my toolset.
Visual Studio 2008 looks like the ultimate environment. In case you are working on an .net 2.0 project that's just a project setting. Net 1.x is a different story, I have a virtual machine to handle that. Alas for reporting services it's a different game. At the moment VS 2008 cannot create or import reporting projects. Period. But the situation is a little more complicated than waiting for the service pack. Let's investigate.
RS is part of an MS sql server installation. To the report consumers RS is just a web site. In a default install this web site is an asp.net application which runs in IIS on the sql server machine. The RS web app stores all report data in an own instance of sql server. That scenario is not acceptable for a lot of organizations. The RS web site will live in the DMZ but a database server should be far away behind a firewall. Thank goodness the data for the reports do not have to live in the same db server as RS. They can be anywhere, in any format. All the report needs is a connection string. As the RS database is only used by the web app on the same machine there are a lot of ways to hide the db server to the outer world. All interaction, including uploading reports, is done through the web site.
So setting up RS requires another sql server license. As far I have read al the docs it could be possible to set up RS in such a way that it will use a database on another server. But as configuring reporting services is tricky enough as it is I have skipped this option.
RS is a part of several sql server versions: sql 2000, sql 2005 and sql 2008. Installing sql 2000 will result in an asp.net 1.1 web application, installing 2005 will result in an asp.net 2.0 web application. I am not sure about 2008, that's well hidden in the docs. As moving to another version of a database server is a far bigger step than moving an application to another .NET version I don't have any hand on experience yet.
We experienced quite a gotcha when we upgraded a web application to asp.net 2.0. All went well until we arrived at the RS part. The server is an old reliable Windows 2000 server with sql 2000 RS. The main problem is that Windows 2000 cannot run two different asp.net versions (2.0 for the application 1.1. for RS 2000) in the same web site. That takes server 2003. So upgrading the app requires upgrading either the RS to 2005 or even the complete OS to 2003.
Another gotcha is in the report definitions. It takes Visual Studio to create and edit report definitions. The existing reports were all built using VS 2003 and worked good enough on RS 2000. (Given the many little quirks I talked about in other posts). VS 2008 does not accept a reports project, VS 2005 does. But when importing the project it converts the report definitions. After that RS 2000 no longer understands the reports. RS 2005 does.
Given all this we have several options
- Dive into configuration hell
- Stay with RS 2000. Which will require an upgrade of the server OS and we have to keep VS 2003 alive
- Move to RS 2005. It is a big step to move to another DB server version. But as it is only on the web server that should be manageable. We have to keep VS 2005 alive. Or not move to 2008 yet.
- Wait for RS 2008 and VS 2008 integration. Not an option, it should have been operational yesterday.
My main problem with RS is that it ties my application to stricter versions of it's infrastructure than I had believed. I've been too naive again and any advice is more than welcome.