Microsoft Report Builder 3.0

With the release of SQL 08 R2, Microsoft is making Report Builder 3.0 available. It has been a long road from the original Report Builder 1.0. It was a little clunky, not very intuitive, and was dependent on Report Models. Report Models are an abstraction of the data, and are generally designed by the BI Developer to expose data to the Business User for report creation.

While there are some benefits to using Report Models, (You can give fields and views logical names, group data logically, define relationships where none existed, and use them to enforce security to name a few), it still falls on the BI Developer to create them.

With the advent of Report Builder 2.0, we are no longer dependent on Report Models. Users can connect directly to the data source. The beauty of Report Builder 2.0 was that the clunky interface of Report Builder 1.0 was replaced by a much more intuitive “Office-like” interface. This gives users familiar with the standard Office 2010 interface a head start in understanding the environment. The remaining challenge is that they understand the data that they are working with, but there isn’t much we can do about that here.

Report Builder 3.0 picks up where 2.0 leaves off. It keeps the same intuitive interface, the ability to connect directly to various data sources, but it brings in some new features and functionality that really elevate this product. While it is still designed to be accessible to the Business User (or Information Worker, Microsoft is very careful about not describing users of Report Builder asEnd-users), the functionality available makes it a viable tool for experienced Report Developers.

Let’s look at some of the new functionality in Report Builder 3.0.

Report Parts

Report parts are report items (charts, graphs, text boxes, etc,…) that you store on a report server, or on a SharePoint site that is integrated with a report server. You can now reuse these report parts throughout your environment in multiple reports. This allows dev groups to utilize the individual strengths of the team members.

When you add a report part to your report, it maintains a relationship to the original instance on the site or server. You can then modify the report part independent of the original report part object. After you have modified your version of the report part, you can save the modified report part back to the site or server, either adding a new reportpart,or over-writing the original.  When someone modifies the original report part on the site or server, you can choose to accept the modification to the report part in your report, or decline it.

Shared Data Sets

A shared dataset provides a way to share a query to help provide a consistent set of data for multiple reports. These data sets can connect to various external data sources, and can include parameters. You are able to configure the data set to generate cached results for a specific parameter combination using a schedule, or just specifying first use.

As with Report Parts, the shared dataset maintains a relationship with the original instance of thedata seton the site or server.

Data Sources

In addition to the wide variety of data sources available, Report Builder 3.0 has added a few new data sources.

Report Builder 3.0 also exposes data from SQL Azure, allowing reports to be built from data basesin the Cloud. Data in SharePoint lists can be exposed using the SharePoint List Extension and we can expose data from the Microsoft SQL Server Parallel Data Warehouse as well.

Maps

Report Builder 3.0 adds a Map Wizard, and a Map Layer Wizard as well. A map layer displays map elements based on spatial data from a map in the Map Gallery, from a SQL Server query that returns SQL Server spatial data, or from an Environmental Systems Research Institute, Inc. (ESRI)shapefile.

This allows us to build reports that display data through a geographical interface. You can add interactive features such as tooltips anddrillthroughlinks, or provide parameters that enable a user to interactively control the visibility of each layer.

Report Builder 3.0 also comes with some additional BI type tools, such as sparklines,databars, and indicators. These tools allow us to convey a lot of information, in a relatively small space by displaying a graphical icon to represent large volumes of data.

The more time you spend in Report Builder 3.0, the harder it is to look at it as a tool for non-technical users. It has become a very robust BI tool that every BI Developer should take advantage of.  While it is doubtful (to me) that the average Business User will take advantage of all the robust functionality of the product, it is still a benefit to expose this product to them as an ad-hoc report tool, even if they never take it out of second gear. I think Report Builder has made huge strides in becoming a reporting tool that is available to all, yet still has the functionality that only the BI Developers are going to fully appreciate.