End-User Reporting Tools

Developers have always had a multitude of options for creating SQL Server reports. However, as the need for reporting and business intelligence (BI) has expanded, so has the need for business end users to create their own reports and visualizations. Although SQL Server Reporting Services (SSRS) opened up reporting to a whole new audience that hadn’t employed reporting tools before, in many organizations creating reports for SSRS is still often the domain of the developer. In many cases, having the business user handle reporting directly is an advantage because end users know the real meaning of the information that’s extracted from the data.

Numerous products are starting to address this need. Some products integrate with SSRS, whereas others provide their own infrastructure for managing reports. You might be interested in one of these products if you’ve used SSRS but want to extend its native functionality. I evaluated five products that target end-user rather than just developer reporting. I separated the tools into three categories: Web report builders that provide online-only access, standalone client reporting tools that employ Windows applications for creating reports, and automation utilities.

Web Report Builders
Web report builders provide online-only access. The productsI reviewed include RSinteract and ApexSQL Report.

RSinteract. Independent Computer Systems’ (ICS’s) RSinteract is an add-on to SSRS that provides an alternative environment for building reports. It’s a “zero-footprint” application, running completely within Internet Explorer (IE) and requiring no downloads or client installations. RSinteract makes building reports simpler than using SSRS’s standard Report Builder but still manages to retain the flexibility of a more complex designer.

When RSinteract installs it integrates into SSRS, sharing the same report directory structure. The tool creates its own Active Directory (AD) groups to separately regulate access to its Web interface, but you can still use the standard SSRS interface to view the reports that RSinteract creates. RSinteract’s data sources are separate from those in the SSRS data sources folder and are generated through an administration console (run on the server as a Windows application) by specifying the server type and a connection string. Unlike SSRS’s data models, which need to be maintained independently from the data, RSinteract’s data sources point directly to the database, so they stay current after table changes. When you create a new report, you must select a single data source—although you can choose multiple joined tables, a stored procedure, or a custom SQL statement to provide whatever data the report needs. Unfortunately, RSinteract doesn’t include an editor query builder to write or edit custom SQL.

Although RSinteract outputs standard reports that run in SSRS, the report design interface is much different than SSRS’s Report Builder. RSinteract uses Asynchronous JavaScript and XML (Ajax) to enable a smooth drag-anddrop interface in the browser for creating table, matrix, and chart reports. The actual report, rather than a design surface or preview, displays while you’re working, providing instant feedback and allowing for experimentation and quick changes while building reports. A floating dialog inside the browser window contains all of the report’s settings and lets you immediately apply changes to the report without closing the dialog. One drawback to RSinteract’s interface is its method of displaying fields: Instead of labeling dimensions and measures or field data types, the tool displays a single list of all fields by name only.

Because RSinteract integrates with SSRS, you can use either environment to view RSinteract reports. You can use the tool’s normal report view at any time to directly edit reports that you created through RSinteract. You can’t use RSinteract to edit existing SSRS reports.

Overall, RSinteract is an excellent online tool thatmakes report building more accessible to the business end user. The straightforward interface and WYSIWYGdesigner make RSinteract much more approachable thanstandard report creation tools, and the product’s responsivenessalmost makes you forget it’s a Web application. (see Figure 2)

[Editor’s Note: At press time, the vendor indicated that an updated version of RSinteract would be available in October.]

ApexSQL Report. All the ApexSQL products, including ApexSQL Report, are built specifically for SQL Server. Like RSinteract, ApexSQL Report provides a Web interface for creating and managing reports but doesn’t depend on SSRS. ApexSQL Report is built on ASP.NET so it runs directly in the browser like RSinteract, with the only exception being its Flash-generated charts. For evaluation purposes, ApexSQL provides a hosted demo installation on their Web site that you can use to create reports against a sample database. The interface consists of a treeview that displays all of the available reports, which you can organize into folders, and tab navigation to access additional report editing and administration functions.

ApexSQL Report attaches to a single database at installation and can access other databases only through calls from the stored procedures being used. Unlike SSRS’s data models and RSinteract’s data sources, ApexSQL Report can access data only through stored procedures that exist in the attached database, and the tool allows only a single procedure to be used per report. Stored procedure text can be viewed, but not edited, in the report wizard that creates the reports.

ApexSQL Report’s main strength lies in its simplicity. If the report you need is a basic display of data coming from a stored procedure, the tool can help you quickly create an interactive report that’s instantly Web deployed. Another selling point is integration of FusionCharts (for which a development license is included) to generate the tool’s Flash charts and graphs.

