As much as people hate to admit it, Microsoft Excel is the most widely used tool for collecting and analyzing data, and, given the market share that Microsoft Office enjoys, Excel may also be the most widely used business intelligence (BI) tool on the market. Unfortunately, Excel spreadsheets are difficult to maintain and frequently disconnected from back-end systems users must enter (and reenter) data in order to analyze and distribute it.
One of the first goals of most BI projects is to cut down on the number of spreadsheets or reports created by hand, and over the years, different vendors have had their own ideas on how best to accomplish this goal. Most vendors have their own tools to design reports that can be distributed to other users in a wide variety of destinations and formats. The only problem with these tools is the learning curve involved in becoming proficient at report design. Other end users will frequently ask that the report be exported back to Excel anyway for further analysis and distribution.
Rather than trying to change this paradigm, Actuate is making the most of this trend with e.Spreadsheet Designer, a report design tool that leverages the user's knowledge of Excel to create feature-rich reports in a familiar interface, with menus, tools, and icons that mimic Excel's.
In addition, you can open existing Excel spreadsheets within e.Spreadsheet Designer and use these spreadsheets as a template for creating reports based on a data source instead of manual data entry. (See Figure 1.) This feature can help eliminate the discontent that users sometimes feel when their Excel spreadsheets are replaced with a proper reporting solution. Instead of a specification for a new report created from a new toolset, users can keep their existing spreadsheets and have them tied directly into back-end systems.
Installing e.Spreadsheet Designer is quick and easy, and when you first open the application you may do a double take as the interface looks so similar to Microsoft Excel. There are a number of tutorials in both the printed and online documentation, but you'll find that after the first few steps required to connect to your data source, you'll be able to speed along with the rest of the report design process unaided. The formatting tools are similar to the ones you would find in Excel, and since the majority of report design time is spent arranging fields and formatting data, the common interface will have you up to speed quickly.
The easiest way to get started creating reports is through the Report Range Wizard, which will take you step-by-step through creating a connection to your database, building a SQL query, and setting some basic formatting options. The e.Spreadsheet Designer supports a wide variety of data sources through ODBC, JDBC, text files, and XML.
If you attempt to connect to an ODBC data source, you'll receive a warning message explaining how the e.Spreadsheet Designer connects to ODBC data sources. The product uses an ODBC-JDBC bridging utility from Sun Microsystems, and Actuate recommends using only ODBC for report development. In order to deploy your report to a production environment, you'll need to obtain and configure the appropriate JDBC driver. This is a potential hurdle, as a number of application and database providers that provide ODBC drivers don't have JDBC drivers available.
Once you have created a connection to your data source, the Report Range Wizard will walk you through selecting the tables and fields for your report, as well as creating any filters you want to add to limit the records that are returned to your report. The e.Spreadsheet Designer also lets you add parameters to a report. But the process of creating and using parameters is not intuitive, and it will take some effort to understand how they work.
You can also use the Report Range Wizard to select the grouping and sorting that you want to appear in your report, as well as any sub- or grand totals. The final steps of the wizard let you select a layout and style for your report, again cutting down on the time you'll need to spend formatting your report.
You always have complete control over the format of your reports the layout and style options just provide a starting point for your own unique design. You can format individual cells or selections and add grouping, outlining, and the like to your report to help categorize and arrange your data.
Once you have created a basic report you can then add additional components, such as charts and graphs. Several different chart and graph formats are available, with support for the most popular business formats (line, bar, and pie), and the available options give you almost complete control over the chart or graph's appearance. (See Figure 2.) An added bonus is that when you save your report to Excel format, these charting options and the underlying data can be modified within Excel itself. Other BI vendors offer export to Excel, but generally export the graphs as static images.
Another standout area within the product is the formula language which, again, closely resembles the formula language that Excel uses. Excel "power users" should have no problems using their existing formula skills within e.Spreadsheet Designer. You can create formulas for a wide range of uses, from simple calculations to complex branching and control. The error messages for incorrect formulas or logic are similar to the error messages you might find within Excel, but the Help file provides only basic coverage of formulas so you'll need to have some experience with Excel to make the most of this feature.
And once you have finished creating your report, you can distribute it by publishing it to Actuate's iServer using a simple process. The iServer is a separate server product from Actuate that provides a robust processing and distribution back end for reports created with e.Spreadsheet Designer and can be used to deploy reports on the Web in a variety of formats.
Overall, e.Spreadsheet Designer has a number of features that make it a serious contender for organizations that currently rely on Excel for their reporting needs. Excel power users should easily transition to creating reports with e.Spreadsheet Designer with little or no formal training. The only possible detriment to implementing the product is the requirement to access data through JDBC instead of ODBC or native drivers.
With that said, third-party database drivers are available if your current application or database vendor doesn't provide a JDBC driver. It could be worth buying an additional driver for the cost savings that e.Spreadsheet Designer provides to Excel-centric organizations looking to move to a proper BI solution.
David McAmis [[email protected]] is an IT consultant, journalist, author, broadcaster, and expert in business intelligence who lives and works in Sydney, Australia.