Are you a geek? Sorry to be personal, but that's the question you should be asking yourself when choosing between Reporting Services and Report Builder. This article will explain how these two products are almost identical, and then give details on the differences.
Some Background
A common need in business is to publish reports based on corporate data onto a company's website. Microsoft experimented with data access pages in Access, but seem to have settled on SQL Server Reporting Services (currently on version 2008 R2, although SSRS 2012 is at release candidate stage).
However, Microsoft are aware that there are two types of people who'll want to build reports:
- Geeks (that's most readers of this site, I suspect), who perhaps already develop in Visual Studio and who certainly aren't afraid to do a bit of programming.
- End-users, who just want to report on their data, and certainly don't want to be intimidated by complicated software.
Recognising this dichotomy (a posh word, I grant you), Microsoft created SQL Server Reporting Services (SSRS) for the first group of people, and Report Builder for the second. Let's look at each of these products in more detail.
SQL Server Reporting Services
Most people developing SSRS reports will do so using a tool which Microsoft call BIDS, or Business Intelligence Development Studio. I'm not actually convinced this exists; I think it's just a subset of Visual Studio. Strangely, as of now you can't use the latest version of Visual Studio (2010) to develop SSRS reports, having instead to use Visual Studio 2008.
Here's what you see when you try to create a new project in Visual Studio 2008:
What this shows is that Visual Studios is much more than just a tool for creating reports (you can use it to create ASP.NET webistes, Windows applications and much more).
Report Builder
Report Builder, by contrast, doesn't pretend to be any better than it is - a standalone application for creating reports. Here's what you see when you start Report Builder:
You can create reports - and that's about it!
Differences between RB and SSRS
With the above preamble in mind, let's look at the differences between the two products. Before we continue, it's worth stressing here that Report Builder 3.0 and SSRS 2008 R2 are virtually identical. Remember the Parent Trap, with Lindsey Lohan in? Well, Annie and Hallie have more differences between them than do RB and SSRS.
However, there are some discrepancies (this wouldn't be much of an article otherwise), and we'll look at them under 3 headings:
- Cosmetic differences, which don't really affect the functionality of the software.
- Functional differences (the most important category for most users).
- Buggy differences (features which just shouldn't be there in one version, and aren't in the other).
Let's start with a look at the cosmetic differences.
Cosmetic Differences
At first sight, Reporting Services and Report Builder look very different. Here's how you add a control (except they're called Report Items, for some strange reason) in Reporting Services:
Here, by contrast, is how you do the same thing in Report Builder:
Notice any similarities? It turns out that the Report Builder ribbon and toolbox in SSRS contain the same tools which do the same things!
While on the subject of cosmetic differences, Report Builder contains several extra wizards:
Personally, I don't like them: they have a tendency to get you from A to B without explaining either the journey taken or how you could modify it in the future.
Functional Differences
Of far more interest are the functional differences between the two products: the things that work in different ways. There are four that I can think of:
- Report parts
- Query Designer
- The expression builder
- The properties window
Let's consider each of these in turn.
Report Parts
Report parts are an excellent idea. They allow you to save part of a report for reuse.
For example, suppose that you have lovingly spent ages designing your perfect chart:
You now want to use this in other reports. In Report Builder you can save this as a report part:
The tooltip above pretty much explains what report parts do, and how they work. A great idea - so where are they in SSRS?
1-0 to the Report Builder, but keep reading, as I have a feeling that SSRS might come from behind to sneak victory.
Query Deisgner
For every database report you have to specify which tables and which fields it will include, and how. The way this is done in both SSRS and Report Builder is using something called Query Designer, but don't assume that because they have the same name that they'll work in the same way.
Here's a diagram of the Query Designer in SSRS. You'll notice that it looks virtually identical to the view designer in SQL Server Management Studio, and any users of SQL Server or Access will almost intuitively know how to use it:
By contrast, here is part of the query designer in Report Builder (this article assumes that you aren't using a report model; I have to draw a line somewhere!):
What Microsoft have tried hard to do is to make query designer easy to use. However, IMHO they haven't actually succeeded. Two examples:
- To draw a relationship in SSRS involves dragging one field onto another; to draw one in Report Builder involves 12 mouse clicks (I've just counted them).
- In Report Builder there is no way to use the graphic designer to apply sorting. So if, for example, you want the rows for a drop list for a parameter to appear in alphabetical order, you have to edit the SQL generated and add an ORDER BY clause, surely defeating the whole point of the software?
Having said the above, if you have a well-designed database and know how to create views and/or stored procedures, it won't make a blind bit of difference which query designer you use.
The Expression Builder
And so we come, at last, to the single reason why I would choose SSRS any day of the week over Report Builder. For some unfathomable reason, SSRS includes full Intellisense in its expression builder ... and Report Builder doesn't have any.
To illustrate this, here's exactly the same expression in the two software applicaitons. First, Reporting Services:
Next, Report Builder:
Which one would you rather use?
Expressions can get quite complicated in SSRS. The excellent expression builder colours different words, prompts you for arguments and provides squiggly red lines when you make a mistake. Report Builder does none of this. This is a BIG drawback: touchdown for SSRS, methinks.
Choosing a Report Item
It's only a small niggle, but it's nice to be able to use a drop list to choose for which report item you want to set properties:
Which makes it a shame that this drop list doesn't exist in Report Builder! Quite why Microsoft would introduce this difference is beyond me - perhaps some clever forum reader will tell me?
Buggy Differences
Just to complete the picture, here are some minor differences between SSRS and Report Builder, which may influence your purchasing decision!
Crashes
Our experience is that:
- Report Builder crashes occasionally - perhaps once every day or two on average.
- SSRS, by contrast, almost never crashes, but occasionally anomalies occur. When you're 100% confident that you're right and SSRS is wrong, it's often worth existing Visual Studio and going back in again: it's amazing how often this solves a problem.
In general, though, both programs are pretty stable (it's probably worth mentioning that Report Builder requires less memory, runs more quickly and is always free, whereas unless you get Visual Studio Express SSRS is anything but).
Strange Bugs
SSRS contains two bugs (sorry, features) which don't exist in Report Builder:
- When you right-click on a text box to change its properties and select the Number category to set number formatting, everything works perfectly. However, do it a second or third time and the dialog box starts closing itself, and you have to use the properties pane instead. This is irritating, but not earth-shattering.
- When assigning a parameter for a subreport, SSRS has no dropdown - you have to type the parameter in. When you consider that the parameter name is case-sensitive, this increases the scope for error for no obvious reason.
Conclusions
This article is based on my experience of training people in both products (it's a longer verson of an earlier blog on SSRS and Report Builder). Although there are a lot of differences listed above, there's only one which matters: the expression builder.
Why does the lack of intellisense in Report Builder matter so much? Consider the expression I used above:
=ReportItems!txtFirst.Value
When typing this in, you can make the following mistakes:
- Fail to make ReportItems plural
- Forget what the text box is called
- Remember, but type it in using the wrong case (yes, it's case-sensitive)
- Use the wrong property on the end
The expression builder will make each of these mistakes far less likely, because you can see your progress on screen. So until Microsoft include the SSRS expression builder in Report Builder, do yourself and your develoepers a favour: go for SSRS.
Here's hoping that someone finds this useful!