I ran into a few problems while testing ApexSQL Report. First, during the otherwise simple installation, I had to hesitate at the somewhat ominous check box option that simply says “Do not make changes to the database.” The installation instructions confirm that because ApexSQL Report writes its own metadata directly into the attached database (including some tables and stored procedures), this option exists for upgrading existing installations. This might be a problem if you’re hesitant to install products that modify your production databases. I also ran into trouble with ApexSQL Report’s internal stored procedures failing when I connected the tool to a database using case-sensitive collation. The biggest problem I had with ApexSQL Report was browser incompatibility. The interactive functionality of its reports relies on drag and drop, which in the current version of the product is fully supported in IE 6.0—but I couldn’t get it to work with either IE 7.0 or Firefox.

Support for ApexSQL Report is provided primarily through forums at the company’s Web site. The forums seem to be pretty well monitored and on par with Microsoft’s forums (take that as a positive or negative). Responses can take hours or a few days.

At $599 per server license, ApexSQL Report is a bargain if you have lots of users or reports. However, I don’t recommend the product over RSinteract unless you’re on a very tight budget. ApexSQL Report’s niche is now mostly occupied by SSRS itself; the tool needs some additions before I’d suggest choosing it instead of just using SSRS.

Standalone Client Reporting
Standalone client reporting tools use Windows applications to create reports. The products I tested include Crystal Reports Developer Edition and Crystal Reports Server XI, as well as Tableau.

Crystal Reports. Crystal Reports has been around for a long time and has grown into the de facto reporting platform for enterprise applications. Just reading through the long list of supported platforms gives you an idea of how expansive this product is. It’s the only product in this group that provides support for Linux and Java environments and can connect to almost any database platform or other data source. In addition to its capabilities as an embedded platform for applications, its standalone report designer is a capable environment for building, viewing, and exporting reports.

Whereas the other report creation products in this group typically rely on drag and drop of fields from your data source to create reports in mostly precreated formats, Crystal Reports’ designer operates more like SSRS’s Report Builder. It starts with a blank page and lets you build a report however you choose. This method can obviously be more involved than using tools with prestructured formats for basic reports but also allows a huge amount of flexibility in creating exactly the look and layout you want.

The Crystal Reports interface is set up like Microsoft Visual Studio, with lots of toolbars and dockable tool windows. An abundance of “Experts” (i.e., wizards) are available through menus and toolbar buttons to assist in creation of everything from charts to sorting. The Workbench window lets you group multiple individual reports into projects. A preview window renders the current report, to assist in report design or just as a live report viewer. You can export reports from the designer to a multitude of formats, including PDF and HTML.

Crystal Reports XI is available in a new server version that adds automation and online delivery options. Reports can be published to the server and run on schedules, with results made available for viewing or download from the browser. The server version setup is definitely not as quick and simple as for other products in this category. Installation is more involved because the product supports so many platforms—however, setup for a product of this size is typically handled by the IT department rather than the developer or end user, as might be the case for other products in this group. (see Figure 5)

If you’re in a large, heterogeneous organization that needs an enterprise-class cross-platform reporting solution, Crystal Reports is made for you. The product is well suited to embedding reports in applications, and the new server version lets you distribute reports out of the box. Although Crystal Reports doesn’t require in-depth technical knowledge, it’s probably still too complex a solution if you’re just looking for a simple tool that lets business end users create their own reports.

Tableau. Tableau is designed for a single user and concentrates on data visualization rather than creating standard reports. It’s more of a desktop BI tool than a reporting tool and is most powerful when the end user is gaining insight by directly manipulating data.

The standard version of Tableau can connect to data stored in Microsoft Excel or Access or in text files. The higher-end versions add connectivity to a more complete list of database products, including SQL Server, SQL Server Analysis Services (SSAS), Oracle, and DB2. When connecting to a data source, a simple, almost automatic interface lets you select and join multiple tables. Fields are automatically split into dimensions and measures, but you can move them if they were initially miscategorized.

Tableau provides its visual analysis capabilities through a self-contained application that uses a drag-and-drop interface to make creating and manipulating tables and graphs simple. (It’s like an Excel pivot table on steroids.) A short tutorial video available on Tableau’s Web site makes the intuitive interface even easier to get started using, especially for the nonprogrammer novice; you can access this video from a popup that opens the first time you start the software. The overall experience resembles a combination of Excel and a Web browser, with tabbed worksheets containing visualizations that save just like in an Excel workbook, as well as browser-like forward and back buttons that allow simple navigation through the various steps for building or drilling down into a visualization. This flexible navigation system encourages experimentation and eliminates worry about causing irreversible damage to a hardbuilt chart.

