C H A P T E R 13
Creating Reports Using
Report Builder 1.0, 2.0, and 3.0
When it comes to user-developed reports, we often have to deal with the problem of providing the
underlying data in a controlled and secure way, but with enough freedom for that data to be useful for
making business decisions. In SQL Server 2005, Reporting Services included the first version of the
Report Builder. Report Builder is an application that offloads some of the tedious report design tasks to
the report consumers themselves.
In Report Builder 1.0, users with appropriate permissions could create and publish their own
reports with one caveat: the data had to be provided using a predesigned report model. This report
model created an abstraction layer, if you will, that allowed the users to use the provided attributes as
report elements. As with any new advances in software development, we often find some functionality is
deprecated. In that regard, SQL Server 2012 is no different. We can no longer create Report models using
the 2010 version of BIDS, SSDT, or Visual Studio, but we can still utilize them as a data source within our
reports.
With the release of SQL Server 2008 came the Report Builder 2.0 application. With its Ribbon
technology, akin to other Microsoft Office products, Report Builder 2.0 delivered a familiar interface and
trumped Report Builder 1.0 by allowing users to write queries and use stored procedures to access data
directly from the database, without the extra abstraction layer of a report model. This new interface was
intuitively easier to use, with capabilities that far exceeded those of Report Builder 1.0.
In April of 2010 came the release of SQL Server 2008 R2 and with it came another version of the
Report Builder that offered dataset caching and integration with the enhanced visualization report
controls like the Map and Sparklines, as well as Indicators. Since you are now familiar with producing
reports using BIDS, let’s turn our attention throughout this chapter to creating practical real world
reports in Report Builder 1.0, 2.0 and 3.0 applications.
One thing to note before we get started is that, at the time of writing of this book, the vast majority of
organizations are still using prior versions of SQL Server. As such, we decided to keep Report Models and
Report Builder 1.0 in this version of the book. If SQL Server 2012 is the only version that your
organization owns, or if your company is planning to migrate everything to SQL Server 2012, you can
skip over the Report Model and Report Builder 1.0 sections. If your company is one of those migrating to
2012, just know that there is currently no path for migrating Report Models to SQL Server 2012. With that
caveat behind us, let’s get started.
First, we will explore Report Builder 1.0 and its components. As you will see, even though the user
who compiles the report needs to do some design work, the administrator still needs to design the report
model that provides data to the Report Builder 1.0 client application. We will walk you through the
process of using Report Builder 1.0 from two vantage points—developing and publishing a report model
based on user input and then creating your own ad hoc reports based on that model. The three steps we
will cover are gathering user feedback, creating and publishing the report model, and finally testing the
report model by creating reports with Report Builder 1.0: