A conversation on Twitter earlier this week has inspired me to pose this question…Is Report Builder the “Red-Headed Stepchild” when it comes to the Microsoft Self-Service BI toolset? Report Builder doesn’t get much attention these days, for various reasons we’ll discuss below.
(Note: The perspective of this blog entry is Self-Service BI so Report Builder is the reference – however, everything stated is applicable to full-fledged Reporting Services as well.)
Is Report Builder Deprecated?
Nope! Report Models, however, are deprecated. As of SQL Server 2012, you can no longer create or update Report Models (SMDL files). You can continue to use Report Models currently, but it is recommended to move away from them as time allows. Report Builder itself is at version 3.0 and is a mature product.
Self-Service BI Tools
The primary set of Microsoft Self-Service BI tools includes Excel (+ add-ins for data modeling such as PowerPivot), Power View, and Report Builder. Some people actually wouldn’t include Report Builder in this list, but I do believe it has a valid place as one of the Self-Service BI tools (albeit, a smaller audience & used for specific purposes).
Drawbacks of Report Builder
Learning curve / ease of use for report designer. There are a significant number of properties and options. This offers significant control over the output – the cost for this significant control is ease of use because all the options in Report Builder can be a bit overwhelming for the casual business user. However, it’s not overly difficult to use for technically adept users who enjoy working with reporting tools and data.
Limited interactivity. While there are some interactive features (such as drill-down, drill-through, sortable columns), each has to be explicitly defined by the report designer. Report Builder isn’t dynamically interactive like Power View or even Excel – rather, Report Builder is far more suitable for fully formatted reporting needs.
Longer to develop. There’s some things that can be done with Power View or Excel that are inherently more work to do in Report Builder. The first example that comes to mind is hierarchies – with Power View or Excel, you drop a hierarchy onto the row & the navigation up/down works; with Report Builder you’d have to set up what is shown vs. hidden and the toggle properties. This is not overly difficult to set up, but could be frustrating for someone just getting started with Report Builder.
GUI support is limited. There is a drag & drop graphical interface for SQL Server and Analysis Services data sources (plus a couple of others). This limited support leaves the report designer writing query syntax sometimes – which is obviously not the most user-friendly for non-technical folks. Currently the nicest way for users to work with Report Builder is using a BISM data source (i.e., the data is stored in Analysis Services or PowerPivot). SQL Server (relational) can be ok for users to work with if the data sources are made to be understandable & easy to work with (ex: with reporting views or stored procedures) – this takes some effort from the IT Dept. or BI Center to make sure it’s made suitable for self-service.
Positives of Report Builder
Native connectivity to many data sources, including BISM. The Microsoft BI framework is very different from other BI tools (such as Cognos or Business Objects) which require a metadata layer – i.e., a report model between the data source & the reporting tool. Microsoft permits tremendous flexibility here – in fact, you can natively send queries from Report Builder to a variety of databases including non-Microsoft. Power View is very limited in terms of accepted data sources, and Excel can be somewhat limited (unless you bring the data into an intermediary PowerPivot model first – PowerPivot offers great flexibility in this regard, but do realize you are storing the data redundantly).
Significant formatting control. If you need a pixel-perfect highly formatted report, Report Builder is the tool for you. Ironically, this is the inverse of the “learning curve / ease of use” drawback listed above. With Excel you can exercise a lot of control over the look & feel of your report (except Pivot Tables – you have to use formulas if you need to "break” out of the Pivot Table). Power View has some text size control and some color schemes to choose from, but overall offers minimal user control over formatting (after all, it’s a data discovery tool meeting an entirely different need – and it is purposely trying to remain simple).
Consistent RDL file format. If a business user starts a report in Report Builder and needs some help with it, one of the IT or BI folks can open the report using their tool of choice (BIDS or SSDT in Visual Studio), make some updates, and send it back to the user with the file format intact. The consistent format is also helpful if a report is being promoted from the Self-Service environment to the Corporate BI environment.
Reusable elements. To facilitate Self-Service BI using Report Builder, things such as shared data sources (reusable data connections) are obvious but there are others as well. Shared datasets (reusable queries) can be really helpful to handle common things like Dates and Geography. Report parts (reusable charts, graphs, tables) can be helpful to display commonly used elements. Images can also be stored centrally for reuse. Setting up reusable elements does take some effort from the IT Dept. or BI Center though, but can improve the Self-Service user’s experience tremendously.
Parameterization. Reports with a number of parameters (within reason of course) can sometimes be thought of as “guided ad-hoc analysis” because one report can yield many different combinations of results depending on parameter values. Report Builder handles parameterization well.
Subscriptions and alerts. If you wish to have reports delivered to you at a predefined frequency or based upon a condition, then Reporting Services is the tool to make that happen.
Export and RSS capabilities. Report Builder can export to many different file formats. It can also publish an RSS feed – this can be very useful for a business user to consume existing aggregated/calculated data that has been rendered by Report Builder without recalculating or reinventing anything. Utilizing published report data via RSS also helps with the elusive “one version of the truth” that’s a constant challenge.
Integration with SharePoint. With a Report Builder report, you can view or edit the report directly from the SharePoint document library (with appropriate permissions of course). Alternatively, a Report Manager portal can be used (although it would be used in isolation from other BI tools and reports).
So, even though sometimes Report Builder seems to be the “Red-Headed Stepchild” I very much appreciate having the tool in our toolbox. I hope it’s alive and well for a long time.
Got any thoughts on this subject? Leave a comment … I’d love to hear your thoughts.
Disclosure: The company I work for is a Microsoft Partner
Disclosure: I am a real user, and this review is based on my own experience and opinions.
Jul 09 2013