Tableau’s “Show Me!” feature lets you automatically generate a view of data by just selecting fields to analyze. Although the initial results might not provide the best view of your data, this feature is sophisticated enough to make a good guess—and the “Show Me! Alternatives” feature allows a bit more control over the type of visualization created while still providing an easy path from data to visualization. This feature is powerful for quickly obtaining insight into data and can be a useful tool for reporting novices to gain an understanding of how a report is built. Because the generated visualizations are just regular Tableau pages, the Show Me! feature can provide a good starting point even if you know exactly how you want a report set up.

It’s no coincidence that Tableau continues to win numerous awards, including a SQL Server Magazine 2007 Editor’s Choice award. Tableau’s features and ease of use make the product easy to recommend. The almost instant insight into data, even in the hands of a novice, is a powerful tool.

Automation Utilities
Although SSRS includes some built-in features for generating and exporting reports, third-party utilities expand those capabilities. The automation utility I tested is ChristianSteven Software’s SQL-RD.

SQL-RD. SQL-RD is unique in this group in that it doesn’t directly affect the content of your reports, but rather how you work with existing reports. It’s basically a set of utilities that includes an advanced scheduler and a report exporter that supports a multitude of formats.

Although at first glance you might dismiss these features as functionality already built into SSRS, the scheduling tools in particular encompass a huge variety of additional options and do a lot more than simply run reports. A wizard lets you easily set up the scheduler to schedule single reports or packages containing multiple reports with the same destination. SQL-RD’s available export formats include Word, Rich Text Format (RTF), Excel, HTML, PDF, and comma-separated value (CSV). You can send reports via file system, email (using any of a variety of server types), ftp, fax, or even multiple printers at once.

SQL-RD also includes event-based and dynamic report schedules. Event-based schedules can be triggered by file system or database value changes. Dynamic schedules can run a report multiple times with a range of values for an input parameter generated from a database field. This option lets you run the entire set of possible parameterized report instances for a given report with a single schedule. Another handy feature is the ability to automatically save a snapshot of the report data whenever a schedule is run. This snapshot saves the actual data rather than just the report contents, so reports can be produced again using data from a prior run.

The administration application has an Outlook-style interface that lets you view all of the configured schedules and start creating new schedules of any of the available types. After actually setting up a schedule, I can’t imagine what other options could be added. The scheduler includes event-triggered schedules, as well as standard recurring time-based schedules, and lets you send an individual report or a report package to multiple destinations at once. You can run additional tasks before, during, or after running the scheduled report, including starting external applications, file system operations, and registry or database settings—or even sending Microsoft Systems Management Server (SMS) text messages.

Although on paper SQL-RD doesn’t seem to have a huge number of features, the tool covers its niche very well and can provide a lot of value. On the downside, many of the more advanced scheduling features such as database field event driven schedules are available only in the higher-priced Enterprise Pro Plus and Corporate editions. However, if SQL-RD fits your environment and you give it a try, you won’t want to live without it afterward.

The Bottom Line
The tools I reviewed each have strengths in their target environment. RSinteract is a very capable extension to SSRS for those looking to ease Web report creation. ApexSQL Report is a quick way to build Web reports if you can stay within its constraints; however, ApexSQL is a weaker competitor with SSRS than other products are, and the tool definitely falls short of RSinteract’s added value. Crystal Reports is a serious enterprise platform that still manages to stay usable on a smaller scale and is also the obvious choice for embedded reporting in applications. SQL-RD provides unique functionality that could be a job saver (or at least a stress saver) if you need more automation than SSRS provides.

Despite the different focus of each of these tools and their varying degrees of success in achieving their targets, Tableau’s ease of use and powerful capabilities stood out the minute I started using the product. This tool is firmly focused on giving meaningful insight into data and is very well executed. Probably the biggest surprise to me was that it was fun to work with, which I can’t often say about a reporting tool.

The importance of reporting to businesses will undoubtedly continue to grow, assisted by the increasing quality of reporting tools. The bottom line is that if you’re still struggling with writing your own custom reporting solution, or you’re using an outdated product, a variety of capable tools are available to make your job easier.