Whither SQL Server Reporting Services?

Over the past several years, I've sometimes needed to run reports on a SQL Database. Given the ease with which reports can be created in Access, I figured that there had to be some reporting capabilities built into SQL Server. After some research, I was very surprised to learn that this wasn't true.

There were two obvious alternatives: stick with the free version of Crystal Reports that comes with Visual Studio (which is limited) or buy a commercial reporting tool. The old R&R Report Writer from dBase days was inexpensive and effective. Suffice to say that reporting tools today are dramatically more expensive.

Months later I learned about something called SQL Server Reporting Services. The only problem was finding it. Have you seen how many versions of SQL Server there are out there? MSDE, Express, Workgroup, Standard, Developer, Enterprise... Then you have to sort through Add-Ons such as Analysis Services, Reporting Services and Integration Services. Then you have to figure out what combinations of add-ons can be used with which versions of SQL Server. Finally, you have to figure out what obscure file to download to enable each of the various add-ons.

And did I mention the difficulty in sorting out the licensing rights to the various versions for us in development (non-production) environments?

And, to top it all of, it'll be a cold day in hell before you find any of this in Microsoft documentation. It was actually easier to find some of the information in Wikipedia than in Microsoft's docs!

So here I am, running Visual Studio 2008 on Windows Vista. Of course, VS2008 doesn't ship with SQL Server, you have to install SQL Server Express 2005. No problem there, but SQL Server Express 2005 doesn't ship with Reporting Services and you won't find any download labeled Reporting Services.

I thought I had struck gold when I found an article titled Introducing Business Intelligence Development Studio. That article says that Visual Studio 2005 has specific project types for creating reports and that you just need to select Add New Project. Fantastic! Of course, when I looked in VS2005 there was no such project type and the article had no troubleshooting tips.

Are you detecting a trend here?

I found a post where numerous people attempted to figure out how to get through this morass. The final conclusion was that you had to uninstall the Workstation components and install a new copy from a fresh download of the "SQL Express Toolkit." Of course, the person didn't post the link to that download and it isn't on MSDN Downloads.

I finally found it under the name Microsoft SQL Server 2005 Express Edition Toolkit. When I tried to install it on Vista I got a "compatibility warning." It remains to be seen what problems that causes. (Note that the Business Intelligence components under Workstation are not selected by default, so you have to make sure you check them manually.)

At this point I opened VS2008, tried to add a new project, and discovered that Business Intelligence Project was not one of the choices. (And yes, I did restart VS2008.) More digging on the web uncovered this little gem: Visual Studio 2008 Will NOT Support SQL Server 2005 Reporting Services projects.

So I started VS2005 and saw that Business Intelligence Projects was now visible at the top of the list in the Add New Project dialog in VS2005. Under that project type were two entries for Report Server Project. I also found the SQL Server Business Intelligence Development Studio (BIDS) in my Start menu under SQL Server 2005. I'm not yet sure exactly what the difference is between BIDS and the VS2005 projects.

If you need to install something other than Reporting Services, the instructions above won't help you much. After Setup is complete, you'll see this message:

The version of Business Intelligence Development Tools that is included in SQL Server 2005 Express Edition Toolkit does not include projects for SQL Server 2005 Integration Services or SQL Server 2005 Analysis Services. These projects are available only with editions of SQL Server 2005 that include Integration Services and Analysis Services. SQL Server 2005 Express Edition does not include Integration Services or Analysis Services.

You'll notice that this message does not tell you which versions of SQL Server will do what you need.

So I've gotten further than I was, but I still have several questions to resolve:

1. What is the relationship between BIDS and VS2005?
2. How do I install Analysis Services?
3. How do I install the server-side components of Reporting Services?
4. How will all of this change when SQL Server 2008 is released?

Maybe I should just use Microsoft Visual FoxPro, which is awful, but at least R&R Report Writer still supports it.

[Update 1/9/2008] Here are some initial answers to my questions:

1. BIDS runs in Visual Studio 2005. It's a "personality", just like C++ versus C#.
2. TBD
3. You have to install SQL Server 2005 Express Edition with Advanced Services SP2. In addition, when you do that installation, you have to specifically enable the features for Reporting Services. My recommendation is to enable everything. Don't forget to enable the subprojects. Make sure you install this BEFORE you install Visual Studio 2005 or 2008.
4. TBD