by Tim Zapawa, Vice President of Client Services
There are a multitude of options, tools, and programs available for analyzing and reporting on your Advantage data. Deciding on and then implementing an optimal solution can be challenging for many organizations. And as companies and datasets get larger, the importance and impact of these decisions on data analysis increase as well. I'm often asked questions about how the data is accessed and what type of reporting tools should be used. In this article, I will briefly describe and compare some of the software programs available for data analysis and reporting. In the next article, I'll discuss some of the options for how data can be accessed.
Most of our clients use some combination of Advantage user-defined reports, Excel PivotTables, SQL Server Reports, and Crystal Reports for their data analysis and reporting. Each reporting tool has some benefits and drawbacks that should be considered.
Advantage user-defined reports provide an excellent means for novice users with simple reporting requirements. Report columns can be easily selected and labeled from an array of predefined tables that are linked to one another in what we refer to as a virtual file (note that virtual files are the equivalent of a SQL view; both are just a means of tying together multiple tables into one). For organizations that do not use external software programs for analysis or reporting, the Advantage user-defined report tool is ideal, as IT Operations staff does not have to be concerned about learning and supporting programs outside of the Advantage system. However, like most graphically-designed report writers, the reporting development tool provides only basic report development functionality. We offer more advanced courses for organizations that want to write the reports using our Tools language and this can be a good option for companies that want to maintain all their reports within Advantage.
Excel PivotTable reports are probably the most popular option being used at our client sites. It's not surprising, as PivotTable reports are extremely powerful, yet easy to use. Moreover, most users already have Microsoft Excel installed on their computers, so there is no additional licensing expense. And users are generally already familiar and comfortable with Excel. Building expertise with PivotTable reporting can generally be accomplished with minimal training and practice. Microsoft also continues to enhance the reporting tools in each new release of Excel.
Crystal Reports also provides some powerful tools for report development and analysis. It's also integrated with Advantage, so running a Crystal Report is no different than running an Advantage report. Run-time parameters, such as beginning and ending dates, can be specified and passed to the Crystal Report when requested. However, unlike Excel, Crystal Reports is not as pervasively installed. And while licensing costs are fairly nominal, becoming an expert with this software generally requires more training and practice. This program is also ideal for creating business forms--I don't know of any software that even comes close to matching its capabilities in the same range of software licensing cost.
SQL Server Reports is a relatively new player in the report development marketplace. It provides a lot of the same reporting tools as Crystal Reports, although it cannot hold a candle to the business form development tools in Crystal. However, there's no additional licensing cost if you already have SQL Server installed; it's simply a SQL Server component that needs to be turned on. And like Crystal Reports, SQL Server Reports can be run directly from Advantage. We're making extensive use of SQL Server reports in our Business Intelligence module, as there are so many web-enabled options that provide easy-to-use tools for users to view, subscribe-to, and run their reports through a web page. If you attended any of our BI sessions in the past year, you've seen this impressive program in action.
Most organizations opt to use Excel, given the range of development tools and ease of use. One PivotTable report can easily provide the equivalent of hundreds of comparable reports in Advantage, Crystal, or SQL Reports. However, Excel does not have the same level of web-enabled options as SQL or the powerful form programming tools of Crystal. Of course, you need to weigh multiple factors in choosing the best report solution for your organization.
In the next article, I'll discuss important considerations for implementing reporting and data analysis tools within your organization. Contact me at TZapa@AdvantageCS.com or your ACS representative if you'd like more information about these reporting tools and how they can be best implemented at your company.
Editor's Note: Tim Zapawa is the author of Excel Advanced Report Development and Excel 2007 Advanced Report Development, both published by Wiley.