Report Builder – Past, Now and Future
The Report Builder technology made its debut in SQL Server 2005. It targeted business users who wanted to author their own (ad-hoc) reports but they were not necessarily technically savvy, meaning that no technical knowledge about database schemas or query syntax was assumed. The Report Builder technology includes a Report Builder Model and Report Builder Client. Similar to other ad-hoc reporting tools, such as Impromptu, the solution developer was required to implement a Report Builder model that is layered on top of the database and deploy it to the server. This model would receive the reports authored by the end users, which were described in an abstract language (SMDL), and automatically translate them to native queries for the supported data sources (SQL Server, Oracle, and Analysis Services).The initial vision, which didn’t materialize, was to make the query generator open, so developers can plug in custom query generators for other data sources. The Report Builder Client is a ClickOnce desktop report designer which was capable of producing very simple template-based reports.
As Brian Welcker, a former Group Program Manager on the SSRS team, explained in his blog, the Report Builder technology itself enjoyed good acceptance but it wasn’t perfect. To start with, many business users found the Report Builder Client too limiting. Targeting Analysis Services as a data source was problematic to say the least as essentially you had to “wrap” the cube with the Report Builder model layer while a direct access to the cube was preferred. As a result, SQL Server 2008 brought in Report Builder 2.0 which was targeted to supersede the Report Builder Client. As I explained, besides its name Report Builder 2.0 has very little to do with its predecessor as it’s much closer to the BIDS Report Designer than Report Builder 1.0. It provides a full RDL support and shares the same design layout with the BIDS Report Designer but runs outside Visual Studio as a desktop application (ClickOnce-enabled in SQL Server 2008 Service Pack 1).
So, does this mean that the Report Builder technology itself is outdated moving to SQL Server 2008? Absolutely not. If you are looking for an ad-hoc solution that targets small to medium-size SQL Server and Oracle databases, you should definitely evaluate Report Builder. In fact, future releases are expected to bring exciting changes to the Report Builder model which I cannot discuss as they are under NDA. On the report authoring side, business users should evaluate both Report Builder 1.0 (still available in SQL Server 2008) and Report Builder 2.0. Based on my experience, even non-technical users, such as these who struggle with copy and paste, would prefer Report Builder 2.0, so I’d definitely encourage my users to switch to it.
That said, remember that Report Builder is not the only ad-hoc technology that Microsoft provides. As many of you know, I am a big fun of Analysis Services for OLAP and ad-hoc reporting. If you are willing to invest time to learn Analysis Services and MDX (and you should), another approach is to implement a cube instead of a proprietary Report Builder model. If the user needs traditional (banded) reports, they can use the Report Builder 2.0 MDX Query Designer to author such reports from the cube. Of course, one of the advantages of this approach is that Analysis Services enjoys broader client support so you are not limited to Report Builder 2.0 only as a reporting tool. For example, business users can use Excel, ProClarity, and third-party tools. So, choosing a Report Builder model or SSAS is an important decision that requires carefully evaluating your reporting requirements, implementation effort, and skill set.
What may not be so obvious is that if you decide to use a Report Builder model, you can have the best of both worlds: Report Builder model on the server that abstract the database and Report Builder 2.0 client that supports all report authoring features. Unlike the BIDS Report Designer, with Report Builder 2.0 the user doesn’t have to (in fact the user can’t) create a data source that points to the model. Instead, the user wold point to a server Report Builder model when defining the report data source:
- In the Report Data pane, click New –> Data Source. Notice that the Data Source Properties dialog says “Use a shared connection or report model”.
- Browse to the Report Builder model on the server and click OK.
Once in the query designer, notice that it shows the same Report Builder metadata as in Report Builder 1.0. You have the same entity navigation that guides the user to the entities that are related to the selected entity. From here, authoring a report is a matter of dragging and dropping entities and fields. One thing that is not working in Report Builder 2.0 though, which is likely to be fixed in a future release, is filter prompts. Report Builder Client 1.0 would automatically populate the parameter available values if you select a filter as a prompt. Report Builder 2.0 would generate a report parameter but you have manually set up a dataset for the available values and bind it to the parameter.
