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.