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.
