This posting is intended to provide a brief overview comparison of reporting tools which Microsoft has positioned as possible options for end-user ad-hoc reporting. By ad-hoc reporting, we mean the ability for business users who are experts in their subject area, and are not technical; to construct their own reporting ad-hoc based rather than request the report from IT. The tools we’ll examine specifically are Microsoft SQL Server Report Builder 3.0, and Microsoft Office Excel 2010 pivot tables.
Report Builder and Excel
Report Builder is a scaled down visual design interface for constructing Microsoft SQL Server Reporting Services reports. Being scaled down, it resembles many facets of the Microsoft Visual Studio interactive development environment, with a set of capabilities that is intended to allow a somewhat sophisticated user to create a basic table- or chart-based report from various sources of data.
On the other hand, Microsoft Excel is the tool Microsoft has specifically intended to be used by a wide user-base to create ad-hoc reports. Excel itself is a sophisticated tool; but it has gained an almost universal prevalence among the basic skills of business users. It has limits of its own, such as number of rows per sheet, and simplicity of data definition and formatting. However, some of these limitations are giving way to solutions involving added technology such as PivotTables, PowerPivot, and corresponding server-based software such as Excel Services within SharePoint
But Excel does not afford a number of the features that make Reporting Services a robust report writing and delivery solution, such as subscriptions, data-driven subscriptions, caching and snapshot execution, native security (outside of document passwords, and network access control), and access and development versatility. Herein, SharePoint provides a solution to many of these issues, and SharePoint integration with Excel is evolving rapidly.
Notes Concerning PowerPivot
PowerPivot is a topic that occasionally comes up in the context of discussing Excel as a reporting solution. PowerPivot is an end-user data modeling tool. Much like Analysis Services organizes and manages data around a dimensional model, PowerPivot uses a powerful data manipulation engine to let users obtain data from various sources, filter it, format it, relate it, and bring it into Excel in a table-based model.
PowerPivot is not an ad-hoc reporting tool, per se, but it can provide helpful data access capability in Excel as part of ad-hoc report development. It also is not nearly as robust as Analysis Services for modeling data on an enterprise scale.
SCS continues to be a leader in recommending and providing reporting solutions to our clients, including the complete array of ad-hoc reporting alternatives available using Microsoft tools.