SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

The Report Builder

By Andy Warren,

I've used Reporting Services for a while and more recently been teaching a two day intro class (see all SQLServerCentral classes here) and it's been interesting to watch the student response as we spend time working with Report Builder. The majority have not looked at it. Once they've been briefed they believe it to be less than useful for their needs and they don't think that end users will be able to use it. Today I'd like to introduce you to Report Builder and it's main concepts, and they discuss whether or not I agree with the evaluation of those students.

At a high level, this is Report Builder:

  • New in SQL Server 2005 and comes in the box in every edition except Express.
  • It's primary function is to provide an ad hoc reporting capability to users who would find BI Design Studio overwhelming
  • It's a .Net executable that is deployed via the Report Manager home page - 1 click delivery
  • It's model based, users do not know and cannot control the SQL generated to build their report

Here is how you would access it:

And here is what it looks like:


What does 'model based' mean? Boiled down it's an abstraction layer, one that may closely resemble the physical model in some places and be quite a bit different in other places. The model used here is actually fairly rich, those attributes under Birth Date were all generated automatically when the model was compiled. Having an abstraction layer lets you change the model without changing the database, or vice versa. More importantly for the users, all the joins and join types have already been described, so they just pick the items they want. The entities and fields windows in the image above show the simple model I loaded for the screenshot. Users do NOT need to know or ever see SQL.

Now we can address perception. If you're used to using a full fledged report writer like the one in Access or Crystal, there is no doubt you'll find this product limiting. For example, while it can do charts and crosstabs in addition to standard columnar output, you can only have one of those display types in each report. Keep in mind though - it wasn't designed for people that use Access or Crystal!

The more interesting question is whether the end users can use it. I typically spend about 4 hours teaching the product in my class and while they are all generally experienced with reporting, we try to explore this product thoroughly so that they understand what it can do and are ready to train/support end users of the product if needed. Non IT power users can be up and running in an hour, I can see probably doing a two hour initial training session and then offering a two hour 'advanced session' for those that are interested and have actually put the product to work. I truly believe that end users can not only use this tool, I think they'll enjoy the access it gives them.

The other half of that question is will the results be meaningful. With any tool that required the user to understand the table relationships there was a huge danger of someone doing a cross join, or doing an inner join when they needed an outer join to get the correct results. The model fixes all of that. It's still possible to grab the wrong value from an entity, or to average it when they meant to sum, or any other number of mistakes.

There are two real reasons you should make Report Builder available to at least a subset of your end users:

  • There isn't enough time in the day to do the work we've been given. If even a small percentage of users are able to get their own answers it will improve the state of the business and mean one less task on your list.
  • Politically it's a huge advantage to be able to tell management that you have an ad hoc reporting solution and that for most simple reports users can build their own. Remember, you have three types of users out there. First, you have the ones who either don't care about reports or find the ones you've already provided sufficient - probably 80% of your users. You've got another 10% who just like to play, they ask for minor changes or variations that while probably useful to some degree, aren't compelling in terms of setting aside IT time to make the changes. The final 10% are the ones you're trying to help, the really smart guys and girls who are trying to find an answer or a trend and get incredibly frustrated when no one is willing to help them. Not surprisingly those 10% are the ones most likely to have the ear of senior management and probably be promoted into senior management at some point.

Here's my challenge to you. Don't just discard this tool because of fear of what might go wrong. Find a day to invest in building a model and trying out Report Builder. Then do a pizza lunch or after 5pm session for a couple of power users, just show them the basics and see what they say. From there you can decide if you want to invest any more time in it.


Total article views: 7896 | Views in the last 30 days: 6
Related Articles

Securing a Report Builder Model

SSRS Report Builder Tool - Semantic Model


Ad-Hoc Report Builder Software as a Sevice Model Saas

How can we use Report Builder for SaaS model


Ad-Hoc Report Builder Software as a Sevice Model Saas

How can we use Report Builder for SaaS model


report builder

Microsoft report builder