ReportBuilder 3.0 Impresses in SQL Server 2008 R2

sql server

I have been testing out SQL Server 2008 R2 with the November 2009 CTP and this week my focus has been on ReportBuilder 3.0. This product has come a long way since the 1.0 version which was aimed at the end-user to build ad-hoc reports via a browser. Now ReportBuilder 3.0 appears to become the GUI of choice for report developers too. Let's take a look.

ReportBuilder 1.0 required a Report Model to be set up in advance using Visual Studio. The Report Model would contain templates for selected tables and views that the user could build reports from. The architecture benefit was that ReportBuilder would generate the same RDL format that was also used in the Report Designer in Visual Studio Report projects. So advanced users could generate powerful ad-hoc reports quickly by themselves and publish them for other users. That was the theory anyway.

What has transpired is that Microsoft has discovered that most analyst-type users prefer using Excel with a direct connection to the data using an OLEDB provider to a relational or multi-dimensional database. Excel is a product they know inside and out. Learning a new product UI was always a tough sell. However, the report developers would gladly use ReportBuilder to generate powerful RDL reports with advanced features like infinite clickthrough, interactive sorting and drilldown without much effort.

The RDL could then be opened using Report Designer for further editing (once you reset the Dataset to correctly point to the Report Model). But ReportBuilder would not support normal RDL reports built using Report Designer.

Now, ReportBuilder 3.0 supports any RDL report regardless of where it was created and the GUI rivals that of Report Designer.

ReportBuilder is launched from a browser using a button in Report Manager. It can also be executed from a URL from your own application or from Sharepoint. Effectively, the application is downloaded via http and run on the client machine. It can now also be installed locally and run from a Windows menu or shortcut.

The new features in 3.0 include a Map Wizard so you can build an interactive map based on spatial data. You can include your own layer on top of the map to integrate with Virtual Earth, for instance. No mention of Google Earth surprise, surprise. I tested out the USA Map, with the States defined as spatial polygon objects. You can easily make them into hyperlinks for drilldown reporting.

You can now create Shared Datasets so that queries can be reused in multiple reports. You can also create Report Parts that are reusable report items stored on the server. An example may be a chart that is needed on multiple reports. The idea is to be able to snap together a report from components. In the past we used Sub Reports and Stored Procedures so this gives us some extra options.

Other features seem to have been borrowed from Excel. Now we have Sparklines and Data Bars for some extra visualization effects. The Excel export feature itself now allows you to export selected page breaks to named worksheets within the Excel file.

I tested out ReportBuilder 3.0 using the "Create a Report" wizard and also editing existing reports built in Report Designer. It worked well. The GUI did seem to be much easier to use and all the features of Report Designer seemed to be there. So now you have a viable choice and can switch back and forth as you please.

Let the users have Office and Excel. We'll keep ReportBuilder for ourselves, no problem.

cheers
Brian

• Microsoft
• ReportBuilder
• ReportBuilder 3.0
• reporting services
• SQL Server
• SQL Server 2008 R2
• ssrs